Can Stored Procedures in SQL Server have optional parameters.
-
Can Stored Procedures in SQL Server have optional parameters.
I want to uer the same stored proc for insert , update , delete. Which is the best Method
Sanjay Masawan
-
SP's can have default values for parameters. They can't have optional parameters. If I were you, I would rather do some more typing and have a single dedicated proc for each INSERT, DELETE or UPDATE.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
If u have a procedure with common insert, update and delete functionality please pass on to me on sanjaymasawan@bsil.com
-
No, I don't have such a procedure. I meant a single proc for each operation for *each* table. Otherwise you're into dynamic sql as you can read here: http://www.sommarskog.se/dynamic_sql.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I agree you should seperate them or at least use procedure groups. If you have all encapsulated in a single sp you will take a big performance hit each time it changes process as you will always have a cache miss and a recompile of the execution plan or a poor plan will cause a big degrade. Considering it is just as easy to write a new proc and decide in the app which to hit as it is to flag it I would go that way.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply