Operanden Daten vom Typ varchar ist ungültig für die Summe, der operator - Prozedur-Fehler-Korrektur

Ich bin mit SQL-Server - 2005...

ALTER Proc [dbo].[ExamResult] (@Course_Id varchar(100), @Semester varchar(10))
as
begin
    declare @subjname varchar(100)  
    declare @subjects varchar(7000)  
    declare @subjectsselection varchar(7000)  
    declare @SumSelection varchar(7000)  
    declare @NoOfSubjects int
    set @NoOfSubjects = 0

    set @subjects = ''  
    set @subjectsselection = '' 
    set @SumSelection = ''

    DECLARE subject_cursor CURSOR  
    FOR SELECT distinct Subject_Name FROM Exam_Result where course_id = @Course_Id And Semester = @Semester 

    OPEN subject_cursor  

    FETCH NEXT FROM subject_cursor  
    INTO @subjname  

    WHILE @@FETCH_STATUS = 0  
    BEGIN  
        set @subjects = @subjects + '[' + @subjname + '],'  
        set @subjectsselection = @subjectsselection + 'Sum(Isnull([' + @subjname + '],0)) As [' + @subjname + '],' 
        set @SumSelection = @SumSelection + 'Sum(Isnull([' + @subjname + '],0))+' 

        set @NoOfSubjects = @NoOfSubjects + 1

        FETCH NEXT FROM subject_cursor  
        INTO @subjname  
    End  
    CLOSE subject_cursor;  
    DEALLOCATE subject_cursor;  

    select @subjects = LEFT(@subjects, LEN(@subjects) - 1)  
    select @subjectsselection = LEFT(@subjectsselection, LEN(@subjectsselection) - 1)  
    select @SumSelection = LEFT(@SumSelection, LEN(@SumSelection) - 1)  

    print @subjects  
    print @subjectsselection  
    print @SumSelection

    declare @query nvarchar(4000)  

    set @query = 'select S.Enroll_Number, pvt.Student_Name, pvt.Course_Id, pvt.Semester, ' + @subjectsselection + ',' 
    set @query = @query + 'Exam_Type,' + @SumSelection + ' As Grand_Total, '
    set @query = @query + '(' + @SumSelection + ')' + '/' + convert(varchar(10),@NoOfSubjects) + ' As Avg'
    set @query = @query + ' From '  
    set @query = @query + '(select Enroll_Number, Student_Name, Course_Id, Semester, Subject_Name, MarksObtained, Exam_Type from Exam_Result ) ps '  
    set @query = @query + ' pivot(sum(MarksObtained) for Subject_Name in (' + @subjects + ')) as pvt'  
    set @query = @query + ' inner join Stud_Info S on S.Enroll_Number = pvt.Enroll_Number '
    set @query = @query + ' where pvt.Course_Id = ''' + @Course_Id + ''' and pvt.Semester = ''' + @Semester + ''''
    set @query = @query + ' group by S.Enroll_Number, pvt.Student_Name, pvt.Course_Id, pvt.Semester, Exam_Type'
    print @query
    exec sp_executesql @query  
end

Exam_Result Tabelle:

CREATE TABLE [dbo].[Exam_Result](
    [Result_Id] [int] IDENTITY(1,1) NOT NULL,
    [Enroll_Number] [varchar](50) NULL,
    [Student_Name] [varchar](100) NULL,
    [Course_Id] [varchar](50) NULL,
    [Semester] [varchar](50) NULL,
    [Subject_Id] [varchar](50) NULL,
    [Subject_Name] [varchar](50) NULL,
    [MarksObtained] [Varchar](10) NULL,
    [Exam_Type] [varchar](50) NULL)

Sample-Daten-Abfrage -...

INSERT into Exam_Result VALUES('11147','Mr.Mack28','CIV','1','PS1','ps1','60','Final')
INSERT into Exam_Result VALUES('11147','Mr.Mack28','CIV','3','PS1','ps1','70','Final')
INSERT into Exam_Result VALUES('11147','Mr.Mack28','CIV','3','PS1','ps1','80','Final')
INSERT into Exam_Result VALUES('11147','Mr.Mack28','CIV','3','PS1','ps1','20','Internal')
INSERT into Exam_Result VALUES('11147','Mr.Mack28','CIV','3','PS1','ps1','22','Internal')

Student_Info Tabelle: benutze ich nur für abgerufene Student_Information wie Enroll_Number...

CREATE TABLE [dbo].[Stud_Info](
    [Enroll_Number] [varchar](20) NOT NULL,
    [Student_Name] [varchar](20) NULL,
    [Course_Type] [varchar](20) NULL,
    [Course_Id] [varchar](20) NULL,
    [Semester] [varchar](20) NULL,
    [Password] [varchar](20) NULL,
    [Gender] [varchar](6) NULL,
    [City] [varchar](15) NULL,
    [Mobile_Number] [varchar](10) NULL)

Es gibt mir eine Fehlermeldung wie diese...

Msg 8117, Ebene 16, Status 1, Zeile 1
Operanden Daten vom Typ varchar ist ungültig für sum operator.

Rahul: wie pro Sie die Antwort, die ich aktualisiert, aber es gibt noch Fehler......

set @query = @query + ' pivot(sum(cast(MarksObtained as int)) for Subject_Name in (' + @subjects + ')) as pvt'  

Ich denke, es gibt Fehler, weil ich bin mit der MarksObtained Spalte (as varchar(10))...plz geben Sie mir einen Vorschlag, wie ich die konvertieren kann varchar zu int so, dass die Summe der insgesamt MarksObtained getan werden kann.

InformationsquelleAutor mack28 | 2012-05-05