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 1beginselect * from table1end elsebeginselect * from table 2end |
|
X002548
Not Just a Number
15586 Posts |
|
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 clausewhere condtion1 = @conditon1if 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 condtion2Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
 |
|
X002548
Not Just a Number
15586 Posts |
|
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)beginselect statement endELSEBegin Same select statement Plus "and password = @password" added into the where clauseEND |
 |
|
X002548
Not Just a Number
15586 Posts |
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-28 : 15:31:49
|
COALESCE will do the trickselect * 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 |
|
Sachin.Nand
2937 Posts |
|
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 |
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-06-28 : 17:11:14
|
quote: Originally posted by X002548 SELECT * FROM tableWHERE 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 ShawSQL 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 tableWHERE 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 ShawSQL 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 ShawSQL Server MVP |
 |
|
|