CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user1086748
user1086748

Reputation: 21

Using IF statement in SQL Server stored procedure

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?

Upvotes: 2

Views: 3147

Answers (3)

Elias Hossain
Elias Hossain

Reputation: 4469

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

Upvotes: 0

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

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

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453910

Use

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

Or

 LIKE 'CA[4-6]0'

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

Upvotes: 4

Related Questions