Hi, I am planing to use a sproc to insert records in the db. That said, the insertion process is to some extent dynamic. If the user has already subscribed to the enews on our site, and now is completing a full registration the insertion process will contain some updates. If this is the first time anything related to this user is being inserted, the full record is inserted. Also there is the dynamic situation of inserting the title, if the user did not provide a title, there is no insertion in the titles table etc. keep in mind that a Transaction must be used

What i need help on is which design is better to yield the highest performance
1- a sproc with if..else and the queries in between
2- scripting the different parts in separate queries and using if..else to excu them from the main sproc
3- scripting a number of sproc and using the server code (asp.net or whatever) to select the separate sprocs to use?

is it a good or bad thing to use Transaction in the sproc code itself?
thanks

From the maintainence standpoint, using single SProc is better than 3, however performance-wise all three are same...Keeping transaction in SProc is much, much better than at front-end (after all it's the thing for which we use RDBMS !!!)

Harsh Athalye
India
"Nothing is impossible"