asked on

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

ASKER CERTIFIED SOLUTION