CODEWITHSUNDEEP
sqlsql-server
DotNetBeginner
DotNetBeginner

Reputation: 439

SQL where clause case

SELECT     
*
FROM  tbl_Something
WHERE 
RoleID = 1 AND 
GroupID = 
    CASE
        WHEN @GroupID = 1 THEN @GroupID OR GroupID IS NULL -- issue here
        WHEN @GroupID = 2 THEN @GroupID

What I want to do is when @GroupID = 2 then GroupID = @GroupID meaning get all the rows with groupID = 2 when @GroupID == 1 then GroupID = @GroupID or GroupID IS NULL meaning gets all rows with GroupID = 1 or GroupID IS NULL

Where am i going wrong

Upvotes: 1

Views: 85

Answers (4)

Crono
Crono

Reputation: 10478

Why using CASE WHEN at all when you can do this:

SELECT     
    *
FROM
    tbl_Something
WHERE 
    RoleID = 1 AND 
    ISNULL(GroupID, 1) = @GroupID

... unless you need to make sure the query must not return anything if @GroupID is anything else than 1 or 2, in which case the following would be far better:

IF @GroupId IN (1, 2)
BEGIN
    SELECT     
        *
    FROM
        tbl_Something
    WHERE 
        RoleID = 1 AND 
        ISNULL(GroupID, 1) = @GroupID
END

Upvotes: 1

bobthedeveloper
bobthedeveloper

Reputation: 3783

You can use ISNULL for this. It will test if GroupId is equal to @GroupId, if GroupId is null it will check if 1 is equal to @GroupID

ISNULL(GroupID,2) = @GroupID

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726489

You need to split up the condition, because OR GroupID IS NULL cannot be part of an expression that selects a GroupID for comparison. Since you have only two possibilities for @GroupID, it would be cleaner to rewrite your query without CASE altogether:

SELECT     
*
FROM  tbl_Something
WHERE 
RoleID = 1 AND (
    (@GroupID = 1 AND GroupID = 1)
    OR
    (@GroupID = 2 AND (GroupID = 2 OR GroupID IS NULL))
)

Upvotes: 2

crthompson
crthompson

Reputation: 15865

Use Coalesece, or IsNull:

WHEN Coalesce(@GroupID, 1) = 1 THEN 1

Additionally, notice that when you are testing for 1, its easier to read if you just return 1.

Upvotes: 1

Related Questions