How to use the current date in a trial?

Question

Good night, you guys. I need some help. For you to understand, I am trying to execute a system process that asks me some variables that I can pass them in a fixed way, but one is date. in case the date needs to be the current day, because I want to schedule it for execution daily. Manually reporting works,I tried to use the getdate() as a value for the parameter @date , but it didn’t work. What I need is to pass the current date (date of day) to the parameter @date. If anyone can help, I’d appreciate it. Using SQL server 2008 express.

Code of the trial:

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

Answer 1

Hello, André

Try to remove the GETDATE() function from the Procedure call. A 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

Note that it is always a good practice to insert in the parameters of the past and assign its value. This avoids errors if in the future some other parameter is inserted in the previous.