How to use the current date in a procedure?

0

Good evening guys. I need help. For you to understand, I'm trying to run a system procedure that asks me for some variables that I can pass them in a fixed way, but one is date. in case the date needs to be of the current day, as I want to schedule it to run daily. Manually reporting works, I tried to use getdate() as the value for the @date parameter, but it did not work. What I need is to pass the current date (date of the day) to the parameter @date . If anyone can help, I appreciate it. Using SQL Server 2008 Express.

Procedure code :

GO
ALTER PROCEDURE [dbo].[sp_CloseTerminal] (@StoreCode char(6), @Terminal char(3), @Period char(2), @Operator char(4), @Date datetime, @SaveBalance bit)
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @intSuspended smallint, @intSelling smallint, @nBalance numeric(14, 2)

    SELECT @intSuspended = COUNT(*) FROM LOJA_VENDA WHERE CODIGO_FILIAL = @StoreCode AND TERMINAL = @Terminal AND DATA_VENDA = @Date AND LANCAMENTO_CAIXA IS NULL

    IF @intSuspended > 0
    BEGIN
        SELECT @intSuspended AS SuspendedSales, @intSelling as Selling, CONVERT(bit, 0) AS TerminalClosed
        RETURN
    END

    SELECT @intSelling = COUNT(*) FROM LOJA_VENDA (NOLOCK) V INNER JOIN LOJA_VENDA_PGTO (NOLOCK) PG ON V.CODIGO_FILIAL = PG.CODIGO_FILIAL AND V.TERMINAL = PG.TERMINAL AND V.LANCAMENTO_CAIXA = PG.LANCAMENTO_CAIXA WHERE V.CODIGO_FILIAL = @STORECODE AND V.TERMINAL = @TERMINAL AND DATA_VENDA = @Date AND VENDA_FINALIZADA = 0

    IF @intSelling > 0
    BEGIN
        SELECT @intSuspended AS SuspendedSales, @intSelling as Selling, CONVERT(bit, 0) AS TerminalClosed
        RETURN
    END

    SELECT @nBalance = 0

    IF @SaveBalance = 1
    BEGIN
        SELECT 
            @nBalance = ISNULL(SUM(ENTRADA_CAIXA), 0) + ISNULL(SUM(-SAIDA_CAIXA), 0) 
        FROM 
            LOJA_CAIXA_LANCAMENTOS A 
            LEFT JOIN LOJA_PGTO_CLIENTE B on A.CODIGO_FILIAL = B.CODIGO_FILIAL AND A.TERMINAL = B.TERMINAL AND A.LANCAMENTO_CAIXA = B.LANCAMENTO_CAIXA
        WHERE 
            /* A.TIPO_LANCAMENTO_CAIXA NOT IN ('99') AND */ ISNULL(B.TIPO_PGTO, 'D') = 'D'
            AND A.CODIGO_FILIAL = @StoreCode AND A.TERMINAL = @Terminal AND A.DATA = @Date -- AND A.PERIODO_FECHAMENTO = @Period

        SELECT 
            @nBalance = @nBalance + ISNULL(SUM(VALOR), 0)
        FROM 
            LOJA_VENDA_PGTO A 
            INNER JOIN LOJA_VENDA_PARCELAS B ON A.CODIGO_FILIAL = B.CODIGO_FILIAL AND A.TERMINAL = B.TERMINAL AND A.LANCAMENTO_CAIXA = B.LANCAMENTO_CAIXA
        WHERE 
            TIPO_PGTO = 'D'/*IN ('D', 'C', 'P')*/ AND A.CODIGO_FILIAL = @StoreCode AND A.TERMINAL = @Terminal AND A.DATA = @Date -- AND A.PERIODO_FECHAMENTO = @Period
    END

    INSERT INTO LOJA_CAIXA_LANCAMENTOS 
        (CODIGO_FILIAL, TERMINAL, LANCAMENTO_CAIXA, TIPO_LANCAMENTO_CAIXA, HISTORICO, ENTRADA_CAIXA, SAIDA_CAIXA, 
        DATA, DIGITACAO, CAIXA_VENDEDOR, PERIODO_FECHAMENTO) 
    VALUES 
        (@StoreCode, @Terminal, dbo.fn_GetNextPaymentSequence(@StoreCode, @Terminal, @Date), '99', 'Fechamento do terminal', 0, @nBalance, 
        @Date, GETDATE(), @Operator, @Period)

    UPDATE LOJA_TERMINAIS SET STATUS = 0, DATA_ULTIMO_STATUS = @Date WHERE CODIGO_FILIAL = @StoreCode AND TERMINAL = @Terminal

    SELECT @intSuspended AS SuspendedSales, @intSelling as Selling, CONVERT(bit, 1) AS TerminalClosed

    SET NOCOUNT OFF
END

    
asked by anonymous 03.04.2015 / 03:13

1 answer

2

Hello, André

Try removing the GETDATE () function from the procedure call. One solution would be to leave it in a variable and use this variable instead. Getting kind of like this:

DECLARE @DataAtual DATETIME
    SET @DataAtual = GETDATE()

EXEC [dbo].[sp_CloseTerminal]
      @StoreCode    = '000042'
    , @Terminal     = '001'
    , @Period       = '01'
    , @Operator     = '0000'
    , @Date         = @DataAtual
    , @SaveBalance  = 0

See that it is always good practice to enter in the parameters of the procedure and assign its value. This prevents errors if in the future some other parameter is entered in the procedure.

    
14.04.2015 / 14:28