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. 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 may place a bid on the same auction item at the same time, below 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
    }
}

I enclose if-else blocks inside try-catch block and transaction block. I am new to SQL, and I am always curious that, would it 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.

The other thing is, 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?

My 3rd question is, is there a better way to handle error/exception, and pass them to client code such as C#?

Thanks a lot.