SQL Server stored procedures for INSERT and UPDATE, better to separate or condense?

Question

I am creating stored procedures for inserting and updating data in my SQL Server database. At first I was creating a separate procedure for Add/Set but then I stumbled across a query that allows me to condense them into a single procedure. I wanted to check with the SO community on 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 creating procedures for and am trying to cut down on the number of procedures created, however I don't want to sacrifice performance. I know the second method is more processing, but would it be significant enough to cause issues? I don't see the type tables holding mass amounts of data, no more than 100 records with the average being around 10-20.

Any thoughts or suggestions are appreciated.

Answer 1

What version of SQL Server? This information is always useful so please get in the habit of tagging your question with the specific version.

If sql-server-2008 or greater, you might consider MERGE instead of separate INSERT/UPDATE operations, though since writing this answer I have definitely changed my tune and prefer the UPDATE / IF @@ROWCOUNT = 0 / INSERT methodology you proposed. For more info, see:

• Use Caution with SQL Server's MERGE Statement
• So, you want to use MERGE, eh?

Here is a MERGE sample (run it in tempdb), but again I recommend against it in general.

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 a 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 let's 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

Clean up:

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