Multiple SPs versus SP with branches
-
Consider the case of a database table for which Select, Insert and Delete actions are required.
One way to implement this functionality is to create three stored procedures: Table_Select, Table_Insert, Table_Delete.
Another way to implement this is to create a single SP into which is passed an additonal parameter to specify the action to be carried out. This action can then be the target of an if statement within the SP.
Which of these two methods is "better"? Which is preferred?
Thanks!
-
I prefer to have the different functions in separate stored procedures so that it is immediately obvious to the programmer what the procedure is supposed to do.
usp_GetContacts = SELECT
usp_SetContacts = UPDATE
usp_DelContacts = DELETE
usp_AddContact = INSERT
I don't mind branches within the individual stored procedures. For example usp_GetContacts may have an optional @lContactId parameter. If this is set then the stored procedure will return one record, if it is NULL then the stored procedure branches to a SELECT that retrieves multiple records.
-
I'd follow the obvious to the programmer thought. I'd approach it a bit more dynamicly, though. If the sproc is performing business logic then I'd only branch to another sproc if the resulting options can't easily be laid out before the programmer.
Everett Wilson
ewilson10@yahoo.com -
I have done many ways. I personnaly use SP groups with business logic to decided the SP number in the group to run and of course comments to above each. It really comes done to personal choice and complexity of what you are doing.
Here is an example of a single SP group. However you could easily make an sp for each INSERT,UPDQATE, and DELETE with good names and have another SP do the same logic as ip_UpdateSupPer;1 and execute the related item, that way another developer can find each individually. This is usefull in ASP if you have a single page that can do all actions but the other method is better when you have multiple pages that do the individual actions. It is all a matter of choice. Just make sure you create some sort of documentation no matter what for anyone who has to follow you.
CREATE PROC ip_UpdateSupPer;1
@BDG varchar(10),
@PersonnelID int,
@Title int
AS
SET NOCOUNT ON
DECLARE @FC_ID int
SET @FC_ID = (SELECT [Index] FROM dbo.LocDetail WHERE [BDG#] = @BDG)
If @PersonnelID = 0
EXEC dbo.ip_UpdateSupPer;2 @FC_ID, @Title
ELSE
BEGIN
IF Exists(SELECT * FROM dbo.tbl_FC_Personnel WHERE FK_LocDetailID = @FC_ID AND FK_TitleID = @Title)
EXEC dbo.ip_UpdateSupPer;3 @FC_ID, @PersonnelID, @Title
ELSE
EXEC dbo.ip_UpdateSupPer;4 @FC_ID, @PersonnelID, @Title
END
GO
-- Delete support personnel
CREATE PROC ip_UpdateSupPer;2
@FC_ID int,
@Title int
AS
SET NOCOUNT ON
DELETE dbo.tbl_FC_Personnel WHERE FK_LocDetailID = @FC_ID AND FK_TitleID = @Title
GO
-- Update personnel item.
CREATE PROC ip_UpdateSupPer;3
@FC_ID int,
@PersonnelID int,
@Title int
AS
SET NOCOUNT ON
UPDATE
dbo.tbl_FC_Personnel
SET
FK_PersonnelID = @PersonnelID
WHERE
FK_LocDetailID = @FC_ID AND
FK_PersonnelID != @PersonnelID AND
FK_TitleID = @Title
GO
-- Insert new support personnel item.
CREATE PROC ip_UpdateSupPer;4
@FC_ID int,
@PersonnelID int,
@Title int
AS
SET NOCOUNT ON
INSERT dbo.tbl_FC_Personnel
(
FK_LocDetailID,
FK_PersonnelID,
FK_TitleID
 
VALUES
(
@FC_ID,
@PersonnelID,
@Title
 
GO
-
I got quite enthusiastic about the ability to append group procs together, until I realised you can't explicitly drop a particular proc in a group.
-
There is such a thing as over-normalisation. Use Separate SPROCS for each action, otherwise you won't be able to use sql security to enforce who can do what.
Eg. You may want sales reps to be able to create and update orders, but only finance people to delete them.
Using SQL Server and Windows Auth, you could simply define these permissions against your sprocs, but if the insert/update/delete sprocs had been rolled into one, you'd have to create your own security layer inside the sprocs to determine who can do what. Double the effort!
Julian Kuiters
juliankuiters.id.au
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply