Stored procedure with default 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) = NULLAS...
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) ASSELECT *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'