Using IF statement in SQL Server stored procedure

Question

I'm using the IF statement like this:

ALTER proc spGetUserLevelCode 
( 
    @strLoginID VARCHAR(20) = '', @outdata int  output
) AS

if(select level_code 
    from org_person with(nolock) 
    where person_code = @strLoginID) = 'CA40'

as you can see, I extend CA50, CA60 like this adding behind 'CA40' or 'CA50' or 'CA60' but there is an error

How can I use additional condition in IF condition?

Look into this: msdn.microsoft.com/en-us/library/ms182587.aspx But in the meantime, perhaps your SELECT returns more than one row? What error did you get? Kindly post it.. — Nonym
– Nonym, Commented Dec 8, 2011 at 0:17

Martin Smith · Accepted Answer · 2011-12-08 00:15:33Z

4

Use

IN ('CA40','CA50','CA60' )

Or

 LIKE 'CA[4-6]0'

Not = 'CA40' or 'CA50' or 'CA60'

answered Dec 8, 2011 at 0:15

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dash Over a year ago

+1 for the SQL LIKE pattern - IN is more readable, but the LIKE demonstrates a neat additional way of looking at the problem, plus a useful demo of a feature of SQL Server's string matching :-)

user1086748 Over a year ago

thanks for your reply.even though It's beginners' question :) Finally I made it

UnhandledExcepSean · Accepted Answer · 2011-12-08 00:30:50Z

1

I think I'd prefer to use an exists in this case

ALTER proc spGetUserLevelCode 
( 
    @strLoginID VARCHAR(20) = '', @outdata int  output
) AS

if exists(select *
    from org_person with(nolock) 
    where person_code = @strLoginID
    AND level_code IN ('CA40','CA50','CA60') )
BEGIN
    --DO SOMETHING
END

answered Dec 8, 2011 at 0:30

UnhandledExcepSean

12.9k4 gold badges38 silver badges54 bronze badges

Comments

Elias Hossain · Accepted Answer · 2011-12-08 02:04:00Z

0

You can use either way as below:

IF EXISTS(SELECT 1
   FROM org_person WITH(NOLOCK) 
   WHERE person_code = @strLoginID
        AND level_code IN ('CA40','CA50','CA60') )
BEGIN
  -- Other statement goes here
END

OR

IF EXISTS(SELECT 1
   FROM org_person WITH(NOLOCK) 
   WHERE person_code = @strLoginID
        AND level_code LIKE 'CA[4-6]0' )
BEGIN
  -- Other statement goes here
END

answered Dec 8, 2011 at 2:04

Elias Hossain

4,4991 gold badge23 silver badges33 bronze badges

Collectives™ on Stack Overflow

Using IF statement in SQL Server stored procedure

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related