Take the 2-minute tour ×

I have select, insert, update and delete query.

If I have to write all queries in the same stored procedure that is good for performance or should I write all queries in separate stored procedures?

share|improve this question
If all the operations are related, it's better to keep them within the same transaction. But there's no performance benefit that I'm aware of. –  OMG Ponies Mar 19 '10 at 14:59
@OMG - fewer round trps to the database? Only thing I can think of. –  Justin Mar 19 '10 at 15:02
@Kragen: Good point - I was thinking query performance itself. That's what I get for commenting Before Coffee (tm) –  OMG Ponies Mar 19 '10 at 15:48
add comment

2 Answers

For ease of maintenance, I would go for separate procedures.

Speed is not going to be an issue if the same code is in one proc or multiple procs - as long as the code is the same, it will behave in the same way in one proc or many.

The best method to get good speed is to write an efficient query. Run it and review the execution plan; then tune the query where required.

You will find a lot of good information on query tuning and index tuning on this site (just search for it).

share|improve this answer
add comment

If it is something like, and all the parameters are manageable:


yes, all in one will eliminate the little overhead of multiple calls and keep the logic together as a unit.

however if it is:

@ParamType char(1) --given parameter "I"nsert, "U"pdate, "D"elete

IF @ParamType='I'
ELSE IF @ParamType='U'

split them up into separate procedures, they make no sense to be combined together.

share|improve this answer
@AjmeraInfo, my answer tries to address WHEN to combine them and when not to. Sometimes it is good to combine them, sometimes not. I say that if they are related to a transaction, combine them, if they are not related do not combine them. Your question doesn't give any hints as to if they are related together or not, and is very vague. If you edit your question and provide more details, Id be glad to revise my answer based on any new information you provide. –  KM. Mar 19 '10 at 15:12
add comment

Your Answer


