Stored procedure : assign date if passed parameters are null

user_4218929 0
Viewed 87

I am trying to assign values to stored procedure when the passed arguments are null.

If startDate or endDate is null, I want to assign values to them

Like

@StartDate = '1990-01-01'
@EndDate = DATEADD(YEARS, 30, GETDATE()) -- 30 years from now.

How can I do this?

CREATE OR ALTER PROCEDURE dbo.GetByProductID
(
    @ProductID INT,
    @StartDate DATETIME = NULL
    @EndDate DATETIME = NULL
)
AS
    SELECT ProductId, Name, InsertedDate, UpdatedDate
    FROM Products
    WHERE ProductId = @ProductID
      AND InsertedDate > @startDate
      AND UpdatedDate < @EndDate
stored-procedures sql-server sql
1 Answers
CREATE OR ALTER PROCEDURE dbo.GetByProductID
(
    @ProductID INT,
    @StartDate DATETIME = NULL
    @EndDate DATETIME = NULL
)
AS
    set @StartDate = coalesce(@StartDate,'1990-01-01')
    set @EndDate = coalesce(@EndDate,DATEADD(YEARS, 30, GETDATE())) -- 30 years from now.

    SELECT ProductId, Name, InsertedDate, UpdatedDate
    FROM Products
    WHERE ProductId = @ProductID
      AND InsertedDate > @startDate
      AND UpdatedDate < @EndDate
user_13674540
Related