You probably hear about exception handling in TSQL already but here is a neat use of the feature: TSQL exception handling is very close to the top when it comes to popularity of the new TSQL features. The ability to handle exceptions without having to put an IF @@ERROR!=0 is extremely valuable for ease of coding. There is one pattern, however, that I really found extremely helpful with the new TRY … CATCH: the ability to handle deadlocks and implement a retry logic on the server side.
Quick sample code with a standard disclaimer. This is not intended to demonstrate any best practices but is purely to demonstrate the ability of the exception handling in TSQL.
CREATE SCHEMA jukebox
CREATE TABLE jukebox.artists_tbl(id INT IDENTITY(1,1) PRIMARY KEY,artistname NVARCHAR(128) not null)
INSERT INTO jukebox.artists_tbl VALUES('Spyro Gyra')
INSERT INTO jukebox.artists_tbl VALUES('Vital Information')
-- SESSION #1
DECLARE @retry INT
SET @retry = 5 -- number of retries
WHILE (@retry > 0)
UPDATE jukebox.artists_tbl SET artistname='Paco De Lucía' WHERE id = 1
WAITFOR DELAY '00:00:10'
UPDATE jukebox.artists_tbl SET artistname='Jan Garbarek' WHERE id = 2
if (error_number() = 1205)
SET @retry=@retry-1 --retry
SET @retry=-1 --not a deadlock
-- SESSION #2
UPDATE jukebox.artists_tbl SET artistname='Chick Corea Electric Band' WHERE id = 2
UPDATE jukebox.artists_tbl SET artistname='Weather Report' WHERE id = 1
You need to be quick and run SESSION #1 and SESSION #2 codes in two separate management studio connections. Give it a try and let me know if this scenario works for you.