Case Statements in SQL Server 2000

Help 127.1.1.1 wrote:
> Hello,
> I have a stored proc that has a integer parameter (@case_number)
> used to determine what SQL query to execute based on the parameter as
> well as another parameter (@var1). However, I try to compile the
> following code and I get errors:
>
> CASE WHEN @case_number = 1 THEN SELECT a.tmp from tester where rell =
> @var1 WHEN @case_number = 2 THEN Select * from tester
> ELSE SELECT 'Nothing found'
> END
>
> I get errors such as:
> Incorrect syntax near the keyword 'CASE'.
> Incorrect syntax near the keyword 'ELSE'.

CASE can only return a single value (think of it as a function that
doesn't use the standard function syntax). It can't be used to control
program flow.

What you'd want would be:

IF @case_number = 1 BEGIN
SELECT a.tmp FROM tester WHERE rell = @var1
END ELSE IF @case_number = 2 BEGIN
SELECT * FROM tester
END ELSE BEGIN
SELECT 'Nothing found'
END

> Can I use the case statement in SQL like we do in programming code?

No.

> I
> can get around this by doing if else statements but 1. I want this to
> be efficient because once I have the correct case number I don't need
> to go check to see and execute the other queries after I have
> finished executing the correct query. Any help is appreciated.

By using if/else if/else if... the above won't continue to check after
it has found a branch of the code to execute.

--
Colin McGuigan