How to set DATE parameter default to dynamically calculated value in SQL Server-CodePudding

I have a stored procedure with two DATE parameters that I want to have a default value. However, I also want to be able to override these values as needed. I'm using SSMS 18. Here's how I would like my code to work. (this is not a working example)

CREATE PROCEDURE dbo.usp_stuff_and_things
    -- Add the parameters for the stored procedure here
     @begin_date DATE = (SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0) )
     ,@end_date DATE = (SELECT DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) )
    
AS
BEGIN

    SET NOCOUNT ON;

    -- Insert statements for procedure here

These two parameters are grabbing the first day of the previous month (@begin_date) and the last day of the previous month (@end_date). These default values work most of the time, but I'm sometimes required to pull a custom date range. I'd like to be able to pass values like this to the stored proc as needed.

EXEC dbo.usp_stuff_and_things @begin_date = '2021-01-01', @end_date = '2021-12-31'

Is there a way to accomplish this?

CodePudding user response：

A DEFAULT value for a procedure must be a literal, it can't be an expression, and certainly can't be resolved from a SELECT statement. Instead you could DEFAULT the value to NULL and then assign their value to an expression within the procedure's definition if the value is NULL:

CREATE PROCEDURE dbo.usp_stuff_and_things
    -- Add the parameters for the stored procedure here
     @begin_date DATE = NULL,
     @end_date DATE = NULL
AS
BEGIN

    SET NOCOUNT ON;

    IF @begin_date IS NULL
        SET @begin_date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0);

    IF @end_date IS NULL
        SET @end_date = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1);

    -- Insert statements for procedure here

END;
GO

CodePudding user response：

You can't use an expression for the default. Try:

CREATE PROCEDURE dbo.usp_stuff_and_things
    @begin_date date = NULL,
    @end_date   date = NULL   
AS
BEGIN
  SET NOCOUNT ON;
  
  SET @end_date   = COALESCE(@end_date, 
        EOMONTH(GETDATE(), -2));

  SET @begin_date = COALESCE(@begin_date,
        DATEADD(DAY, 1, @end_date));
END
GO

EOMONTH() doesn't have a lot of value in most cases IMHO, but it is an easy way to determine month boundaries dynamically. Some other methods that are better than the messy and cryptic dateadd/datediff approaches:

Simplify Date Period Calculations in SQL Server

I'd also really recommend avoiding yyyy-mm-dd format, as it can be misinterpreted in some contexts as yyyy-dd-mm:

What date/time literal formats are LANGUAGE and DATEFORMAT safe?

Though I have to say, a begin date the day after the end date doesn't make a lot of sense.