0

In my stored procedure, I want to fetch data where StudentElectivePaper.SubjectPaperID is equal to passed parameter value or has no value at all (null value).

I have tried OR statement like

StudentElectivePaper.SubjectPaperID = @SubjectPaperID OR StudentElectivePaper.SubjectPaperID IS NULL

but it doesn't yield fruitful result. Please help! 

ALTER PROCEDURE [dbo].[StudentElectiveMappings]
    @SubjectPaperID INT,
    @ExamPaperID INT,
    @ExamID INT,
    @SubjectElectiveID INT,
    @ExamPaperTypeID INT,
    @LCID INT = NULL
AS
BEGIN
    SELECT DISTINCT
        ViewStudentExamApplicant.StudentID,
        ViewStudentExamApplicant.ExamRollNo,
        CASE 
           WHEN StudentElectivePaper.ID IS NULL 
              THEN CAST(0 AS BIT) 
              ELSE CAST(1 AS BIT) 
        END AS 'IsMapped'
    FROM
        StudentElectivePaper
    RIGHT OUTER JOIN 
        ViewStudentExamApplicant ON StudentElectivePaper.ExamPaperID = ViewStudentExamApplicant.ExamPaperID
                                 AND StudentElectivePaper.ExamID = ViewStudentExamApplicant.ExamID
                                 AND ViewStudentExamApplicant.StudentID = StudentElectivePaper.StudentID
    WHERE 
        ViewStudentExamApplicant.ExamID = @ExamID
        AND ViewStudentExamApplicant.ExamPaperID = @ExamPaperID
        AND StudentElectivePaper.SubjectPaperID = @SubjectPaperID
        AND ViewStudentExamApplicant.ExamPaperTypeID = @ExamPaperTypeID
        AND StudentElectivePaper.SubjectElectiveID = @SubjectElectiveID
        AND (ViewStudentExamApplicant.LCID = @LCID OR @LCID IS NULL)
    ORDER BY 
        IsMapped DESC, ViewStudentExamApplicant.ExamRollNo
gulshan arora
  • 371
  • 1
  • 8
Binod
  • 11
  • 5
  • 2
    RIGHT OUTER JOIN? Most people find `main table left join option data` much easier to understand than `optional data right join main table`. – jarlh Jul 24 '19 at 10:17
  • BTW, your right join returns inner join result anyway. Move the StudentElectivePaper conditions from WHERE to ON to get true outer join result. – jarlh Jul 24 '19 at 10:18
  • Show us the whole "IS NULL" query attempt. (Missing parentheses perhaps...) – jarlh Jul 24 '19 at 10:19
  • 2
    Aliasing, *good* use of whitespace and line breaks would really improve the readability of that query... (As can be seen in this [db<.fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=b4afa0de5237b70ff594b4f07c1e61c1)) – Thom A Jul 24 '19 at 10:21
  • Comments are not the place for information that should be in your question @Binod – Thom A Jul 24 '19 at 10:23
  • 2
    Also, as an FYI; SQL Server 2008 is now completely unsupported. You really should be looking at upgrade paths now. The longer you leave it, the more security vulnerabilities you'll face, and the harder it'll be to upgrade when you do. – Thom A Jul 24 '19 at 10:24

0 Answers0