Stored procedure with default parameters

Question

I am trying to create a stored procedure based on a query I wrote with parameters that are predefined   When restructuring to a create stored procedure and I execute the stored procedure it states that the parameters were not supplied  Can anyone tell me why    I know I have missed something essential but after messing about with the code I have reached the point of needing some help from the experts   This is my code  shortened    Alter Procedure  Test       StartDate AS varchar 6         EndDate AS varchar 6  AS     Set  StartDate    201620  --Define start YearWeek     Set  EndDate     SELECT CAST DATEPART YEAR getdate    AS varchar 4     CAST DATEPART WEEK getdate   -1 AS varchar 2         SELECT                FROM          SELECT DISTINCT                YEAR    WeekOfYear            FROM                dbo   DimDate            WHERE                Year     WeekOfYear  BETWEEN  StartDate AND  EndDate  dimd     LEFT JOIN           Schema   Table1  qad ON  qad  Year    qad  Week of the Year      dimd  Year    dimd WeekOfYear    When I run the procedure I get      Msg 201  Level 16  State 4  Procedure test  Line 0   Procedure or function  test  expects parameter   StartDate   which was not   supplied    Thanks in advance

User · Accepted Answer

I wrote with parameters that are predefined

They are not "predefined" logically, somewhere inside your code. But as arguments of SP they have no default values and are required. To avoid passing those params explicitly you have to define default values in SP definition:

Alter Procedure [Test]
    @StartDate AS varchar(6) = NULL, 
    @EndDate AS varchar(6) = NULL
AS
...

NULLs or empty strings or something more sensible - up to you. It does not matter since you are overwriting values of those arguments in the first lines of SP.

Now you can call it without passing any arguments e.g. exec dbo.TEST

User · Answer

I d do this one of two ways   Since you re setting your start and end dates in your t-sql code  i wouldn t ask for parameters in the stored proc  Option 1  Create Procedure  Test  AS     DECLARE  StartDate varchar 10      DECLARE  EndDate varchar 10      Set  StartDate    201620  --Define start YearWeek     Set  EndDate     SELECT CAST DATEPART YEAR getdate    AS varchar 4     CAST DATEPART WEEK getdate   -1 AS varchar 2     SELECT    FROM      SELECT DISTINCT  YEAR   WeekOfYear  FROM  dbo   DimDate  WHERE  Year   WeekOfYear  BETWEEN  StartDate AND  EndDate   dimd     LEFT JOIN  Schema   Table1  qad ON  qad  Year  qad  Week of the Year      dimd  Year  dimd WeekOfYear    Option 2  Create Procedure  Test   StartDate varchar 10   EndDate varchar 10  AS  SELECT    FROM      SELECT DISTINCT  YEAR   WeekOfYear  FROM  dbo   DimDate  WHERE  Year   WeekOfYear  BETWEEN  StartDate AND  EndDate   dimd     LEFT JOIN  Schema   Table1  qad ON  qad  Year  qad  Week of the Year      dimd  Year  dimd WeekOfYear    Then run exec test  2016-01-01   2016-01-25

[sql-server] Stored procedure with default parameters

Examples related to sql-server

Examples related to tsql

Examples related to stored-procedures

Examples related to parameters