1

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'.

CoolUserName
  • 3,715
  • 6
  • 26
  • 30
  • why not use an if then else (http://technet.microsoft.com/en-us/library/ms182587.aspx) – xQbert Aug 15 '13 at 18:56
  • what you are doing with with results of this case ? seems like you can come up with one query which uses case for a column and/or inside where and order clause. – vittore Aug 15 '13 at 18:59

5 Answers5

3

Use IF...ELSE syntax for control flow:

DECLARE @myVar int;
SET @myVar = 1;
IF @myVar = 0
    SELECT 0;
ELSE
    SELECT GETDATE();
Bryan
  • 17,112
  • 7
  • 57
  • 80
2

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()
Low Chee Mun
  • 610
  • 1
  • 4
  • 9
1

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.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
1

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?)

Community
  • 1
  • 1
KekuSemau
  • 6,830
  • 4
  • 24
  • 34
0

Use this format:

IF (@myVar = 0)
BEGIN
  SELECT <something>
END
ELSE
BEGIN
  SELECT <something else>
END
BWS
  • 3,786
  • 18
  • 25