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 · 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

User · 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

[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