I have created this stored procedure that works when hard coded
(in the where clause at @WeekStart and @WeekEnd),
If I try to add parameters to the query @WeekStart and @WeekEnd I get the following error:
Must declare the scalar variable "@WeekStart".
My goal is to do do something like this instead of having to hard code it:
exec dbo.GetTotals @WeekStart='2022-04-11',@WeekEnd='2022-04-25'
The stored procedure:
CREATE PROCEDURE [dbo].[GetTotals]
@WeekStart Date,
@WeekEnd Date
AS
begin
set nocount on;
--get row names
DECLARE
@columns NVARCHAR(MAX) = '',
@sql NVARCHAR(MAX) = '';
-- select the category names
SELECT
@columns+=QUOTENAME(DepartmentName) + ','
FROM
DepartmentTable
ORDER BY
DepartmentName;
--set@colums variable
SET @columns = LEFT(@columns, LEN(@columns) - 1);
-- construct dynamic SQL
SET @sql ='
SELECT * FROM
(
select
JobCode,
DepartmentName,
(COALESCE(MonTime, 0)+COALESCE(TueTime, 0)+COALESCE(WenTime, 0)+COALESCE(ThurTime, 0)+COALESCE(FriTime, 0)
+COALESCE(SatTime, 0)+COALESCE(SunTime, 0)) as total
from TimeSheetTable
INNER JOIN DepartmentTable ON TimeSheetTable.DeptId=DepartmentTable.Id
inner join JobCodeTable on TimeSheetTable.JobId=JobCodeTable.Id
–This Works–
— Where WeekStartDate Between ”2022-04-11” and ”2022-04-11” —
–This is throwing an erro–
Where WeekStartDate Between @WeekStart and @WeekEnd
) t
PIVOT(
sum(total)
FOR DepartmentName IN ('+ @columns +')
)pivot_table
ORDER BY JobCode
'
---- execute the dynamic SQL
EXECUTE sp_executesql @sql;
end
>Solution :
exec sp_executesql @Sql, N' @WeekStart Date, @WeekEnd Date', @WeekStart = @WeekStart, @WeekEnd = @WeekEnd