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 |
|
madridjohn
Starting Member
2 Posts |
 Posted - 2009-03-23 : 10:35:13
|
| Hi there,I am looking for a little bit of insight, and the recommended way of creating a stored procedure with regards to the following:I have a stored procedure that returns records but i can pass in a group so it only returns certain items... Basically i have 4 or 5 operations that i could easily do with just one stored procedure using IFs ... or provide 5 separate stored procedures that accept certain parameters and others do not.The way i see this is that 1 stored procs is great as everything is stored within it... but this stored procedure provides more than 1 function.... OOP (although not exactly the same :-) ) .. always says 1 object - 1 responsibility... Anyway advise if its better to provide 5 stored procedures that would return information but each accepting a different parameter for example or consolidating everything into 1I have up until to now always created a separate stored procedure, but my list is growing!!! :-) So i got to thinking maybe i should consolidate them - but then the 1 object - 1 responsibility keeps causing me problems :-) I would very much love to hear your ideas |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-03-23 : 11:45:37
|
| depends.. are all the 4-5 operations similar? as in querying same tables with different where clauses? What might end up happening is the optimizer will end up with one plan fits all scenario where 1-2 of those operations might be performing very well and the others might not because of a poor query plan.. I would recommend separate stored procedures..Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
madridjohn
Starting Member
2 Posts |
Posted - 2009-03-23 : 12:13:38
|
Yes exactly the situation, its 4 - 5 operations (in reality - WHERES) .. passing different parameters to the store procedure ..Thanks very much for your comments. My naming conventions as like so..TableNameTypeOfOpDescriptionso hence -- all 4 - 5 operations would be the same hence..VehicleSelectByIdVehicleSelectByColorVehicleSelectAvailableVehicleSelectWithAccidentAll come from the table Vehicle, all are SELECT ... and the rest explains what the stored procedure would do..Ideas on this? ... Good or bad situation?quote:
Originally posted by dinakar
depends.. are all the 4-5 operations similar? as in querying same tables with different where clauses? What might end up happening is the optimizer will end up with one plan fits all scenario where 1-2 of those operations might be performing very well and the others might not because of a poor query plan.. I would recommend separate stored procedures..Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/
|
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2009-03-23 : 12:16:57
|
| you could start with putting them all in same procedure.. as time goes on and if you see poor performance look for query plans.. if you see index scans where it should be doing an index seek you know its time to separate them.. and the application doesnt have to know this.. it will still call the main proc and get the results.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-23 : 12:18:12
|
| if its only WHERE conditions that vary, you could simply write boolean logic on where condition rather than having seperate IFs something likeWHERE (IdField=@ID OR @ID IS NULL)AND (Colorfield=@Color OR @Color IS NULL)... |
|
|
|
|
|
|
Individual SProcs or use 1 with IF?