Second-sealed-bid auction web app

Question

I am working on a second-sealed-bid auction web app. I have an Auction table to keep auction items on record, a Bid table to keep the bids, and a ProxyBid table to keep trace of the highest bids that users have ever placed.

Bid and ProxyBid are both associated with Auction. There could be multiple bid records for a given user id and auction id, whereas there can only be one record for a given user id and an auction id. The Auction table has a foreign key which refers to Bid table, so that I can keep track of the current bid of each auction item.

I have multiple users who may place a bid on the same auction item at the same time. Here is the stored procedure that places a bid:

ALTER PROCEDURE [dbo].[PlaceABid] 
    @UserId NVARCHAR(128), 
    @AuctionId INT,
    @BiddingPrice DECIMAL(16,2)
AS
BEGIN

    IF NOT EXISTS (SELECT TOP 1 Id FROM dbo.AspNetUsers WHERE Id = @UserId)
        THROW 50000, 'User could not be found.', 1; -- this can stop the execution, and level is 16

    IF NOT EXISTS (SELECT TOP 1 Id FROM dbo.Auction WHERE Id = @AuctionId AND Closed = 0 AND EndingDate < GETDATE())
        THROW 50000, 'Auction could not be found.', 1;  


    BEGIN TRY

        BEGIN TRANSACTION;  -- TODO: consider other islolation level 

        SET NOCOUNT ON;

        DECLARE @CurrentPrice DECIMAL(16,2),
                @HighestBiddingPrice DECIMAL(16,2),
                @HighestBidderId NVARCHAR(128),
                @StartingPrice DECIMAL(16,2);

        SELECT TOP 1 @CurrentPrice = BiddingPrice FROM dbo.Bid WHERE AuctionId = @AuctionId ORDER BY BiddingPrice DESC, Id DESC;
        SELECT TOP 1 @HighestBiddingPrice = BiddingPrice, @HighestBidderId = UserId FROM dbo.ProxyBid WHERE AuctionId = @AuctionId ORDER BY BiddingPrice DESC;
        SELECT TOP 1 @StartingPrice = StartingPrice FROM dbo.Auction WHERE Id = @AuctionId;

        IF @BiddingPrice <= @CurrentPrice
        BEGIN
            ROLLBACK TRANSACTION;
            THROW 50000, 'Bidding is lower than the current bid.', 1;   
        END

        IF @HighestBiddingPrice IS NULL -- nobody has placed any bids on the auction
        BEGIN

            INSERT INTO dbo.ProxyBid (AuctionId, UserId, BiddingPrice, CreatedTime, BidTime) VALUES (@AuctionId, @UserId, @BiddingPrice, GETDATE(), GETDATE());

            IF @@ROWCOUNT < 1   -- indicate that the insertion has failed
            BEGIN
                ROLLBACK TRANSACTION;
                THROW 50000, 'Failed in inserting bid record into ProxyBid table.', 2;
            END

            INSERT INTO dbo.Bid (AuctionId, BidderId, BiddingPrice, BiddingTime) VALUES (@AuctionId, @UserId, @StartingPrice, GETDATE());

            IF @@ROWCOUNT < 1   -- indicate that the insertion has failed
            BEGIN
                ROLLBACK TRANSACTION;
                THROW 50000, 'Failed in inserting bid record into Bid table.', 2;
            END

            UPDATE dbo.Auction SET CurrentBidId = SCOPE_IDENTITY(), CountBids = CountBids + 1 WHERE Id = @AuctionId;

            IF @@ROWCOUNT < 1   -- indicate that the update has failed, AuctionId is invalid
            BEGIN
                ROLLBACK TRANSACTION;
                THROW 50000, 'Failed in updating the auction record.', 2;
            END

        END
        ELSE
        BEGIN
            IF @BiddingPrice <= @HighestBiddingPrice    -- 
            BEGIN

                INSERT INTO dbo.Bid (AuctionId, BidderId, BiddingPrice, BiddingTime) 
                VALUES (@AuctionId, @UserId, @BiddingPrice, GETDATE())
                        ,(@AuctionId, @HighestBidderId, @BiddingPrice, GETDATE());

                IF @@ROWCOUNT < 2   -- indicate that the insertion has failed
                BEGIN
                    ROLLBACK TRANSACTION;
                    THROW 50000, 'Failed in inserting bid record into Bid table.', 3;
                END

                UPDATE dbo.Auction SET CurrentBidId = SCOPE_IDENTITY(), CountBids = CountBids + 2 WHERE Id = @AuctionId;

                IF @@ROWCOUNT < 1   -- indicate that the update has failed, AuctionId is invalid
                BEGIN
                    ROLLBACK TRANSACTION;
                    THROW 50000, 'Failed in updating the auction record.', 3;
                END

            END
            ELSE
            BEGIN

                IF EXISTS (SELECT TOP 1 CreatedTime FROM dbo.ProxyBid WHERE AuctionId = @AuctionId AND UserId = @UserId)
                BEGIN
                    UPDATE dbo.ProxyBid SET BiddingPrice = @BiddingPrice, BidTime = GETDATE() WHERE AuctionId = @AuctionId AND UserId = @UserId;
                END
                ELSE
                BEGIN
                    INSERT INTO dbo.ProxyBid (AuctionId, UserId, BiddingPrice, CreatedTime, BidTime) VALUES (@AuctionId, @UserId, @BiddingPrice, GETDATE(), GETDATE());
                END

                IF @@ROWCOUNT < 1   -- indicate that the update/insertion has failed
                BEGIN
                    ROLLBACK TRANSACTION;
                    THROW 50000, 'Failed in updating the bid or inserting new bid record into the ProxyBid table.', 4;
                END

                INSERT INTO dbo.Bid (AuctionId, BidderId, BiddingPrice, BiddingTime) 
                VALUES (@AuctionId, @HighestBidderId, @HighestBiddingPrice, GETDATE())
                        ,(@AuctionId, @UserId, @HighestBiddingPrice, GETDATE());

                IF @@ROWCOUNT < 2   -- indicate that the insertion has failed
                BEGIN
                    ROLLBACK TRANSACTION;
                    THROW 50000, 'Failed in inserting bid record into Bid table.', 4;
                END

                UPDATE dbo.Auction SET CurrentBidId = SCOPE_IDENTITY(), CountBids = CountBids + 2 WHERE Id = @AuctionId;

                IF @@ROWCOUNT < 1   -- indicate that the update has failed, AuctionId is invalid
                BEGIN
                    ROLLBACK TRANSACTION;
                    THROW 50000, 'Failed in updating the auction record.', 4;
                END

            END
        END

        COMMIT TRANSACTION;

        RETURN 0

    END TRY

    BEGIN CATCH -- if the execution reaches here, the return value is below 0
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

        DECLARE @ErrorNumber INT = ERROR_NUMBER();
        DECLARE @ErrorLine INT = ERROR_LINE();
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        DECLARE @ErrorState INT = ERROR_STATE();

        --PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
        --PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));

        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);

    END CATCH

END

Its logic can be simplified as:

if nobody has placed a bid on the auction item in question
{
  insert a record into ProxyBid
  insert a record into Bid
  update the auction item record, making it reference to the latest Bid record
}
else
{
  if the price that the user is bidding at is no higher than the current highest bidding price
  {
      insert two records into Bid (note: because it is second sealed bid auction)
      update the auction item record, making it reference to the latest Bid record
  }
  else
  {
      if the user already has a record in ProxyBid
      {
          insert a record into ProxyBid
      }
      else
      {
          update the existing record in ProxyBid
      }

      insert two records into Bid
      update the auction item record, making it reference to the latest Bid record
  }
}

1. I enclose if-else blocks inside try-catch block and transaction block. I am new to SQL, and I am always curious about whether it would be better to do it in the other way, which is to enclose one try-catch block and transaction block inside each if-else block.
2. The control flow depends on the highest bidding price at the execution time. So, if another user inserts another record with higher price in middle of the execution, the data will be inconsistent. Will the default isolation level of transaction prevent that from happening?
3. Is there a better way to handle error/exception, and pass them to client code such as C#?

Answer 1

You are going to think this is strange but I think this can be done a lot easier
With Views I think you can just get it done with one bid table

The merge statement is a combined update insert and does not require a transaction. A single statement is a transaction

  create view AuctionBid as 
  SELECT [Auction].[AuctionId]
        ,[UserId]
        ,[BiddingPrice]
        ,[DateTime]
        ,[Auction].[StartingPrice]
  FROM [Auction]
  LEFT JOIN [Bid] on [Auction].[AuctionID] = [Bid].[AuctionId]

  select * from AuctionBid 

  create view AuctionBidItemMax as 
  select [AuctionId]
        ,[UserId]
        ,[BiddingPrice]
        ,[DateTime]
        ,[StartingPrice]
  from 
  (
  SELECT [Auction].[AuctionId]
        ,[UserId]
        ,[BiddingPrice]
        ,[DateTime]
        ,[Auction].[StartingPrice]
        ,ROW_NUMBER() over (partition by [Auction].[AuctionID] order by [BiddingPrice] desc) as rn
  FROM [Auction]
  LEFT JOIN [Bid] on [Auction].[AuctionID] = [Bid].[AuctionId]
  ) tt 
  where tt.rn = 1

  DECLARE @BidPrice DECIMAL(16,2) = 15;
  DECLARE @UserId int = 2;
  DECLARE @AuctionId int = 5;

  MERGE Bid AS target  
    USING (SELECT @AuctionId, @UserId, @BidPrice) AS source ([AuctionId], [UserId], [BiddingPrice])  
    ON (target.[AuctionId] = source.[AuctionId] and target.[UserId] = source.[UserId])  
  WHEN MATCHED THEN   
        UPDATE SET [BiddingPrice] = source.[BiddingPrice], [DateTime] = getdate()  
  WHEN NOT MATCHED THEN  
    INSERT ([AuctionId], [UserId], [BiddingPrice], [DateTime])  
    VALUES (source.[AuctionId], source.[UserId], source.[BiddingPrice], getdate()); 

  select * from bid