March 18, 2019 at 2:54 pm
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
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;
March 18, 2019 at 3:38 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy