I have a simple SQL stored procedure that I use to update a table, sometimes I face a deadlock that prevents the update. What can I use in the stored procedure to trap this deadlock and then retry the update again?
You can try/catch it
http://technet.microsoft.com/en-us/library/aa175791%28v=sql.80%29.aspx
DECLARE @Tries tinyint
SET @Tries = 1
WHILE @Tries <= 3
BEGIN
BEGIN TRANSACTION
BEGIN TRY
INSERT Authors VALUES
(@au_id, @au_lname, '', '', '', '', '',
'11111', 0)
WAITFOR DELAY '00:00:05'
SELECT * FROM authors WHERE au_lname LIKE 'Test%'
COMMIT
BREAK
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
ROLLBACK
SET @Tries = @Tries + 1
CONTINUE
END CATCH;
END