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.

Answer 1

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