CASE statement question - 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 2000 Forums

SQL Server Development (2000)

CASE statement question

Author

Topic

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-21 : 17:17:25

I want to be able to pass a variable to my sproc and based upon its value, assign other values to declared variables to insert to a table.
The variable is @AbbSel
I try :
CASE @AbbSel
WHEN 'V'
THEN
SET @game = [Vis] + 'vs.' [Home]
SET @sport = 'MLB'
WHEN 'H'
THEN
SET @game = [Vis] + 'vs.' [Home]
SET sport = 'MLB'
END CASE
[Vis] and [Home] are field values from a selected table. I simply take some of those values and plan to insert to a different table.
I tried with IF..Then but seem to run into a problem there. I have tried quite a few iterations. Yesterday, I learned that you have to have a matching Else with an If. My If expression by itself would not work without using an Else. But I have 4 cases to evaluate here and no idea how to do it with SQL. Somebody educate me please.
Appreciate it.
John

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-21 : 17:59:26

if @AbbSel = 'V'
begin
select @game = [Vis] + 'vs.' + [Home]
, @sport = 'MLB'
from tbl
end
else if @AbbSel = 'H'
begin
select @game = [Vis] + 'vs.' + [Home]
, sport = 'MLB'
from tbl
end

or

select @game = case @AbbSel
when 'V' then [Vis] + 'vs.' + [Home]
when 'H' then [Vis] + 'vs.' [Home]
end,
@sport = case @AbbSel
when 'V' then 'MLB'
when 'H' then 'MLB'
end
from tbl

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

John T.
Posting Yak Master

112 Posts

Posted - 2003-04-21 : 19:37:31

Thanks very much. A great help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources