Pass Parameters in Pivot SQL-CodePudding

I had this query below and got an error with parameter @emp_group during execution. Need your suggestion on how to pass the parameter.

DECLARE @cols NVARCHAR (MAX)
DECLARE @query NVARCHAR(MAX);
    
SELECT @cols = COALESCE (@cols   ',['   CONVERT(NVARCHAR, [LogDate], 106)   ']', 
                   '['   CONVERT(NVARCHAR, [LogDate], 106)   ']')
                   FROM    (SELECT DISTINCT LogDate FROM AttendanceLog where LogDate between DateFrom and DateTo) PV  
                   ORDER BY [LogDate]
                   
    SET @query = '  SELECT * FROM 
                 (
                    SELECT * FROM Table1 A
                    INNER JOIN Table2 B ON B.empno= A.empno WHERE A.Status =  ''Active'' and B.EmployeeGroup = @emp_group
                        
                 ) PvTable
                 PIVOT 
                 (
                     SUM(OTHrs)
                     FOR [LogDate] IN ('   @cols   ')
                     
                ) PvTable      
                ';     
    EXEC SP_EXECUTESQL @query

The query will run when removing the parameter but need to filter the records. thanks in advance.

CodePudding user response：

You can can do the same thing with @emp_group like you did with @cols. Extract them vom the text and pass them as a paramenter.

SET @query = 'SELECT * 
              FROM (SELECT A.*, B.EmployeeGroup
                    FROM Table1 A
                    INNER JOIN Table2 B ON B.empno= A.empno 
                    WHERE A.Status =  ''Active'' and B.EmployeeGroup = '''   @emp_group   ''') PvTable
              PIVOT(SUM(OTHrs) FOR [LogDate] IN ('   @cols   ')) PvTable';