TSQL Check if a row exists, otherwise insert

Question

Hi. I need to write a T-SQL stored procedure that updates a row in a table. If the row doesn't exist, insert it. All this steps wrapped by a transaction.

This is for a booking system, so it must be atomic and reliable. It must return true if the transaction was commited and the flight booked.

I'm new to T-SQL, and not sure on how to use @@rowcount. This is what I've written until now. Am I on the right road? I'm sure is an easy problem for you. Thanks

-- BEGIN TRANSACTION (HOW TO DO?)

UPDATE Bookings
 SET TicketsBooked = TicketsBooked + @TicketsToBook
 WHERE FlightId = @Id AND TicketsMax < (TicketsBooked + @TicketsToBook)

-- Here I need to insert only if the row doesn't exists.
-- If the row exists but the condition TicketsMax is violated, I must not insert the row and return FALSE

IF @@ROWCOUNT = 0 
BEGIN

 INSERT INTO Bookings ... (omitted)

END

-- END TRANSACTION (HOW TO DO?)

-- Return TRUE (How to do?)

Answer 1

I assume a single row for each flight? If so:

IF EXISTS (SELECT * FROM Bookings WHERE FLightID = @Id)
BEGIN
    --UPDATE HERE
END
ELSE
BEGIN
   -- INSERT HERE
END

I assume what I said, as your way of doing things can overbook a flight, as it will insert a new row when there are 10 tickets max and you are booking 20.

Answer 2

This is something I just recently had to do:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[cjso_UpdateCustomerLogin]
    (
      @CustomerID AS INT,
      @UserName AS VARCHAR(25),
      @Password AS BINARY(16)
    )
AS 
    BEGIN
     IF ISNULL((SELECT CustomerID FROM tblOnline_CustomerAccount WHERE CustomerID = @CustomerID), 0) = 0
     BEGIN
      INSERT INTO [tblOnline_CustomerAccount] (
       [CustomerID],
       [UserName],
       [Password],
       [LastLogin]
      ) VALUES ( 
       /* CustomerID - int */ @CustomerID,
       /* UserName - varchar(25) */ @UserName,
       /* Password - binary(16) */ @Password,
       /* LastLogin - datetime */ NULL ) 
     END
     ELSE
     BEGIN
      UPDATE  [tblOnline_CustomerAccount]
      SET     UserName = @UserName,
        Password = @Password
      WHERE   CustomerID = @CustomerID 
     END

    END

Answer 3

You could use the Merge Functionality to achieve. Otherwise you can do:

declare @rowCount int

select @rowCount=@@RowCount

if @rouCount=0
begin
--insert....

Answer 4

Take a look at MERGE command You can do update, insert & delete in one statement.

Here is a working implementation on using MERGE
- It checks whether flight is full before doing an update, else does an insert.

if exists(select 1 from INFORMATION_SCHEMA.TABLES T where T.TABLE_NAME = 'Bookings') begin
 drop table Bookings
end
GO

create table Bookings(
 FlightID  int identity(1, 1) primary key,
 TicketsMax  int not null,
 TicketsBooked int not null
)
GO

insert Bookings(TicketsMax, TicketsBooked) select 1, 0
insert Bookings(TicketsMax, TicketsBooked) select 2, 2
insert Bookings(TicketsMax, TicketsBooked) select 3, 1
GO

select * from Bookings

declare @FlightID int = 1
declare @TicketsToBook int = 2

--; This should add a new record
merge Bookings as T
using (select @FlightID as FlightID, @TicketsToBook as TicketsToBook) as S
  on T.FlightID = S.FlightID
   and T.TicketsMax > (T.TicketsBooked + S.TicketsToBook)
 when matched then
  update set T.TicketsBooked = T.TicketsBooked + S.TicketsToBook
 when not matched then
  insert (TicketsMax, TicketsBooked) 
  values(S.TicketsToBook, S.TicketsToBook);

select * from Bookings

Answer 5

Pass updlock, rowlock, holdlock hints when testing for existence of the row.

begin tran /* default read committed isolation level is fine */

if not exists (select * from Table with (updlock, rowlock, holdlock) where ...)
    /* insert */
else
    /* update */

commit /* locks are released here */

The updlock hint forces the query to take an update lock on the row if it already exists, preventing other transactions from modifying it until you commit or roll back.

The holdlock hint forces the query to take a range lock, preventing other transactions from adding a row matching your filter criteria until you commit or roll back.

The rowlock hint forces lock granularity to row level instead of the default page level, so your transaction won't block other transactions trying to update unrelated rows in the same page (but be aware of the trade-off between reduced contention and the increase in locking overhead - you should avoid taking large numbers of row-level locks in a single transaction).

See http://msdn.microsoft.com/en-us/library/ms187373.aspx for more information.

Note that locks are taken as the statements which take them are executed - invoking begin tran doesn't give you immunity against another transaction pinching locks on something before you get to it. You should try and factor your SQL to hold locks for the shortest possible time by committing the transaction as soon as possible (acquire late, release early).

Note that row-level locks may be less effective if your PK is a bigint, as the internal hashing on SQL Server is degenerate for 64-bit values (different key values may hash to the same lock id).