SQL Server not executing Delete statement

Question

I created a SQL query w/c has a Delete statement but unfortunately the Delete statement seems not executing. What's wrong with my SQL statement.

Here is my code :

BEGIN TRAN
BEGIN TRY
DECLARE @Mid VARCHAR(100) = '100303000'
DECLARE @Cnt INT
DECLARE @ScHeader TABLE
(
    ScHeaderId UNIQUEIDENTIFIER
)
declare @SCHeaderId uniqueidentifier
    -- get ids to be deleted 
    INSERT INTO @ScHeader(ScHeaderId)
    SELECT scd.ServiceChargesId
    FROM ServiceChargeDetails scd
    INNER JOIN ServiceCharges sc  ON scd.ServiceChargesId = sc.ServiceChargesId
    INNER JOIN CreditCardMID cc ON cc.CreditCardMIDId = scd.CreditCardMIDId
    WHERE cc.MID = @Mid;
        /*
            do some other task here Related to @ScHeaderId
        */

    SELECT @Cnt = COUNT(*) FROM CreditCardMID WHERE MID = @Mid;
    Print 'Count before delete ' + Cast(@Cnt as VARCHAR(10))
    PRINT 'Deleting Mid ' + @Mid;
    DELETE FROM CreditCardMID WHERE MID = @Mid; -- not executed
    PRINT 'Done deleting mid '; -- -- not executed
    SELECT @Cnt = COUNT(*) FROM CreditCardMID WHERE MID = @Mid;-- not executed
    Print 'Count after delete ' + Cast(@Cnt as VARCHAR(10)) -- not executed
    COMMIT TRAN
END TRY
BEGIN CATCH
    ROLLBACK TRAN
    PRINT @@ERROR
END CATCH

Here is the result from Message window :

enter image description here

Maybe try to print ERROR_MESSAGE() or ERROR_NUMBER() in your CATCH to find out the actual error : Using TRY...CATCH in Transact-SQL — har07
– har07, Commented Apr 18, 2015 at 8:41

user2818323 · Accepted Answer · 2015-04-18 08:01:55Z

3

The "0" appearing after the "(0 row(s) affected)" implies that the delete has not worked and the catch phrase is fired. Can you run this code without the catch (inside a begin tran/rollback tran) pair to see what the error is (i presume its an error from a trigger on CreditCardMID table)

answered Apr 18, 2015 at 8:01

user2818323

462 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Winston Madiano Over a year ago

I found a trigger w/c restricts row deletion based on a certain condition. Thanks user2818323.

Collectives™ on Stack Overflow

SQL Server not executing Delete statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related