The Delete code doesnt work after it updates a column

When I send data to the procedure which contains the ResStatus as C it updates the ResMaster’s ResStatus as X After it updates the column, I want to delete a record from 2 tables they are- Folio,ResRoomAllocation.

I have done the coding but it doesn’t work it only updates the ResStatus as X

What’s wrong with my code?I’m new to MSSQL

IF EXISTS(Select TOP (1) * From ResMaster Where VoucherNo = @BookingId AND ResStatus ='C')
    BEGIN
        Update ResMaster
        Set ResStatus = 'X'
        Where VoucherNo = @BookingId

        DECLARE @RemoveBookingId NVARCHAR = (Select ResNo from ResMaster where VoucherNo = @BookingId)

        DELETE FROM Folio WHERE ResNo = @RemoveBookingId
        DELETE FROM ResRoomAllocation  WHERE ResNo = @RemoveBookingId

Answer

I have no idea what other problems you have in the code, but this is simply wrong:

  DECLARE @RemoveBookingId NVARCHAR = (Select ResNo from ResMaster where VoucherNo = @BookingId);

First, “id”s are often stored as numbers, especially when associated with something with a name like “ResNo” (presumably “no” implies number).

But let me assume that ResNo is a string. Your declaration:

DECLARE @RemoveBookingId NVARCHAR;

declares a string with ONE character. That usually breaks code. You need a length — preferably the correct on. I tend to use 255 when I don’t know the length:

DECLARE @RemoveBookingId NVARCHAR(255);