我創建了這個在硬編碼時作業的存盤程序（在@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 -- -- 這是一個錯誤 -- 在 @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
uj5u.com熱心網友回復：
exec sp_executesql @Sql, N' @WeekStart Date, @WeekEnd Date', @WeekStart = @WeekStart, @WeekEnd = @WeekEnd
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/467734.html