Passing column value in stored procedure in sql

Question

Create Proc [dbo].[SprptAssessmentCompartion_Subject] 
           @intQstnMasterID int,
           @intAssessmentID int, 
           @intQstnMasterID1 int, 
           @intAssessmentID1 int, 
           @intUserID int 

As

Begin

select DISTINCT vchSubject,intUserID,SUM(FrstId) as 'FrstId' ,SUM(ScndId) as  'ScndId' from 
(   SELECT 
    intSubjectID,
    intUserID,
    ISNULL([@intAssessmentID],0) as 'FrstId',
    ISNULL([@intAssessmentID1],0) as 'ScndId',
    FinalsCORE

FROM 
(
    select DISTINCT intSubjectID,intUserID,intAssignmnetID,SUM(KidTtl) as 'KidScore',COUNT(intSubjectID) AS 'FinalsCORE' ,(SUM(KidTtl) /COUNT(intSubjectID)*100) as 'Ct' from 
    (
        select Answetble.vchAssignmentName ,intAssignmnetID,intUserID,intSlNo,isnull(QuiestionPaper.vchTopic,'-') as 'VchTopic',isnull(Answetble.fltMark,0) as 'KidTtl' ,QuiestionPaper.intSubjectID from QuiestionMapping
        inner join QuiestionPaper on QuiestionPaper.intQstnID=QuiestionMapping.intQstnID
         Left outer join 
         (
             select distinct intQstnID,intAssessmentID,intAssignmnetID,intUserID,intQstnMasterID,fltMark,fltTotalMark,intAssignedByUserID,vchAssignmentName from
             (
             SELECT *  from ManageAssessment 
             inner join Assignment ON Assignment.intAssignmnetID=ManageAssessment.intAssessmentID) as B
             INNER JOIN UserMapping ON UserMapping.intPTUserID= intAssignedByUserID
             where intQstnMasterID=@intQstnMasterID and intAssessmentID=@intAssessmentID AND intPTUserID=@intUserID
             OR( intQstnMasterID=@intQstnMasterID1 and   intAssessmentID=@intAssessmentID1 AND intPTUserID=@intUserID) 
         ) 
         as Answetble on Answetble.intQstnID=QuiestionPaper.intQstnID
         where QuiestionMapping.intQstnMasterID=@intQstnMasterID OR  QuiestionMapping.intQstnMasterID=@intQstnMasterID1
    )as A
    GROUP BY intAssignmnetID,intSubjectID,intUserID


) 
AS t
PIVOT
(
  Max(Ct)
  FOR intAssignmnetID IN( [@intAssessmentID] ,[@intAssessmentID1])
) AS p
)as B

inner join subject on Subject.intSubjectID=b.intSubjectID

group by subject.vchSubject,B.intUserID
End

Here @intAssessmentID is one of the columns when I use this in stored procedure I will get error

Msg 8114, Level 16, State 1, Procedure SprptAssessmentCompartion_Subject, Line 47
Error converting data type nvarchar to int.
Msg 473, Level 16, State 1, Procedure SprptAssessmentCompartion_Subject, Line 47
The incorrect value "@intAssessmentID" is supplied in the PIVOT operator.

Can anyone help me to resolve the error?

Answer 1

You cannot specify columns for PIVOT this way, you have to use dynamic SQL.

Create Proc [dbo].[SprptAssessmentCompartion_Subject] 
           @intQstnMasterID int,
           @intAssessmentID int, 
           @intQstnMasterID1 int, 
           @intAssessmentID1 int, 
           @intUserID int 

As

Begin

declare @sql as nvarchar(max) = N'select DISTINCT vchSubject,intUserID,SUM(FrstId) as ''FrstId'' ,SUM(ScndId) as  ''ScndId'' from 
(   SELECT 
    intSubjectID,
    intUserID,
    ISNULL([' + CONVERT(NVARCHAR(20), @intAssessmentID) + N'],0) as ''FrstId'',
    ISNULL([' + CONVERT(NVARCHAR(20), @intAssessmentID1) + N'],0) as ''ScndId'',
    FinalsCORE

FROM 
(
    select DISTINCT intSubjectID,intUserID,intAssignmnetID,SUM(KidTtl) as ''KidScore'',COUNT(intSubjectID) AS ''FinalsCORE'' ,(SUM(KidTtl) /COUNT(intSubjectID)*100) as ''Ct'' from 
    (
        select Answetble.vchAssignmentName ,intAssignmnetID,intUserID,intSlNo,isnull(QuiestionPaper.vchTopic,''-'') as ''VchTopic'',isnull(Answetble.fltMark,0) as ''KidTtl'' ,QuiestionPaper.intSubjectID from QuiestionMapping
        inner join QuiestionPaper on QuiestionPaper.intQstnID=QuiestionMapping.intQstnID
         Left outer join 
         (
             select distinct intQstnID,intAssessmentID,intAssignmnetID,intUserID,intQstnMasterID,fltMark,fltTotalMark,intAssignedByUserID,vchAssignmentName from
             (
             SELECT *  from ManageAssessment 
             inner join Assignment ON Assignment.intAssignmnetID=ManageAssessment.intAssessmentID) as B
             INNER JOIN UserMapping ON UserMapping.intPTUserID= intAssignedByUserID
             where intQstnMasterID=@intQstnMasterID and intAssessmentID=@intAssessmentID AND intPTUserID=@intUserID
             OR( intQstnMasterID=@intQstnMasterID1 and   intAssessmentID=@intAssessmentID1 AND intPTUserID=@intUserID) 
         ) 
         as Answetble on Answetble.intQstnID=QuiestionPaper.intQstnID
         where QuiestionMapping.intQstnMasterID=@intQstnMasterID OR  QuiestionMapping.intQstnMasterID=@intQstnMasterID1
    )as A
    GROUP BY intAssignmnetID,intSubjectID,intUserID


) 
AS t
PIVOT
(
  Max(Ct)
  FOR intAssignmnetID IN( [' + CONVERT(NVARCHAR(20), @intAssessmentID) + N'] ,[' + CONVERT(NVARCHAR(20), @intAssessmentID1) + N'])
) AS p
)as B

inner join subject on Subject.intSubjectID=b.intSubjectID

group by subject.vchSubject,B.intUserID'

exec sp_executesql @sql

End