CODEWITHSUNDEEP
sqlsql-servert-sql
CoolUserName
CoolUserName

Reputation: 3755

Is it possible to change which SELECT statement is run with a case statement

I have a stored procedure that I want to perform a different select based on the result stored in a local variable. My use case is simply that, on certain results from a previous query in the stored procedure, I know the last query will return nothing. But the last query is expensive, and takes a while, so I'd like to short circuit that and return nothing.

Here is a mock-up of the flow I want to achieve, but I get a syntax error from SQL Management Studio

DECLARE @myVar int;
SET @myVar = 1;
CASE WHEN @myVar = 0
THEN
    SELECT 0 0
ELSE
    SELECT getDate()
END

The error is: Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'CASE'. Msg 102, Level 15, State 1, Line 8 Incorrect syntax near 'END'.

Upvotes: 1

Views: 1926

Answers (5)

Low Chee Mun
Low Chee Mun

Reputation: 610

brother, for CASE function, it can only return single value such as string, in order to execute different query based on certain condition, if else will be the options.

DECLARE @myVar INT
SET @myVar = 1
IF @myVar = 0
SELECT '0 0'
ELSE
SELECT GETDATE()

Upvotes: 2

BWS
BWS

Reputation: 3846

Use this format:

IF (@myVar = 0)
BEGIN
  SELECT <something>
END
ELSE
BEGIN
  SELECT <something else>
END

Upvotes: 0

KekuSemau
KekuSemau

Reputation: 6853

I guess it should be

DECLARE @myVar int;
SET @myVar = 1;
IF @myVar = 0
    SELECT 0;
ELSE
    SELECT getDate();

And if you are just learning some syntax elements, you should also start right away to end every statement with a semicolon.
(When should I use semicolons in SQL Server?)

Upvotes: 1

Bryan
Bryan

Reputation: 17703

Use IF...ELSE syntax for control flow:

DECLARE @myVar int;
SET @myVar = 1;
IF @myVar = 0
    SELECT 0;
ELSE
    SELECT GETDATE();

Upvotes: 3

HLGEM
HLGEM

Reputation: 96610

Use IF not CASE if you want to have differnt select statments. IF is a control flow item, case is for picking alternatives from within a select only.

Upvotes: 1

Related Questions