SQL IF Statement

Question

Post reply

SQL IF Statement

sc-w

Hall of Fame

Points: 3664
More actions
September 30, 2008 at 6:07 am

#194266

Hi,
I want to use an if statement in my SQL stored procedure but what i have tried is not working and there are various examples on the net all showing different methods. I will explain it and maybe someone can give me an example that i can play with. The logic of what i want is very simple.
I have 1 argument being passed to the stored procedure called @APRIORITY this will be either a 1, 2 or 3 if that value is 1 i want it to set a new variable or the same one to a value of high, if it is 2, set it to medium and for 3 set it to low. I will then pass that into the insert query.
Here is an example of one of the things i tried. Any help / advice would be greatly appreciated.
set newvar varchar(8)
IF @APRIORITY = 1 THEN
newvar = 'high'
ELSEIF @APRIORITY = 2 THEN
newvar = 'medium'
ELSE
newvar = 'low'
END
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Vladan SSC-Insane Points: 22072 More actions · Answer 1

What you are trying is not proper SQL syntax - you have to use SET, and @ with the variables... like this:

DECLARE @newvar varchar(8)

IF @APRIORITY = 1 THEN

SET @newvar = 'high'

ELSE IF @APRIORITY = 2 THEN

SET @newvar = 'medium'

ELSE

SET @newvar = 'low'

... or better, using one CASE statement:

DECLARE @newvar varchar(8)

SET @newvar = CASE

WHEN @APRIORITY = 1 THEN 'high'

WHEN @APRIORITY = 2 THEN 'medium'

ELSE 'low' END

However, it is a question whether you really have to use IF (or CASE) in your procedure. There might be a better way to solve your problem... but we can't help you unless you tell us more.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

Take the THEN keyword out (as SQL does not use THEN in its if statements) and replace ELSEIF with ELSE IF and your query should work as you intend.

Also you have to put the keyword SET in front of the assignments

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Vladan (9/30/2008)
What you are trying is not proper SQL syntax - you have to use SET, and @ with the variables... like this:
DECLARE @newvar varchar(8)
IF @APRIORITY = 1 THEN
SET @newvar = 'high'
ELSEIF @APRIORITY = 2 THEN
SET @newvar = 'medium'
ELSE
SET @newvar = 'low'

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'THEN'.

Msg 102, Level 15, State 1, Line 5

Incorrect syntax near 'ELSEIF'.

Msg 156, Level 15, State 1, Line 7

Incorrect syntax near the keyword 'ELSE'.

Rather:

DECLARE @newvar varchar(8)

IF @APRIORITY = 1

SET @newvar = 'high'

ELSE

IF @APRIORITY = 2

SET @newvar = 'medium'

ELSE

SET @newvar = 'low'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Vladan SSC-Insane Points: 22072 More actions · Answer 4

Oh 🙁 I'm sorry. Thanks Gail for catching that, I found the ELSEIF already in your first post and edited my post, but forgot to delete THEN. Well, that happens when one copies some code. I wouldn't write it so, but I didn't notice the superfluous THEN.

sc-w Hall of Fame Points: 3664 More actions · Answer 5

Great, thank you both for that. I have it working with both the if statement and case, i wanted to try them both so i learn more. Great help thanks again.