PIVOT with Parameters in Stored Procedure

Question

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.