if then in stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

if then in stored procedure

Author

Topic

CrazyT
Yak Posting Veteran

73 Posts

Posted - 2010-06-28 : 12:37:31

I have been reading in some blogs that an If/then condition in a stored procedure will cause the sp to recompile everytime which takes the advantage away of having a stored procedure. is that true?

if true -- what is the best way to handle the below --

if condition 1

begin

select * from table1

end

else

begin

select * from table 2

end

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-28 : 12:48:26

FASLE

IF @PARAM = 'X' BEGIN
...code
END

IF @PARAM <> 'X' BEGIN
...Code
END

Never been a big fan of ELSE

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

CrazyT
Yak Posting Veteran

73 Posts

Posted - 2010-06-28 : 14:24:32

basically the difference I am having to if/then around is how to add another condition statement in the where clause

where condtion1 = @conditon1

if condtion2 is not null then " and condition2 = @condtion2"

how would you handle this?

Sachin.Nand

2937 Posts

Posted - 2010-06-28 : 14:27:35

What should be not null?

@condtion2 or condtion2

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-28 : 14:57:44

Is it a Predicate in a SQL Statement, or an Logical Condition in a T-SQL Statementy you are after

Do you actually have any code written that you've tried?

You need to do some of the work here, and then show us

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

CrazyT
Yak Posting Veteran

73 Posts

Posted - 2010-06-28 : 15:20:03

right now in my stored procedure I have it as

IF (@Password is null)

begin

select statement

end

ELSE

Begin

Same select statement Plus "and password = @password" added into the where clause

END

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-28 : 15:26:34

OK,

What don't you understand about this post

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=146560

SELECT * FROM table
WHERE PASSWORD = COALESCE(@PASSWORD,PASSWORD)

You don't need a condition...if you don't understand just let us know

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Sachin.Nand

2937 Posts

Posted - 2010-06-28 : 15:31:49

COALESCE will do the trick

select * from yourtable where Password=COALESCE(@password,Password)

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-28 : 15:48:38

Brilliant!!!!

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Sachin.Nand

2937 Posts

Posted - 2010-06-28 : 15:59:35

quote:
Originally posted by X002548

Brilliant!!!!

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Oops don't know how I dint see your reply.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

CrazyT
Yak Posting Veteran

73 Posts

Posted - 2010-06-28 : 16:59:20

didnt see your reply on the older thread -- thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-28 : 17:07:45

quote:
Originally posted by CrazyT

I have been reading in some blogs that an If/then condition in a stored procedure will cause the sp to recompile everytime which takes the advantage away of having a stored procedure. is that true?

False. http://scarydba.wordpress.com/2010/05/11/recompiles-and-constant-learning/

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-28 : 17:11:14

quote:
Originally posted by X002548

SELECT * FROM table
WHERE PASSWORD = COALESCE(@PASSWORD,PASSWORD)

Just bear in mind that the above query form plays hell with index usage. It'll likely table scan. If the table's small, not a concern.

--
Gail Shaw
SQL Server MVP

Sachin.Nand

2937 Posts

Posted - 2010-06-29 : 02:52:33

quote:
Originally posted by GilaMonster

quote:
Originally posted by X002548

SELECT * FROM table
WHERE PASSWORD = COALESCE(@PASSWORD,PASSWORD)

Just bear in mind that the above query form plays hell with index usage. It'll likely table scan. If the table's small, not a concern.

--
Gail Shaw
SQL Server MVP

That's interesting.If you dont mind can please elaborate it a little.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-06-29 : 05:18:49

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Also read the comments.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources