Select case -- end select in Stored Proc

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)

Select case -- end select in Stored Proc

Author

Topic

abarsami
Yak Posting Veteran

68 Posts

Posted - 2004-02-18 : 20:40:03

I want to use the following in stored proc.

It works in vb -- but not in sql. Can it be done.

Select case @strint
case 1
select * from table a
case 2
select * from table b
end select

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2004-02-18 : 22:47:23

Can do it as dynamic sql in a CASE function such as


DECLARE @ExecStr VARCHAR(500)
SELECT @ExecStr = (CASE @StrInt
WHEN 1 THEN 'SELECT * FROM tableA'
WHEN 2 THEN 'SELECT * FROM TableB'
ELSE something
END)
EXEC(@ExecStr)

Or with IF statements.

Sarah Berger MCSD

SamC
White Water Yakist

3467 Posts

Posted - 2004-02-18 : 23:37:09

Sarah's solution will work, but this may work without dynamic SQL:

The CASE syntax in SQL is similar to VB, BUT it's actually an expression that returns scalar values, not recordsets.

You would need to build an IF statement to do the equivalent function in SQL

IF @strint = 1 BEGIN
SELECT * FROM TABLEA
END ELSE BEGIN
SELECT * FROM TABLEB
END

It's possible to pretzel other solutions (if the table return similar columns)

SELECT * FROM TABLEA
WHERE @strint = 1
UNION ALL
SELECT * FROM TABLEB
WHERE @strint <> 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources