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 |
|
ronmc
Starting Member
12 Posts |
 Posted - 2006-03-09 : 07:34:22
|
| I have heard (and seen) a lot about set based sprocs vs traditional cursor based sprocs. Can anyone direct me to any resources on how to use set based stored procedures?Thanks |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-03-09 : 07:41:50
|
| Books Online is a good place to start.. |
 |
|
|
ronmc
Starting Member
12 Posts |
Posted - 2006-03-09 : 08:02:12
|
| Thanks Rick. I was looking for more of a web site or something more extensive then just the help files. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-03-09 : 08:07:05
|
| If you want to update a table by taking value from other table, you can use Cursor.The set based approach in this case isUpdate T1set col=T2.colfrom Table1 T1 inner join Table2 T2T1.id=T2.idMadhivananFailing to plan is Planning to fail |
|
|
|
ronmc
Starting Member
12 Posts |
Posted - 2006-03-09 : 08:42:35
|
| I must not be too clear on this topic. I thought that there is a way to write stored procedures without using cursors. It was my understanding that using cursors is the slow way of doing things, and that there is some other way of writing stored procedures. I could be wrong about that though. I am willing to learn. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-03-09 : 08:50:51
|
| You can do nearly every job in SQL without the need for cursors, in fact, the less cursors you use the better.. Books Online is very extensive with it's examples btw..The best way is to post your cursor code in a forum like this and ask people if they will help you convert it to a set based solution and learn from the changes.. |
|
|
|
ronmc
Starting Member
12 Posts |
Posted - 2006-03-09 : 09:07:09
|
| Thanks Rick, I appreciate the response. We have written about 300 or so stored procedures, and some of them are cursor based, and some are set based. The set based procedures run much faster on Sql 2000. With Sql 2005, the cursor based procedures seem to work about the same as the set based procedures.After talking to a co-worker here, he said that was probably due to 2005 being more of an "optimized" solution. I'll take a look at some of the stored procedures he has written, and see if I can pick up on the "set based" solution. |
|
|
|
|
|
|
Resources on set based processing