CODEWITHSUNDEEP
sqlsql-serverif-statementcase
PYB
PYB

Reputation: 533

SQL Server - Update on IF condition

I can't believe I wasn't able to find the solution in other posts, but here goes..

I am trying to update different tables depending on a set variable, and I'd like the structure to be this way:

IF @etape = 1
(UPDATE table1 SET column1 = 1)
IF @etape = 2
(UPDATE table2 SET column1 = 1)
ELSE
(SELECT 'Wrong choice')

Which returns:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'update'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'.

I have tried using CASE, with the same results:

CASE WHEN @etape = 1 THEN 
(UPDATE table1 SET column1 = 1)
WHEN @etape = 2 THEN
(UPDATE table2 SET column1 = 1)
ELSE
(SELECT 'Wrong choice')
END

Although the documentation doesn't mention this, it seems like only SELECT statements are allowed after IF or CASE. Would appreciate some help here.

Upvotes: 0

Views: 130

Answers (3)

EdmCoff
EdmCoff

Reputation: 3576

Although I see another answer has fixed your if syntax, have you considered just using the where clause?

UPDATE table1 SET column1 = 1 WHERE @etape = 1;
UPDATE table2 SET column1 = 1 WHERE @etape = 2;
IF @etape <> 2
 SELECT 'Wrong choice'

Upvotes: 0

Jerome Curtis
Jerome Curtis

Reputation: 156

Just remove the parenthesis...

Declare @etape int = 1
IF @etape = 1 UPDATE table1 SET column1 = 1
IF @etape = 2 UPDATE table2 SET column1 = 1
ELSE SELECT 'Wrong choice'

, or wrap your updates in BEGIN...END blocks.

Upvotes: 1

Ghufran Ataie
Ghufran Ataie

Reputation: 190

In the case of updating the table based on parameters, you should use SQL stored procedure which allows you to pass the value as a parameter which is the following.

create procedure spUpdateTable
@etape int
as begin
if @etape = 1
    begin
        UPDATE table1 SET column1 = 1
    end
else if @etape = 2
    begin
        UPDATE table2 SET column1 = 1
    end
else
    begin
        print 'Wrong Choice'
    end
end

Now to run your procedure simply use do like following

spUpdateTable 1 or 2

Upvotes: 0

Related Questions