CODEWITHSUNDEEP
sqlsql-server-2005stored-procedures
Dr. Rajesh Rolen
Dr. Rajesh Rolen

Reputation: 14285

better approach for stored procedure

i need a experts advice that i should create separate stored procedure for every operation or i should create a single stored procedure for all operation like below example:

create proc proc_name
@mode int =0,
@othervariables

as
begin
if @mode =1
begin
insert operation
end

else if @mode =2
begin
update operation
end

else if @mode =3
begin
delete operation
end

else if @mode =4
begin
select statement
end
---- other operations
end

Expert i really need your valuable advice. Speed will be main concern for my web site. their will be less insert/update operation and thousands of retrieval operation per minutes.

Thanks

Upvotes: 1

Views: 72

Answers (1)

Guffa
Guffa

Reputation: 700362

Generally you should create separate procedures.

Often you will use different sets of parameters depending on the operation, and it's easier to use and maintain procedures that have only the parameters that they actually use.

You should try to avoid magic numbers like the mode parameter in your example. It's much easier to see what's going on with descriptive procedure names than a number in a parameter.

There is no relevant performance gain that you can do by choosing one approach or the other.

Upvotes: 3

Related Questions