Wrox Programmer Forums
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 29th, 2008, 03:09 AM
Friend of Wrox
Join Date: Aug 2006
Posts: 231
Thanks: 0
Thanked 1 Time in 1 Post
Default stored procedures

i have two examples of stored procedures

1) is seperate stored procedures for select and seperate for delete and seperate for insert and update operation.

2) is create a one stored procedure and based on operation either insert or delete or update corresponding case should run.

Please guide.

Old March 29th, 2008, 12:19 PM
Friend of Wrox
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts

Not sure what guidance you seek, but both methods can be very effective depending on how they are written. Both methods can also be terrible, again, depending on how they are written.

--Jeff Moden
Old March 30th, 2008, 12:01 PM
planoie's Avatar
Friend of Wrox
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts

My recommendation would be to stick with small, separated operations. For me it makes no logical sense to try to group together add/modify operations with retrieval operations. The former is likely to return very little data while the latter is designed with that intent. Likewise, the former will likely accept many bits of data in order to save it, while the latter take in none to only a little for filtering purposes. Given the significant behavior and the difficulty in debugging the bridge between program code and database code I could stick with keeping the units of processing small.

Old April 11th, 2008, 04:00 AM
Authorized User
Join Date: Dec 2004
Posts: 69
Thanks: 0
Thanked 5 Times in 5 Posts
Send a message via Yahoo to whiterainbow

you can integrate the insert and update into same stored procedure but you need to be careful of sending and receiving the parameters.

It is not advisible to integrate the delete or select in the same stored procedure...

Thanks and Regards,

Senthil Kumar M.
Old April 12th, 2008, 01:39 AM
Friend of Wrox
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts

My two cents if it's worth that.

I largely agree with Planoie. With one addition. If you have repeating logic in your stored proc for the add/modify it may be a good idea to put them both in the same stored proc. Thus if the logic changes you change it in one place not two. But this is for a type of database that is high complexity low volume. If your high volume, split them up into two if less logic and be run through to speed up the proc.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Stored Procedures KeviJay VB Databases Basics 1 June 5th, 2008 07:17 AM
STORED PROCEDURES shazia1 SQL Server ASP 7 September 26th, 2007 06:11 AM
Stored Procedures itHighway SQL Server 2000 3 November 23rd, 2005 10:08 AM
Stored Procedures jazzcatone Classic ASP Databases 0 August 28th, 2005 02:57 PM
Stored Procedures stu9820 Access 3 February 8th, 2004 01:13 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.