Delete sql record with if statement

update dbo.tblMessages
set messageFlags = (messageFlags + 1)
where messageId = @messageId

So that's the update in my proc. I 开发者_运维问答want to be able to delete the record AFTER the update, IF messageFlags becomes '10' after it updates.

How would I do that?

Add the < 10 condition to the WHERE of the UPDATE. If you want to delete when 10 then you are saying "is it 9 now?"

-- DECLARE @rc int
update dbo.tblMessages
set
    messageFlags = (messageFlags + 1)
where messageId = @messageId and messageId < 10
/*
or assign SET @rc = @@ROWCOUNT to do more stuff first
*/
IF @@ROWCOUNT = 0
    DELETE dbo.tblMessages WHERE messageId = @messageId 

Or use the assign feature of UPDATE. Similar to the OUTPUT clause

DECLARE @newvalue int

BEGIN TRANSACTIOn

update dbo.tblMessages
set
    @newvalue = messageFlags = (messageFlags + 1)
where messageId = @messageId

IF @newvalue = 10
    DELETE dbo.tblMessages WHERE messageId = @messageId

COMMIT TRAN

It comes down to the question: do you need the value to actually be 10 first before deleting?

In your proc, you could either have it check for the messageFlags value. If it is going to be 10 (i.e. before the insert happens) then delete it or if you want it after the update happens delete it then (after checking).

You could also use an update trigger to delete it when the value becomes 10.

have you considered CASE statement? http://www.tizag.com/sqlTutorial/sqlcase.php