Using CASE to execute one of two SQL statements

Question

Using CASE to execute one of two SQL statements

stephen mehl

Mr or Mrs. 500

Points: 500
More actions
August 23, 2012 at 10:20 pm

#96856

Hello --
Can CASE be used as follows?
CASE
WHEN expression is TRUE THEN
SQLstatement#1
ELSE
SQLstatement#2
END
Thanks for any help,
Larry Mehl

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

rhythmk SSCertifiable Points: 7271 More actions · Answer 1

stephen mehl (8/23/2012)
Hello --
Can CASE be used as follows?
CASE
WHEN expression is TRUE THEN
SQLstatement#1
ELSE
SQLstatement#2
END
Thanks for any help,
Larry Mehl

Please have a look on below URL.

http://msdn.microsoft.com/en-us/library/ms181765.aspx

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

stephen mehl Mr or Mrs. 500 Points: 500 More actions · Answer 2

stephen mehl

Mr or Mrs. 500

Points: 500

August 23, 2012 at 10:53 pm

#1529546

Thank you rhythmk

Larry

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

stephen mehl (8/23/2012)
Hello --
Can CASE be used as follows?
CASE
WHEN expression is TRUE THEN
SQLstatement#1
ELSE
SQLstatement#2
END
Thanks for any help,
Larry Mehl

No, CASE is a scalar function/expression and not a statement, however the IF statement can do what you want.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

GSquared SSC Guru Points: 260824 More actions · Answer 4

Alternatively, you can set a variable to a statement and then execute it.

declare @Cmd varchar(1000);

select @Cmd =

case

when X = Y then 'sql statement 1'

else 'sql statement 2'

end;

exec (@Cmd);

That kind of thing can work. If ... Else ... is probably easier though.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

stephen mehl Mr or Mrs. 500 Points: 500 More actions · Answer 5

SSCrazy Eights and SS Champion --

Thank you for replying.

Yes, IF EXISTS looks like the way to go.

Larry

stephen mehl Mr or Mrs. 500 Points: 500 More actions · Answer 6

I spoke too soon.

I need multiple uses ... SQL does not like the second instance of "IF"

IF EXISTS (..)

summarize the data - 1

ELSE

add a row of 0s - 1

UNION

IF EXISTS (..)

summarize the data - 2

ELSE

add a row of 0s - 2

Does anyone know how to accomplish this in one .SQL file?

Thanks in advance for any help,

Larry

Sean Lange SSC Guru Points: 286609 More actions · Answer 7

stephen mehl (8/24/2012)
I spoke too soon.
I need multiple uses ... SQL does not like the second instance of "IF"
IF EXISTS (..)
summarize the data - 1
ELSE
add a row of 0s - 1
UNION
IF EXISTS (..)
summarize the data - 2
ELSE
add a row of 0s - 2
Does anyone know how to accomplish this in one .SQL file?
Thanks in advance for any help,
Larry

You can't use IF like that in sql. There are multiple ways you can handle this but from what you posted it is hard to say what the best approach is.

You could split this into two sections.

create temp table

IF EXISTS (..)

insert summarize the data - 1 into the temp table

ELSE

insert a row of 0s - 1 to the temp table

IF EXISTS (..)

insert summarize the data - 2 into the temp table

ELSE

insert a row of 0s - 2 to the temp table

select from temp table

Keep in mind that IF is used to control flow. It is NOT part of a query.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

stephen mehl Mr or Mrs. 500 Points: 500 More actions · Answer 8

stephen mehl

Mr or Mrs. 500

Points: 500

August 25, 2012 at 9:57 am

#1530061

Sean --

Thank you.

That will work for me.

Larry