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.


thanks......

__________________
thanks......

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

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.

-Peter
peterlanoie.blog

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.

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.