solve incorect syntax near )

shiji josh

7y986SQL

exec('INSERT INTO #ExamMarkEntryTotal SELECT SYD.StudentID,EM.ExamID,-1,2,ECSD.SubjectID,0 AS SubjectDivisionId,ESGM.SubjectGroupID,ESGM.EntryTypeId,

ECSD.PriorityNo,ROUND(CASE WHEN ME.Mark<0 THEN 0 ELSE ME.Mark END,1),ECSMD.MaximumMark,ECSMD.MinimumMark,ME.Remark,ECSD.ClasswiseSubjectId,ECGD.ExamGroupID,ME.Mark,ECSMD.Weightage

FROM StudentAcademicYearWisedetails SYD INNER JOIN ClassSectionDetails CSD ON CSD.ClassID=SYD.ClassId AND

CSD.SectionID=SYD.SectionID AND SYD.AcademicYearID=CSD.AcademicYearID INNER JOIN ExamClasswiseSubjectDetails ECSD ON CSD.ClassDetailID=ECSD.ClassDetailID

INNER JOIN ExamClasswiseSubjectMarkDetails ECSMD ON ECSD.ClasswiseSubjectID=ECSMD.ClasswiseSubjectId

LEFT JOIN ExamOptionalSubjectDetails OSD ON OSD.ClassDetailID=ECSD.ClassDetailID AND OSD.SubjectID=ECSD.SubjectId

LEFT JOIN ExamStudentOptionalSubjectDetails SOSD ON SOSD.StudentID=SYD.StudentID AND SOSD.SubjectID=ECSD.SubjectId

LEFT JOIN ExamMarkEntry ME ON ME.StudentId=SYD.StudentID AND ME.ClasswiseSubjectId=ECSD.ClasswiseSubjectID

LEFT JOIN ExamMarkAbbreviationMaster MA ON MA.ExamMarkAbbreviationID=ME.Mark

INNER JOIN ExamMaster EM ON EM.ExamID=ECSD.ExamID INNER JOIN

ExamClassGroupDetails ECGD ON ECGD.ClassDetailID=CSD.ClassDetailID INNER JOIN

ExamGroupExamDetails EGED ON ECSD.ExamID=EGED.ExamID AND EGED.ExamGroupID=ECGD.ExamGroupID INNER JOIN

ExamSubjectMaster ESM ON ESM.SubjectID=ECSD.SubjectID INNER JOIN ExamSubjectGroupMaster ESGM ON

ESGM.SubjectGroupID=ESM.SubjectGroupID

WHERE ESM.SSubjectID NOT IN ('+@AttSubjectID+') AND ESM.SubjectTypeID=1 AND (OSD.SubjectID is null OR SOSD.SubjectID is not null) AND SYD.AcademicYearID='+@AcademicYearID+' AND ESGM.EntryTypeId=1 AND CSD.ClassID='+@ClassID+' AND CSD.SectionID='+@SectionID+' AND

EM.ExamID IN ('+@Exams+') '+@strQry+'')

3 Replies

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

Priyanka Jain7y ago
As your query is complex i have formatted and make it conditional. The error you described is may be due to some variable is not having values and query is not creating properly so i have updated code please review and use it accordingly.
DECLARE @AttSubjectID INT
DECLARE @Exams VARCHAR(50)
DECLARE @strQry VARCHAR(50)
DECLARE @AcademicYearID INT
DECLARE @ClassID INT
DECLARE @SectionID INT
DECLARE @execQuery NVARCHAR(MAX)
SET @execQuery= CONCAT('
INSERT INTO #ExamMarkEntryTotal
SELECT SYD.StudentID,
       EM.ExamID,-1,2,
       ECSD.SubjectID,0 AS SubjectDivisionId,
       ESGM.SubjectGroupID,
       ESGM.EntryTypeId,
       ECSD.PriorityNo,
       ROUND(CASE WHEN ME.Mark=0 THEN 0 ELSE ME.Mark END,1),
       ECSMD.MaximumMark,
       ECSMD.MinimumMark,
       ME.Remark,
       ECSD.ClasswiseSubjectId,
       ECGD.ExamGroupID,
       ME.Mark,
       ECSMD.Weightage
FROM StudentAcademicYearWisedetails SYD
INNER JOIN ClassSectionDetails CSD ON CSD.ClassID=SYD.ClassId AND CSD.SectionID=SYD.SectionID AND SYD.AcademicYearID=CSD.AcademicYearID
INNER JOIN ExamClasswiseSubjectDetails ECSD ON CSD.ClassDetailID = ECSD.ClassDetailID
INNER JOIN ExamClasswiseSubjectMarkDetails ECSMD ON ECSD.ClasswiseSubjectID = ECSMD.ClasswiseSubjectId
LEFT JOIN ExamOptionalSubjectDetails OSD ON OSD.ClassDetailID = ECSD.ClassDetailID AND OSD.SubjectID = ECSD.SubjectId
LEFT JOIN ExamStudentOptionalSubjectDetails SOSD ON SOSD.StudentID = SYD.StudentID AND SOSD.SubjectID = ECSD.SubjectId
LEFT JOIN ExamMarkEntry ME ON ME.StudentId = SYD.StudentID AND ME.ClasswiseSubjectId = ECSD.ClasswiseSubjectID
LEFT JOIN ExamMarkAbbreviationMaster MA ON MA.ExamMarkAbbreviationID = ME.Mark
INNER JOIN ExamMaster EM ON EM.ExamID=ECSD.ExamID
INNER JOIN ExamClassGroupDetails ECGD ON ECGD.ClassDetailID=CSD.ClassDetailID
INNER JOIN ExamGroupExamDetails EGED ON ECSD.ExamID=EGED.ExamID AND EGED.ExamGroupID=ECGD.ExamGroupID
INNER JOIN ExamSubjectMaster ESM ON ESM.SubjectID=ECSD.SubjectID
INNER JOIN ExamSubjectGroupMaster ESGM ON ESGM.SubjectGroupID=ESM.SubjectGroupID',' ');
IF (@AttSubjectID IS NOT NULL)
BEGIN
    IF CHARINDEX('WHERE',@execQuery) = 0
    BEGIN
        SET @execQuery = CONCAT(@execQuery,'WHERE ')
    END
    ELSE
    BEGIN
        SET @execQuery = CONCAT(@execQuery,' AND ')
    END
    SET @execQuery = CONCAT(@execQuery,'ESM.SSubjectID NOT IN (',CAST(@AttSubjectID AS VARCHAR(10)),') AND ESM.SubjectTypeID=1 AND (OSD.SubjectID IS NULL OR SOSD.SubjectID IS NOT NULL)');
END

IF @AcademicYearID IS NOT NULL
BEGIN
    IF CHARINDEX('WHERE',@execQuery) = 0
    BEGIN
        SET @execQuery = CONCAT(@execQuery,'WHERE ')
    END
    ELSE
    BEGIN
        SET @execQuery = CONCAT(@execQuery,' AND ')
    END
    SET @execQuery = CONCAT(@execQuery,'SYD.AcademicYearID =',CAST(@AcademicYearID AS VARCHAR(10)),' AND ESGM.EntryTypeId = 1' );
END
IF @ClassID IS NOT NULL
BEGIN
    IF CHARINDEX('WHERE',@execQuery) = 0
    BEGIN
        SET @execQuery = CONCAT(@execQuery,'WHERE ')
    END
    ELSE
    BEGIN
        SET @execQuery = CONCAT(@execQuery,' AND ')
    END
    SET @execQuery = CONCAT(@execQuery,'CSD.ClassID=', CAST(@ClassID AS VARCHAR(10)))
END
IF @SectionID IS NOT NULL
BEGIN
    IF CHARINDEX('WHERE',@execQuery) = 0
    BEGIN
        SET @execQuery = CONCAT(@execQuery,'WHERE ')
    END
    ELSE
    BEGIN
        SET @execQuery = CONCAT(@execQuery,' AND ')
    END
    SET @execQuery= CONCAT(@execQuery,'CSD.SectionID=',CAST(@SectionID AS VARCHAR(10)))
END
IF @Exams IS NOT NULL
BEGIN
    IF CHARINDEX('WHERE',@execQuery) = 0
    BEGIN
        SET @execQuery = CONCAT(@execQuery,'WHERE ')
    END
    ELSE
    BEGIN
        SET @execQuery = CONCAT(@execQuery,' AND ')
    END
    SET @execQuery = CONCAT(@execQuery,'EM.ExamID IN (',@Exams,')')
END

IF @strQry IS NOT NULL
BEGIN
    SET @execQuery = CONCAT(@execQuery,@strQry)
END

EXEC (@execQuery)
Nirav Gandhi7y ago
Please provide proper details.
I would suggest use "SELECT" command instand of Print. Some time you will not get complete query in "Print" command(Specially Query is complex).
Siddhartha Sharma7y ago
Hey Shiji,

you can print dynamic query with print statement. and easily catch the error.
Please try with proper indentation so you got the error properly.