SQL Server stored procedures for INSERT and UPDATE, is it better to split or condense?

Question

I am creating stored procedures to insert and update data in a SQL Server database. At first I created a separate procedure for adding / installing, but then I came across a request that allows me to condense them into one procedure. I wanted to check with the SO community for any possible future issues doing it this way.

Separate procedures

--INSERT Procedure CREATE PROCEDURE [dbo].[AddDataType] @TypeName [nvarchar](255), @TypeProperty [nvarchar](255) AS BEGIN SET NOCOUNT ON; INSERT INTO DataType(TypeName, TypeProperty) VALUES(@TypeName, @TypeProperty) SELECT SCOPE_IDENTITY() END --UPDATE Procedure CREATE PROCEDURE [dbo].[SetDataType] @ID [int], @TypeName [nvarchar](255), @TypeProperty [nvarchar](255) AS BEGIN SET NOCOUNT ON; UPDATE DataType SET TypeName = @TypeName, TypeProperty = @TypeProperty WHERE ID = @ID END EXEC AddDataType @TypeName = 'Test Name', @TypeProperty = 'Test Property' --INSERT EXEC SetDataType @ID = 42, @TypeName = 'Test Name', @TestProperty = 'Test Property' --UPDATE 

Combined

 CREATE PROCEDURE [dbo].[SetDataType] @ID [int] = NULL, @TypeName [nvarchar](255), @TypeProperty [nvarchar](255) AS BEGIN SET NOCOUNT ON; UPDATE DataType SET TypeName = @TypeName, TypeProperty = @TypeProperty WHERE ID = @ID IF @@ROWCOUNT = 0 INSERT INTO DataType(TypeName, TypeProperty) VALUES(@TypeName, @TypeProperty) IF @ID IS NULL SELECT SCOPE_IDENTITY() END EXEC SetDataType @TypeName = 'New Type Name', @TypeProperty = 'New Type Property' --INSERT EXEC SetDataType @ID = 42, @TypeName = 'Updated Type Name', @TypeProperty = 'Updated Type Property' --UPDATE 

So far I have 15 type tables that I create for procedures and try to reduce the number of created procedures, however I do not want to sacrifice performance. I know that the second method is more processing, but will it be significant enough to cause problems? I do not see type tables containing massive amounts of data, not more than 100 records with an average value of about 10-20.

Any thoughts or suggestions are welcome.

Answer 1

What version of SQL Server? This information is always useful, so please read the topic of the question with a specific version.

If sql-server-2008 or more, you can consider MERGE instead of separate INSERT / UPDATE operations, although after writing this answer I definitely changed my setting and preferred the UPDATE / IF @@ROWCOUNT = 0 / INSERT methodology you proposed. For more information see this article I wrote:

• Use caution with a SQL Server MERGE statement

Here is an example of MERGE (run it in tempdb), but again recommend against it at all.

 CREATE TABLE dbo.DataType ( ID int IDENTITY(1,1), TypeName nvarchar(255), [TypeProperty] nvarchar(255), CONSTRAINT PK_DataType PRIMARY KEY (ID) ); INSERT dbo.DataType(TypeName, [TypeProperty]) VALUES (N'name 1', N'property 1'); GO 

Then follow the procedure:

 CREATE PROCEDURE dbo.MergeDataType @ID int = NULL, @TypeName nvarchar(255), @TypeProperty nvarchar(255) AS BEGIN SET NOCOUNT ON; WITH [source](ID, TypeName, [TypeProperty]) AS ( SELECT @ID, @TypeName, @TypeProperty ) MERGE dbo.DataType WITH (HOLDLOCK) AS [target] USING [source] ON [target].ID = [source].ID WHEN MATCHED THEN UPDATE SET [target].TypeName = @TypeName, [target].[TypeProperty] = @TypeProperty WHEN NOT MATCHED THEN INSERT (TypeName, [TypeProperty]) VALUES (@TypeName, @TypeProperty); END GO 

Now run it and check the results:

 EXEC dbo.MergeDataType @TypeName = N'foo', @TypeProperty = N'bar'; EXEC dbo.MergeDataType @ID = 1, @TypeName = N'name 1', @TypeProperty = N'new property'; GO SELECT ID, TypeName, [TypeProperty] FROM dbo.DataType; GO 

Cleaning:

 DROP TABLE dbo.DataType; DROP PROCEDURE dbo.MergeDataType; 

Answer 2

 USE [//Your Database Name] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[InsertIcone] -- Add the parameters for the stored procedure here ( @amount decimal(18,0), @payer nvarchar(50), @paymentMode nvarchar(50), @date date, @description nvarchar(Max), @operatorId int ) AS begin if exists (select * from wallet2 as t where payer=@payer ) begin update wallet2 set amount=@amount , payer=@payer , paymentMode=@paymentMode , [date] =@date ,[description] =@description where id=(select id from wallet2 as t where payer=@payer ) and operatorId=@operatorId end else begin insert into dbo.Wallet2 values(@amount,@payer,@paymentMode,@date,@description,@operatorId); end END