Pass Parameters in Pivot SQL

Question

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.

Which dbms are you using? (The above code is product specific.) — jarlh
– jarlh, Commented Nov 24, 2022 at 8:30
And where is this @emp_group coming from? What datatype it is? It is not declared anywhere in this query. — Nenad Zivkovic
– Nenad Zivkovic, Commented Nov 24, 2022 at 9:10
The parameter @emp_group was declared in the procedure but it was not shown in the above query. It was declared as nvarchar datatype. Thanks. — newbeliever
– newbeliever, Commented Nov 24, 2022 at 9:31

Falko · Accepted Answer · 2022-11-25 07:13:46Z

1

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';

edited Nov 25, 2022 at 7:13

answered Nov 24, 2022 at 10:13

Falko

2062 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

newbeliever Over a year ago

When I put a static value of @emp_group in the query for example, @emp_group=''Daily'' it did returns records. But when using as parameter, the problem occurs. Can you provide a sample on how to do that in the stored procedure? The stored procedure will accept 3 parameters (DateFrom, DateTo, EmployeeGroup)

Falko Over a year ago

i modified the sql statement. please take care that when you join Table1 with Table2 not to use "SELECT * FROM" ... that generates duplicate column names and sql server will not allow this. Please, specify the sql statement for your needed output.

newbeliever Over a year ago

Thank you Sir and it works! The actual query really select specific columns and not returing all columns on both tables.

Collectives™ on Stack Overflow

Pass Parameters in Pivot SQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related