dynamic pivot with parameter passed in-CodePudding

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

CodePudding user response：

exec sp_executesql @Sql, N' @WeekStart Date, @WeekEnd Date', @WeekStart = @WeekStart, @WeekEnd = @WeekEnd