Conditional when - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Conditional when

Author

Topic

kensai
Posting Yak Master

172 Posts

Posted - 2008-06-23 : 09:06:54

Is anything similar to this possible without writing whole query in every begin block or without dynamic sql:

proc(@arg1 bit)

select * from table where x=y
if @arg1 = 1 begin
and when a=b
end
order by date

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-23 : 09:18:23

SELECT *
FROM Table
WHERE x = y
AND (A = B OR @arg1 = 0)
order by date

E 12°55'05.25"
N 56°04'39.16"

kensai
Posting Yak Master

172 Posts

Posted - 2008-06-24 : 04:03:36

Thank you Peso.

kensai
Posting Yak Master

172 Posts

Posted - 2008-06-24 : 04:27:00

Ok what about multiple condition, like:

select * from table where x=y
case @arg
when 1 then and where a=1
when 2 then and where a=2
end case

is this possible?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 04:30:59

apply similar logic

select * from table
where x=y
and a=@arg

kensai
Posting Yak Master

172 Posts

Posted - 2008-06-24 : 05:53:37

quote:
Originally posted by visakh16

apply similar logic

select * from table
where x=y
and a=@arg

Haha sorry my example was stupid I just realized that. This would be better:

select * from table where x=y
case @arg
when 1 then and where a=b
when 2 then and where a=c
end case

I know it doesn't make much sense in the example but it's the principle I'd like learn, if possible that is.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-24 : 05:55:45

select * from table
where x=y
and (a=b or @arg<>1)
and (a=c and @arg<>2)
...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources