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.
| Author |
Topic |
|
anujrathi
Starting Member
9 Posts |
 Posted - 2006-06-20 : 19:14:13
|
| Hi friends,I m writing some stored procedures.Pls tell me, how can we use IF..ELSE, nested IF..ELSE in stored procedure. means i want to pass a int parameter to stored procedure & according to this value, we will execute my queries.eg if i=1 then insert, if i=2 then update or if i=3 then update. Also i can choose different SELECT statement according to i.Now how can i use the SELECT....CASE in stored procedure. means same above thing i want to do with SELECT....CASE. For example, for case 1, i want to write insert statement, for case 2 update statement & for case 3 delete statement.pls give details |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-06-20 : 19:52:43
|
| DECLARE @i intSET @i = SomeValueIF @i = 1 INSERT...IF @i = 2 UPDATE...You can't use CASE in the way that you want to. Please look it up in SQL Server Books Online for details.Tara Kizeraka tduggan |
 |
|
|
cmdr_skywalker
Posting Yak Master
159 Posts |
Posted - 2006-06-20 : 22:41:42
|
quote:
Pls tell me, how can we use IF..ELSE, nested IF..ELSE in stored procedure. means i want to pass a int parameter to stored procedure & according to this value, we will execute my queries.eg if i=1 then insert, if i=2 then update or if i=3 then update. Also i can choose different SELECT statement according to i.Now how can i use the SELECT....CASE in stored procedure. means same above thing i want to do with SELECT....CASE. For example, for case 1, i want to write insert statement, for case 2 update statement & for case 3 delete statement.
You can probably use the dynamic sql to formulate the statement based on the parameter value and execute it. See the http://www.sommarskog.se/dynamic_sql.html for more info.May the Almighty God bless us all! |
|
|
|
anujrathi
Starting Member
9 Posts |
Posted - 2006-06-21 : 09:48:15
|
| Hi friends,Thank you for ur replies. According to Tara Kizer, I can not use the CASE in my problem. But inplace of "IF", if there is any way to use CASE in this situation, then we can improve the performance of the stored procedure & also reduce execution time bcoz in CASE, there is no need to check every condition. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-06-21 : 10:05:12
|
| In your case you need to use IF. You cant apply CASEMadhivananFailing to plan is Planning to fail |
|
|
|
cmdr_skywalker
Posting Yak Master
159 Posts |
Posted - 2006-06-21 : 18:51:07
|
quote:
then we can improve the performance of the stored procedure & also reduce execution time bcoz in CASE, there is no need to check every condition.
write this in a if statement:Declare@i intset @i = 2if @i=1 print '1'else if @i=2 print '2'else if @i=3 print '3' The SQL if-else does not recheck all the IF conditions when one is already found. It only branches to the very first. Consider the following example:Declare@i intset @i = 2if @i=1 print '1'else if @i=2 print '2'else if @i=2 print '3' --same with first, but will not be reached Same is true with CASE statement.May the Almighty God bless us all! |
|
|
|
|
|
|
|
|
SELECT...CASE in stored procedures