May 31, 2012 at 10:02 am
Just wondering peoples opinion on this, and your take on the pros vs cons of each scenario.
I was recently working on a project and decided to do it different to the norm.
Instead of have maybe 5 different SProcs that all relate to the same schema and work around the same project (e.g. a SProc to insert data, one to update data, one to return data, one to delete data, etc.), I decided to slam them all into 1 SProc and parametrise each function.
So for example:
CREATE PROCEDURE testSproc
(
@p_functionVARCHAR(25),
@p_idINT,
@p_nameVARCHAR(50),
@p_ageINT
)
AS
BEGIN
IF @p_function = 'insert'
BEGIN
INSERT INTO testTable (name, age) VALUES (@p_name, @p_age)
END
IF @p_function = 'get'
BEGIN
SELECTt.id,
t.name,
t.age
FROMtestTable t
WHEREt.id = @p_id
END
END
My thinking was it would obviously reduce the number of SProcs and encapsulate all the required functions into one, but I'd like to hear other peoples opinions on this, and if anyone has insights into performance related stats on this type of structure, as opposed to separating each function into its own SProc.
May 31, 2012 at 10:22 am
I would go with separate stored procedures. Each one should do a single function, get or insert.
May 31, 2012 at 10:25 am
Lynn Pettis (5/31/2012)
I would go with separate stored procedures. Each one should do a single function, get or insert.
What's your reasoning behind this though? Don't get me wrong, I've always done separate stored procedures myself, but is there any particular reason you would stick to this method of separating them, other than its the done thing?
May 31, 2012 at 10:44 am
rossss (5/31/2012)
Lynn Pettis (5/31/2012)
I would go with separate stored procedures. Each one should do a single function, get or insert.What's your reasoning behind this though? Don't get me wrong, I've always done separate stored procedures myself, but is there any particular reason you would stick to this method of separating them, other than its the done thing?
It comes down to design and maintenenace. A stored procedure (a function, if you will) when called should do one thing. It is the way I was taught to develop programs and it works well in the database world as well.
May 31, 2012 at 10:51 am
I'd also do one per function. The only case might be an Upsert/Merge proc that does insert or update. Again, it is about modularity and maintenance.
A wide table would certainly have an awful lot of optional parameters.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 1, 2012 at 1:11 pm
This article explains very clearly why to avoid the pattern:
Multiple Execution Paths[/url]
My opinion: split to separate, more granular procs targeted to one specific purpose each. If the article is too detailed or you have additional questions please post again.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply