SQL Server Conditional where logic

4bbkushb 于 2023-11-16 发布在其他

关注(0)|答案(2)|浏览(140)

I have a SQL Server stored procedure where I want to add a conditional clause based on a parameter ( @action ). So if a action is X or Y then I don't want to use the where clause (and want to fetch all records), but if the action is NOT X OR Y then I want to fetch only those records that match with other parameter ( @username ).

I am trying this:

AND CASE 
        WHEN (ISNULL(@action, '') = 'X' OR ISNULL(@action, '') = 'Y')
            THEN a.name 
            ELSE a.name 
    END = @username

This seems to work ok if the action is not X or Y, however it does not return any data if the action is X.

How can I return all records (basically ignore the Where clause) if the action is X or Y?

sql-server

来源：https://stackoverflow.com/questions/77346042/conditional-where-logic

2条答案

按热度按时间

c7rzv4ha1#

Just use plain conditions - essentially expressing your English description using SQL:

WHERE (@action NOT IN ('X', 'Y') OR a.name = @username)
-- plus any other conditions

赞(0）回复(0）举报 2023-11-16

1qczuiv02#

maybe this -

AND (CASE 
        WHEN (ISNULL(@action, '') = 'X' OR ISNULL(@action, '') = 'Y')  THEN 1
        WHEN ISNULL(@action, '') <> 'X' AND  ISNULL(@action, '') <> 'Y' AND a.name = @username THEN 1
            ELSE 0 
    END) = 1

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server Conditional where logic

2条答案

相关问题

热门标签

最新问答