Stored Procedures parameters

Question

I have a question and I wanted to ask for your help. Next: I have a proc and I want when I don’t pass a parameter it returns to me null and when this happens bring the date of the day -1 and time reset and if it is passed execute normal parameter follows as I was doing.

CREATE PROCEDURE Dbo.AtualizaTMP_Venda(
    @IdContrato INT = 1 
) 
    AS

IF @IdContrato = NULL
    BEGIN
    SELECT CAST(CAST(GETDATE() -1 AS DATE) AS DATETIME)
    END

Answer 1

You must check the NULL with the IS instead of = (The reason you can check in the answer to the question "Why NULL values are not selected?". After that you should use the function DATEADD to return the current date -1 day:

CREATE PROCEDURE Dbo.AtualizaTMP_Venda(
  @IdContrato INT = NULL
) AS
BEGIN
  IF @IdContrato IS NULL
  BEGIN
    SELECT DATEADD(DAY, -1, GETDATE()) AS data;

    RETURN;
  END;
  
  -- Continua a execução normal
END;
GO

---

DATEADD

Returns a specified date with the specified numerical range (signed integer) added to the specified date part.