PIVOT with Parameters in Stored Procedure

Jes Sie

8y1.3k

Hello DBA's good day.

I tried to create a pivot in stored procedure with parameters. Please code snippet:

ALTER PROCEDURE spGet_SummaryOfAttendanceForRegistrar
-- Add the parameters for the stored procedure here
@SchoolCode nvarchar(50),
@SchoolYear nvarchar(50),
@TermCode nvarchar(10),
@GradeLvlCode nvarchar(10)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare @cols varchar(1000)
declare @sqls varchar(2000)
select @cols = COALESCE(@cols + ', ','') + QUOTENAME(AttendanceDate)
from tblStudentAttendance
Group by AttendanceDate
Set @sqls='select * from (select StudentID, StudentName, AttendanceDate, Attendance from tblStudentAttendance WHERE SchoolCode = ''@SchoolCode'' and SchoolYear = ''@SchoolYear'' and TermCode = ''@TermCode'' and GradeLvlCode = ''@GradeLvlCode'') src
PIVOT (Max(Attendance) FOR AttendanceDate
IN ('+@cols+')) pvt'
EXEC(@sqls)
END
GO

If there are parameters, I can't get any results. But if I will remove the parameters, I get what I want.

Any help is very much appreciated. Thank you.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
Accepted answer
Format your sql as follows:
Set @sqls = 'select *
  from (
    select StudentID, StudentName, AttendanceDate, Attendance
    from tblStudentAttendance
    WHERE SchoolCode = ''' + @SchoolCode + '''
    and SchoolYear = ''' + @SchoolYear + '''
    and TermCode = ''' + @TermCode + '''
    and GradeLvlCode = ''' + @GradeLvlCode + ''') src
    PIVOT (
      Max(Attendance)
      FOR AttendanceDate
      IN (' + @cols + ')) pvt '
Before executing you can also print the @sqls variable. So that you can see what the SQL Server sees in your query. Right?
Nitin Sontakke8y ago
We were there where you are today. You will here where we are today.
Jes Sie8y ago
Thank you so much Nitin. I still have long way to learn. People like you help people like me in pursuing this career.