SQL SP Insert Update Combo?

Q. Is the a good reason to NOT use the same SP for both Inserts or Updates?

The example below uses a 'Command' param called by a ASP.NET app.

I often work on databases where there is a separate sp for Select, Insert, Update, Delete.

[Example]
ALTER PROCEDURE [dbo].[sp_InsertUpdateUsers]
(		
	@Firstname		nvarchar(150), 
	@Lastname		nvarchar(150)
        @Command		varchar(10),
	@ID	                        int OUTPUT
)
AS
BEGIN	
     BEGIN TRY	
        BEGIN TRAN;
            IF(@Command = 'INSERT')
	        BEGIN			
	             Insert Into Users (Firstname, Lastname) Values(@Firstname, @Lastname);
                END
            IF(@Command = 'UPDATE')
	       BEGIN
	             Update Users Set FirstName = @FirstName, Lastname= @Lastname;
               END
         COMMIT TRAN;
    END TRY
    BEGIN CATCH
	       ROLLBACK TRAN;
    END CATCH	
	
    SET @ID = Scope_Identity()
END

Open in new window

WorknHardrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
When they are separate, a screwup breaks only one function and is easily determinable.

But changes to the database schema, require updates to all four which experience suggest something/someone might overlook forget.

A versioning of changes is a good way to manage when available.

It all depends on the complexity as well.  I.e. An insert that might require a transaction that creates entries in multiple tables.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WorknHardrAuthor Commented:
Looking a my example posted above, is there anything wrong placing a simple insert and update in the same sp?
0
Scott PletcherSenior DBACommented:
>> Q. Is the a good reason to NOT use the same SP for both Inserts or Updates? <<

There are a few.

1) Less complex code if separate = fewer errors/bugs in code.

2) Less overhead when calling a single proc, because the code is shorter.

3) Other actions if needed, such as "ChangeStatus", are easier, because they automatically go into a separate proc, rather than possibly being added to an existing proc.
0
WorknHardrAuthor Commented:
I agree, thx...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.