0

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
1

1 Answer 1

1
exec sp_executesql @Sql, N' @WeekStart Date, @WeekEnd Date', @WeekStart = @WeekStart, @WeekEnd = @WeekEnd
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.