Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

dynamic pivot with parameter passed in

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:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading