CODEWITHSUNDEEP
sqlsql-servercaseswitch-statement
Moe9977
Moe9977

Reputation: 259

SQL Server Case Statement

I am trying to run this sql statement, but it generate error "Incorrect syntax near update"

Select Case @location 

        When 'MediaFiles' Then update tblMediaFiles set mdActive=1
        When 'MediaFiles1' Then update tblMediaFiles1 set mdActive=1
        When 'MediaFiles2' Then update tblMediaFiles2 set mdActive=2
        Else update tblMediaFiles4 set mdActive=1

    End

Upvotes: 5

Views: 3579

Answers (2)

Rondel
Rondel

Reputation: 4951

From MSDN:

The CASE expression cannot be used to control the flow of execution of Transact-SQL statements, statement blocks, user-defined functions, and stored procedures. For a list of control-of-flow methods, see Control-of-Flow Language (Transact-SQL).

You really want to use an IF-ELSE structure for this kind of logic.

Upvotes: 6

gbn
gbn

Reputation: 432521

SQL has a CASE expression that goes where, er, expressions would go

Use the IF statement instead

IF @location = 'MediaFiles' 
    update tblMediaFiles set mdActive=1
ELSE IF @location = 'MediaFiles1' 
    update tblMediaFiles1 set mdActive=1
ELSE IF @location = 'MediaFiles2' 
    update tblMediaFiles2 set mdActive=2
ELSE
    update tblMediaFiles4 set mdActive=1

Upvotes: 8

Related Questions