传递参数的动态枢轴

Question

我创建了此存储的过程，该过程在硬编码时起作用 （在@weekstart和@weekend的Where子句中），

如果我尝试将参数添加到查询@WeekStart和@weekend，我会收到以下错误：

Must declare the scalar variable "@WeekStart".

我的目标是执行这样的事情，而不必硬编码：

exec dbo.GetTotals @WeekStart='2022-04-11',@WeekEnd='2022-04-25'

存储的过程：

  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

- 此作品 - - 在“ 2022-04-11”和“ 2022-04-11”之间的Weekstartdate - 这扔了一个erro- @weekstart和@weekend之间的WeekStartdate

) t 
PIVOT(
   sum(total)
   
    FOR DepartmentName IN ('+ @columns +')
    
)pivot_table
ORDER BY JobCode
'
---- execute the dynamic SQL
EXECUTE sp_executesql  @sql;

end

Answer 1

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