SQL Server Conditional WHERE clause based on variable [duplicate]

wvt8vs2t 于 2023-08-02 发布在其他

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

This question already has an answer here:

How to create a dynamic order by query with with multiple fields (1 answer)
Closed 12 days ago.

I have something like this:

declare @cucu varchar(10)
select @cucu = 'c'

create table #t(id int, title varchar(50), p bit)
insert into #t(id, title, p) values (1, 'alpha', 0)
insert into #t(id, title, p) values (2, 'beta', 1)
insert into #t(id, title, p) values (3, 'gamma', 0)

if (@cucu = 'a')
begin
    select  *
    from    #t
    where   p = 0
end
else if (@cucu = 'b')
begin
    select  *
    from    #t
    where   p = 1
end
else
begin
    select  *
    from    #t
end

drop table #t

Is there a way to have those 3 cases treated in a CASE/WHEN/END on the WHERE of the select * from #t query somehow?

If I create an other variable, like this:

declare @b bit; select @b = 0; if @cucu = 'b' select @b = 1

then the first 2 cases are simple to have in one query:

select  *
from    #t
where   p = @b

But for the 3rd possibility, I don't know how to do this

sql-server

来源：https://stackoverflow.com/questions/76728771/conditional-where-clause-based-on-variable

2条答案

按热度按时间

q43xntqr1#

You would need to use OR and AND clauses. You'd also likely want to include a RECOMPILE in the OPTION clause, as the query plans for the 3 queries could be quite different (as I assume this would be run against a much larger table):

SELECT id,
       title,
       p
FROM #t
WHERE (p = 0 AND @cucu = 'a')
   OR (p = 1 AND @cucu = 'b')
   OR (@cucu NOT IN ('a','b')) --Assumes @cucu isn't going to have a NULL value
OPTION (RECOMPILE);

赞(0）回复(0）举报 2023-08-02

bn31dyow2#

In my perspective, I think you are trying to execute SELECT statement based on the value of variable '@cucu'.

Here is my approach with CASE/WHEN for your problem assuming you are working in SSMS.

DECLARE @cucu varchar(10)
SET @cucu = 'c'
SELECT * FROM #t WHERE (CASE WHEN @cucu = 'a' THEN 0 WHEN @cucu = 'b' THEN 1 ELSE p END) = p

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Conditional WHERE clause based on variable [duplicate]

2条答案

相关问题

热门标签

最新问答