Where condition in sql PIVOT

Question

I have a problem in following SQL query.. When I am executing this Query without where condition it works fine... but when I use where condition it doesnt work.. it gives following error message..

'Msg 137, Level 15, State 2, Line 4 Must declare the scalar variable "@courseid1".'

ALTER PROCEDURE [dbo].[SP_Attendance]
    @courseid as int=null, @subjid int=null
AS

Declare @colList varchar(max)
Declare @qry varchar(max)

Declare @courseid1 as int=@courseid, 
@subjid1 int=@subjid

SET @colList = STUFF((SELECT distinct ',' + QUOTENAME(SA.Attend_Date)
            FROM Student_Attendance_Sheet SA
            FOR XML PATH(''), TYPE
            ).value('/', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @qry = 'SELECT Roll_No, EnrollmentNo, STUD_FNAME + STUD_MNAME + STUD_LNAME as [Student Name] , '+@colList+'
FROM (
    select SA.Roll_No, SA.Attend_Date, SA.Attendance from Student_Attendance_Sheet SA
    where (SA.Course_ID = +@courseid1+) and (SA.Subject_ID =  +@subjid1+) 
) as s
PIVOT
(
    MAX(Attendance)
    FOR Attend_Date IN (' + @colList + ')
) pvt '
print(@qry)
Exec(@qry)

exec SP_Attendance 2, 3

One more thing.. when I am using where condition in

SET @colList = STUFF((SELECT distinct ',' + QUOTENAME(SA.Attend_Date)
            FROM Student_Attendance_Sheet SA
where (SA.Course_ID = +@courseid1+) and (SA.Subject_ID =  +@subjid1+) 
            FOR XML PATH(''), TYPE
            ).value('/', 'NVARCHAR(MAX)') 
        ,1,1,'')

It works but there is no filtering.. so I have to use where condition in above mentioned manner... Please help me for solving this problem... Thanks

Answer 1

I suggest you.. don't create More variables unnecessarily.. it occupies ur memory space You just go with...

ALTER PROCEDURE [dbo].[SP_Attendance]
    @courseid as int=null, @subjid int=null
AS

Declare @colList varchar(max)
Declare @qry varchar(max)

SET @colList = STUFF((SELECT distinct ',' + QUOTENAME(SA.Attend_Date)
            FROM Student_Attendance_Sheet SA
            where Course_ID= @courseid AND Subject_ID = @subjid
            FOR XML PATH(''), TYPE
            ).value('/', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @qry = 'SELECT Roll_No, EnrollmentNo, STUD_FNAME + STUD_MNAME + STUD_LNAME as [Student Name] , '+@colList+'
FROM (
    select Roll_No, EnrollmentNo, STUD_FNAME, STUD_MNAME, STUD_LNAME, Attend_Date, Attendance from Student_Attendance_Sheet
    where (Course_ID = '+cast(@courseid as varchar(50))+ ') and (Subject_ID =  '+cast(@subjid as varchar(50))+ ') 
) as s
PIVOT
(
    MAX(Attendance)
    FOR Attend_Date IN (' + @colList + ')
) pvt '
print(@qry)
Exec(@qry)

Try this..

Answer 2

The problem with your existing query is your variables @courseid1 and @subjid1 are not defined within your dynamic SQL so they are out of scope.

One way that you can fix this would be to concatenate the string values of your variable to your sql string:

SET @qry 
  = 'SELECT Roll_No, EnrollmentNo, 
      STUD_FNAME + STUD_MNAME + STUD_LNAME as [Student Name] , '+@colList+'
     FROM 
     (
        select SA.Roll_No, SA.Attend_Date, SA.Attendance 
        from Student_Attendance_Sheet SA
        where (SA.Course_ID = '+cast(@courseid1 as varchar(50))+ ') 
          and (SA.Subject_ID = '+cast(@subjid1 as varchar(50))+ ') 
     ) as s
    PIVOT
    (
        MAX(Attendance)
        FOR Attend_Date IN (' + @colList + ')
    ) pvt '
print(@qry)
Exec(@qry)

Another way to write the above query would be to sp_executesql and you will pass in your parameter values:

DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@courseid1 int, @subjid1 int';


SET @qry 
  = 'SELECT Roll_No, EnrollmentNo, 
      STUD_FNAME + STUD_MNAME + STUD_LNAME as [Student Name] , '+@colList+'
     FROM 
     (
        select SA.Roll_No, SA.Attend_Date, SA.Attendance 
        from Student_Attendance_Sheet SA
        where (SA.Course_ID = @courseid1) 
          and (SA.Subject_ID = @subjid1) 
     ) as s
    PIVOT
    (
        MAX(Attendance)
        FOR Attend_Date IN (' + @colList + ')
    ) pvt '

EXECUTE sp_executesql @qry, 
  @ParmDefinition, 
  @courseid1 = @courseid,
  @subjid1 = @subjid;

As far as your second issue with the WHERE clause, you should write the query to generate the list of names similar to:

SET @colList 
    = STUFF((SELECT distinct ',' + QUOTENAME(SA.Attend_Date)
             FROM Student_Attendance_Sheet SA
             where (SA.Course_ID = @courseid1) 
               and (SA.Subject_ID = @subjid1) 
            FOR XML PATH(''), TYPE
            ).value('/', 'NVARCHAR(MAX)') 
        ,1,1,'')