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.
This question is related to
sql-server
tsql
stored-procedures
parameters
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
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'
Source: Stackoverflow.com