Stored procedure with default parameters

39
votes

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.

2
sql-servertsqlstored-proceduresparameters
why are you asking for @StartDate and @EndDate as a parameter if you're setting them in your tsql? just declare them in the the t-sql and it should run fine - jellz77
Your title refers to defaults but you aren't actually specifying any. You could add = null to the declarations to actually give them defaults and then you'd be able to exec without passing them. Existing answers already point out some other fixes. - shawnt00
Sorry been a manic weekend, the reason why I have to use varchar is because the DIM Date table it connects to have the WeekOfYear and Year storted as an nvarchar and the data loaded is coming from Google Analystics which comes out as text. Its for a simple report temporary report but I will be looking to convert in the future. - TSQL_Newbie

2 Answers

69
votes

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

3
votes

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'