SQL Server Case Statement

Question

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

Answer 1

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

Answer 2

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.