CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlcase
Deeptechtons
Deeptechtons

Reputation: 11125

SELECT statement inside CASE clause in TSQL throws error

I had to fine tune a bunch of stored procedures and got one which had loads of IF ELSE blocks within them they had insert statements with transactions. Hence i modeled them using case to find that i get syntax errors try the below statement(a simple example of what is being done) but sure you will get the idea

DECLARE @rowcount int
SELECT @rowcount = -1
 CASE WHEN @rowcount = -1 THEN
    select 'I WON THE RACE'
 WHEN '2' 
    SELECT 'WHAT A LOSER'
 END

each cases have more than one statement, so i just encased them inside ( and ) but to noavail. Please correct my knowledge of Case in TSQL. (Using SQl Server 2005 if that helps)

Upvotes: 1

Views: 13155

Answers (4)

Nighil
Nighil

Reputation: 4129

a suggestion only you can also use set set @rowcount = -1;

DECLARE @rowcount int
set @rowcount = -1;
 select  CASE @rowcount WHEN -1 THEN
     'I WON THE RACE'
 WHEN '2' then
    'WHAT A LOSER'
 END Status

Upvotes: -1

egrunin
egrunin

Reputation: 25053

You are confusing T-SQL's SELECT...CASE with VisualBasic's SELECT CASE.

T-SQL:

The CASE expression is used to evaluate several conditions and return a single value for each condition. For example, it allows an alternative value to be displayed depending on the value of a column.

VB:

Select...Case runs one of several groups of statements, depending on the value of an expression.

It would be nice if there was an equivalent in T-SQL, but there isn't. Here's what's available in T-SQL.

Upvotes: 1

Derek
Derek

Reputation: 23228

From your comment, it sounds like you might want to use an IF statement if you're trying to do 2 different inserts/multiple statements?

IF @rowcount = -1
BEGIN
  <stmt1>
  <stmt2>
END

IF @rowcount = 2
BEGIN
  <stmt3>
  <stmt4>
END

Upvotes: 1

Adam Wenger
Adam Wenger

Reputation: 17540

You want your CASE statement to look like this:

DECLARE @rowcount int
SELECT @rowcount = -1;
SELECT CASE @rowcount
          WHEN -1 THEN 'I WON THE RACE'
          WHEN 2 THEN 'I did not win'
       END AS columnName

The CASE goes inside of a SELECT statement. Then you just look at the value of your @rowCount, and take the appropriate action. You may want to read more about the CASE statement

The second form of a CASE statement looks like this:

SELECT CASE 
   WHEN @rowcount IS NULL THEN 'Null case here'
   WHEN @rowcount = -1 THEN 'I WON THE RACE'
   WHEN @rowcount = 2 THEN 'I did not win'
END AS columnName

If you want to use a CASE with an INSERT:

DECLARE @rowcount int
SELECT @rowcount = -1;

INSERT INTO myTable(column1, column2, column3)
SELECT CASE @rowcount
          WHEN -1 THEN 'I WON THE RACE'
          WHEN 2 THEN 'I did not win'
       END AS column1
   , t.column2
   , CASE
        WHEN t.someColumn < someValue THEN 'thirdColumnValue'
        ELSE 'someColumn is greater than specified value'
     END AS column3
FROM secondTable AS t

Upvotes: 8

Related Questions