passing case statements into a variable possible???

Question

passing case statements into a variable possible???

AirBasi

SSC-Addicted

Points: 463
More actions
August 3, 2010 at 1:30 pm

#224973

Greetings SQL Gurus,
I have a quick question. Anyone know how, or if it is possible, to pass a case statement into a variable? lets say I have the following:
declare @myvariable
set = "case statement"
Select a, b, @myvariable as c
from mytable
anyway to set the variable = to a query with a case statement??? I know this can be done inside SSIS using the execute sql task, but how about using SSMS???
HELP!??!
Al

Viewing 4 posts - 1 through 3 (of 3 total)

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

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

You're not entirely clear about what you're wanting to be dynamic, but if you want to set tables that you're going to query or columns you're going to return based on a dynamic condition, you have to do so through the use of dynamic SQL. However, it's often the case that people think they need to make certain things dynamic when they actually don't.

If you give some detail about what you're trying to do you are more likely to get practical feedback.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

CELKO (8/3/2010)
There is no such thing as a CASE statement in SQL. We have a CASE expression . Expressions return scalar values.
...
I hope you know better than to try to pass a quoted string and expect it to compile, as if you were in some versions of BASIC.
...

1. Expression vs Statement - it is a naming preference...

In MS BOL for Transact SQL, it is called as expression (i guess it is due to nicer language while explaining how it is used in SELECT, INSERT, etc statements). However, it is refered as CASE statement in SQL Server MDX (http://msdn.microsoft.com/en-us/library/ms144841.aspx). Also, you can google for "CASE statement SQL" and surely will find a lot of article where it is reffered as "CASE statement"...

2. You can expect passed quoted string to be compiled and executed when you pass it to the dedicated EXECUTE statement or to sp_executesql system stored procedure 😀

OP, I'm not sure what you're trying to achieve here, but you should read more about using "dynamic SQL" ...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

AirBasi SSC-Addicted Points: 463 More actions · Answer 3

Yes, you are correct, it is a Case "Expression" and not a statement. Also the Dynamic SQL is what I am looking for. I was trying to explain to my Brother that Dynamic SQL is the correct way to go, but he kept telling me that you can pass a case expression directly into a variable, but then again he a C#/VB guy 🙂 Thanks for all the help and comments, they were helpful!