Post reply

Deleting rows in SQL 2014

  • March 18, 2019 at 2:54 pm

    #382022

    Hi,
    in a stored procedure, amongst other things  I have some code to delete a user based on their user ID. This proc is called from a C# application I am writing. It worries me when deleting records from a database so I was thinking how I could make sure it is only one record I delete. Initially I used the following, which has been working well, but it just looks dangerous :O)

    delete from UsersCode where id_user =@id_user

    Should I  be doing some kind of check before using a delete statement? It is unlikely that the above code could cause lots of rows to be deleted as I have a specific criteria, and I have a constraint on the UserCode table so there can be no duplicate userIDs. I was thinking of using:

    IF((SELECT count(*) from UsersCode where id_user =@id_user)=1) BEGIN
    print 'Delete'
    END
    else
    begin
    print 'Nothing to delete'
    end

    Am I overthinking this ?
    Any advice is much appreciated.

  • March 18, 2019 at 3:05 pm

    #2024842

    If you test for existence first, you effectively have to access the table twice to achieve your end goal.
    You could always use a transaction, and only commit the transaction if exactly 1 record is deleted.
    DECLARE @RowsDeleted int;

    BEGIN TRANSACTION;

    DELETE FROM UsersCode
    WHERE id_user = @id_user;

    SET @RowsDeleted = @@ROWCOUNT;

    IF (@RowsDeleted = 1)
    BEGIN
    COMMIT TRANSACTION;
    PRINT 'Record Deleted';
    END;
    ELSE IF (@RowsDeleted = 0)
    BEGIN
    ROLLBACK TRANSACTION;
    PRINT 'OOPS - Nothing Deleted';
    END;
    ELSE
    BEGIN
    ROLLBACK TRANSACTION;
    PRINT 'OOPS - Too much Deleted';
    END;

    How to post data/code on a forum to get the best help.

  • March 18, 2019 at 3:38 pm

    #2024844

    This is perfect, thank you so much.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply