Case EXISTS on where condition

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

General SQL Server Forums

New to SQL Server Programming

Case EXISTS on where condition

Author

Topic

aoriju
Posting Yak Master

156 Posts

Posted - 2010-07-13 : 06:02:05

Dear all,
I have a parameter @test Varchar(10);
SET @test ='A'
My requirement is
IN where condition depending upon values of @test i want to check two conditions
that is
WHEN @test ='A' THEN EXISTS (SELECT a from TABLE A)
ELSE EXISTS (SELECT a FROM TABLE B)

Replay me ASAP

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-13 : 06:10:12

Like this?
where exists(select a from table a where 1=1 and @test='A')
or exist(select a from table b where 1=1 and @test <> 'A')

No, you're never too old to Yak'n'Roll if you're too young to die.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-13 : 16:33:09

i prefer this:


declare @test varchar(10) = 'A'
if @test = 'A'
	select * from TableA
else
    select * from TableB

aoriju
Posting Yak Master

156 Posts

Posted - 2010-07-14 : 02:47:58

If else structure ok...but i am looking for case statement in where clause(to reduce the code size)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-07-14 : 03:07:04

quote:
Originally posted by webfred

Like this?
where exists(select a from table a where 1=1 and @test='A')
or exist(select a from table b where 1=1 and @test <> 'A')

No, you're never too old to Yak'n'Roll if you're too young to die.

No, you're never too old to Yak'n'Roll if you're too young to die.

Devart
Posting Yak Master

102 Posts

Posted - 2010-07-14 : 04:13:19

Hello,

quote:
declare @test varchar(10) = 'A'

declare @test varchar(10)
set @test = 'A'

Best regards,

Devart Team

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-07-14 : 04:37:33

quote:
Originally posted by Devart

Hello,

quote:
declare @test varchar(10) = 'A'

declare @test varchar(10)
set @test = 'A'

Best regards,

Devart Team

In SQL server 2008 we can assign default value to the variable while
it declare.

So its acceptable!

declare @test varchar(10) = 'A'

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-16 : 11:40:39

SELECT a from TABLEA where @test='A'
union all
SELECT a from TABLEB where @test<>'A'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources