Case Statement
-
Hi friends,
I have a small doubt and would be greatfull, if solved.
My SP consist of many "If Then" statements, and as we know sql server does not support the stantdard methodology of CASE i.e. I always need to allocate some values using case.
I need a solution such that, for every condition satisfied, a set of statements need to be executed.
Please help.
Thanks in advance.
Deshii.
-
and as we know sql server does not support the stantdard methodology of CASE
Do we? AFAIK, CASE is pretty much ANSI SQL Standard.
Can you be more specific in your requirement?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
To add to what Frank has written try
SELECT CASE CONVERT(VARCHAR(10), GETDATE(), 101)
WHEN '05/30/2005' THEN 'Happy Memorial Day (US)'
WHEN '07/04/2005' THEN 'Happy Fourth of July (US)'
ELSE CONVERT(VARCHAR(10), GETDATE(), 101) END
which uses CASE and multiple different WHENs.... along with an ELSE.
Good Hunting!AJ Ahrens
webmaster@kritter.net -
People... that's not what Dashii needs... I think the mentioned Sp code must something like:
IF LogicalOne BEGIN
Statement 1
Statement 2
etc.
END
ELSE IF LogicalTwo BEGIN
Statement 1
Statement 2
etc.
END
ELSE IF ...
And the need is to use an standard case statement the way it's used in VisualBasic or Delphi or some programming language... I'll write this using SQL CASE Statament, but, be aware that's a pseudocode... it's not working actually...
CASE
WHEN LogicalOne THEN
BEGIN
Statement 1
Statement 2
etc.
END /*LogicalOne end*/
WHEN LogicalTwo THEN
BEGIN
Statement 1
Statement 2
etc.
END /*LogicalTwo End*/
...
END /*Case End*/
TO do something like this, there two options:
1) The dirty option... use IF statements, NOT Nested, and as final sentence put a "Goto MyLabel"... after last IF statement, put the label... "MyLabel:" this is not a good practice, but it will make the work...
2) if your code allow it, put the whole thing into a function, and replace the Gotos from dirty option with "return 0" and the "MyLable:" line with a "Return -1"
And theres is a final approach... leave it as nested IFs... if they are working...why change them... no?
I hope you find something useful or hepful...
Nicolas Donadio
-
That's what I meant. I would like to see some code and am interested what the standard methodology of CASE is.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi everyone,
Thank you for the help extended.
Basically what I want to describe the same as Nicolas Donadio has described i.e. the basic case statement in most of the software's syntax is in the following way
CASE
WHEN LogicalOne THEN
BEGIN
Statement 1
Statement 2
etc.
END /*LogicalOne end*/
WHEN LogicalTwo THEN
BEGIN
Statement 1
Statement 2
etc.
END /*LogicalTwo End*/
...
END /*Case End*/
unlike in SQL Server it goes to the statge that we need to assign a value
Ex.
SELECT @Counter = Case
WHEN LogicalOne THEN Assignment Value/Clause
WHEN LogicalTwo THEN Assignment Value/Clause
WHEN LogicalThree THEN Assignment Value/Clause
ELSE
END
We can see that SQL Server's CASE helps us to assign a value and always accompanies SELECT.
I would like to know/understand a logic which would work like the normal CASE statement, where in I can execute set of statements (conditional/assignment etc.) for each Logic.
Once again, thank you for the help extended.
Regards,
Deshii
-
I am looking for something like this
Declare @i int
Set @i = @parameterValue
Case @i
When @i=1 Then
Statement 1
Statement 2
.....
When @i=2 Then
Statement 1
Statement 2
.......
ELSE
When @i=1 Then
Statement 1
Statement 2
......
END --End of Case
Can we achieve this without using IF, as there are plenty of logics to be handled in each CASE statement.
Thanks...
-
Maybe somebody already said that but I'm gonna said it anyways : A case statement (in vb or C++) is just a big list of if elseif elseif elseif else end disguised by the word "Case".
If you don't wish to have 15 ifs included in one another (therefore creating an indentation nightmare, I suggest you do something like this) :
If 1=1
begin
print 'a'
--...
end
else if 1=2
begin
print 'b'
--...
end
else if 3=4
begin
print 'c'
--...
end
...
Otherwise it's not possible to use case to do something like this in sql server. Also this will cause a shortcurcuit to occur once any condition is met so you don't have to worry about performance.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply