Insert Update Stored Proc on SQL Server

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

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;

Insert Update stored proc on SQL Server

Your assumption is right, this is the optimal way to do it and it's called upsert/merge.

Importance of UPSERT - from sqlservercentral.com:

For every update in the case mentioned above we are removing one
additional read from the table if we
use the UPSERT instead of EXISTS.
Unfortunately for an Insert, both the
UPSERT and IF EXISTS methods use the
same number of reads on the table.
Therefore the check for existence
should only be done when there is a
very valid reason to justify the
additional I/O. The optimized way to
do things is to make sure that you
have little reads as possible on the
DB.

The best strategy is to attempt the
update. If no rows are affected by the
update then insert. In most
circumstances, the row will already
exist and only one I/O will be
required.

Edit:
Please check out this answer and the linked blog post to learn about the problems with this pattern and how to make it work safe.

Update followed by insert in a stored procedure

A simple change to your current code can give you what you're looking for.
Instead of messing around with @@Identity, which is almost never the right thing to do, you compute the hash of the @Id value once, store it in a local variable, and use it for both the insert statement and the where clause of the update statement - That is, assuming the HashId column is unique.

That being said, I'm not sure why you need the rowId column as well as the incId column - unless one of them is designed to change it's value through an update statement in the lifetime of the row - you are simply keeping redundant data.

Here's an improved version of your stored procedure:

CRETAE PROCEDURE [dbo].[io_sp_admin_add_emp]
    @id BIGINT,
    @lastName varchar(20), 
    @firstName varchar(20)      
AS

BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION [TranAddEmp]

        -- Compute the hash once, store in a local variable
        DECLARE @HashId varbinary(8000) = HashBytes('SHA2_256', cast(@id as varbinary(50)))

        INSERT INTO empTable(
            LastName,
            FirstName,
            hash_id
        )
        VALUES(
            @lastName,
            @firstName,
            @HashId
        )

         UPDATE empTable
         SET rowId = incId  
         WHERE hash_id = @HashId

        COMMIT TRANSACTION [TranAddEmp]   
    END TRY

    BEGIN CATCH
        -- make sure transaction has started and is not commited
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION [TranAddEmp]
    END CATCH
END

SQL Server Stored Procedure IF Exist Update Else Insert

You have to wrap your SP into BEGIN & END, you are missing that. And also use 1 when check IF Exist in Select Statement.And also check that, Some transaction is open but not commit. Instead of Doing this also, You can use SQL MERGE to Update and Insert Statement.

CREATE PROCEDURE Insert_Inventory
(
    @Brand Varchar(50),
    @Series Varchar(50),
    @Model Varchar(50),
    @Gener int,
    @Ram int,
    @HDD int,
    @Processor Varchar(50),
    @Clock float,
    @Graphic_Chip Varchar(50),
    @Graphic_Memory int,
    @Qty int,
    @Price int
)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM Laptops
        WHERE Brand=@Brand
            and Series=@Series
            and Model=@Model
            and Gener=@Gener 
            and ram=@Ram 
            and hdd=@HDD 
            and Processor=@Processor 
            and Clock=@Clock 
            and Graphic_Chip=@Graphic_Chip 
            and Graphic_Memory=@Graphic_Memory
    )
    BEGIN
        UPDATE Laptops set qty=qty+@Qty, Price=@Price
        WHERE Brand=@Brand 
            and Series=@Series
            and Model=@Model 
            and Gener=@Gener 
            and ram=@Ram 
            and hdd=@HDD 
            and Processor=@Processor 
            and Clock=@Clock 
            and Graphic_Chip=@Graphic_Chip 
            and Graphic_Memory=@Graphic_Memory
    END
ELSE
    BEGIN
        INSERT into Laptops Values (@Brand,@Series,@Model,@Gener,@Ram,@HDD,@Processor,@Clock,@Graphic_Chip,@Graphic_Memory,@Qty,@Price)
    END

END

Stored procedures to insert and then update the table based on identity column

First, absolutely yes, scope_identity is the right way to get the Id value.

You say you are storing the returned Id value from your first procedure call (I presume the column is called Id you don't provide the table schema), so you can include a parameter on your second procedure for it (@Id int) and just pass it along with the other values you are already supplying - your comment suggests you are going to do that?

Then in your second procedure you simply

update gds.TBL_SF_INTEGRATION_AUDIT set
<columns=@values>
where id=@id

Another way of capturing the Identity is to use the output clause to capture the identity value along with other correlating values into a separate table.

You can do the following

insert into gds.TBL_SF_INTEGRATION_AUDIT (<columns>) 
output inserted.Id, inserted.Integration_Batch_Id into <another table> -- or any other columns needed
values (<values>)

You can then join to this using the known values to find the Id that was generated.

Sql Stored Procedure to insert and update

Most likely you're looking for something like this

CREATE PROCEDURE  [dbo].[stprOrder]

 @OrderDate date,
 @OrderID nchar(50),
 @ShipToID nchar(50),
 @TotalAmt  decimal(18,2),
AS
BEGIN
  SET NOCOUNT ON;

  IF (SELECT TOP (1) 1 FROM ORDER WHERE OrderID = @OrderID) IS NULL
     INSERT INTO ORDER(OrderDate,OrderID,ShipToID,TotalAmt)
     Values(@OrderDate,@OrderID,@ShipToID,@TotalAmt)
  ELSE
    UPDATE ORDER SET OrderDate = @OrderDate, ShipToID = @ShipToID, TotalAmt = @TotalAmt
    WHERE OrderID = @OrderID
END

First it checks if order with given ID already exists - if it doesn't - a new entry is created, otherwise existing record is updated

SQL Server stored procedure - update one table and insert into another table

The answer was provided by bbaird in the comments. Removing the BEGIN/END keywords fixed the problem. Thank you!

bbaird's full comment below:

1. If the procedure isn't created yet, you will need to do CREATE PROCEDURE. 2. The update and insert statements are independent, no need to put them in their own BEGIN...END block unless there is a conditional. 3. COLUMNS in the insert statement might also be throwing things off - it is not necessary so remove it.

---

Related Topics