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



Go to Top of Page

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?

Go to Top of Page

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
Go to Top of Page

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



Go to Top of Page

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



Go to Top of Page

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



Go to Top of Page

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
Go to Top of Page

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



Go to Top of Page

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
Go to Top of Page

CrazyT
Yak Posting Veteran

73 Posts

Posted - 2010-06-28 : 16:59:20
didnt see your reply on the older thread -- thanks
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -