CODEWITHSUNDEEP
sql-server
Gunaseelan
Gunaseelan

Reputation: 15515

Case in where class - stored procedure

I have return the following SP

ALTER PROCEDURE [dbo].[GetStudentsByUserId]
    @userId UNIQUEIDENTIFIER,
    @roleType bit
AS
SET NOCOUNT ON;
    DECLARE @classId UNIQUEIDENTIFIER

    EXEC [dbo].[GetClassId] @userId,@classId OUTPUT

    SELECT 
        B.StudentId, 
        B.StudentName, 
        B.Description, 
        B.StudentType
    FROM 
        ClassStudents A
    INNER JOIN 
        Student B
    ON 
        B.StudentId = A.StudentId 
    WHERE 
        A.CassId = @classId
    AND 
    ( 
        B.StudentType =
        CASE 
            @roleType
            when 1 
            then 1
            when 2
            then 2
        END
    OR 
        B.StudentType =
        CASE 
            @roleType
            when 1
            then 4
            when 2
            then 2
        END
    )

When I run this as following 

exec [GetStudentsByUserId] '28f95465-f30f-4b8b-cc92-d4c4e8d73273', 1

It returns the students with student type 1. And when I run this as following 

exec [GetStudentsByUserId] '28f95465-f30f-4b8b-cc92-d4c4e8d73273', 2

Also it returns the students with student type 1. But I need students with type 2.

I just want to get students with type 1, 4 when I pass roleType as 1, and want to get students with type 2, when I pass roleType as 2.

Any better solutions are highly appreciable.

Upvotes: 0

Views: 53

Answers (2)

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

Passing 2 to bit type is not possible. You need to change your where clause like below. 

ALTER PROCEDURE [dbo].[GetStudentsByUserId]
        @userId UNIQUEIDENTIFIER,
        @roleType bit
    AS
    SET NOCOUNT ON;
        DECLARE @classId UNIQUEIDENTIFIER

        EXEC [dbo].[GetClassId] @userId,@classId OUTPUT

        SELECT 
            B.StudentId, 
            B.StudentName, 
            B.Description, 
            B.StudentType
        FROM 
            ClassStudents A
        INNER JOIN 
            Student B
        ON 
            B.StudentId = A.StudentId 
        WHERE 
            A.CassId = @classId
        AND 
        ( 
                   (B.StudentType in (2) and 
            and      @roleType=1)
        OR 
            (B.StudentType in (1,4) and 
            and      @roleType=0)
        )

Upvotes: 0

user5246440
user5246440

Reputation:

Your input parameter @roleType is bit datatype.
According to MSDN:

An integer data type that can take a value of 1, 0, or NULL.

But you check it on value 2 that always will be equals 1

Upvotes: 2

Related Questions