Some practical tips against traps in T-SQL
1. Value can be truncated by ISNULL()
--ISNULL truncation
declare @x varchar(5) = NULL
if ISNULL(@x, '123456789') = '12345'
print 'truncated'2. Transaction roll back doesn’t affect local variables including table variables
https://www.brentozar.com/archive/2020/08/half-of-you-dont-understand-variables-and-transactions/
3. Cursor may deadloop with CONTINUE
DECLARE @t TABLE(id INT)
INSERT INTO @t VALUES (11),(22)
DECLARE test_cursor CURSOR FOR
SELECT id from @t ORDER BY id
OPEN test_cursor
DECLARE @tempInt INT
DECLARE @n INT = 0
FETCH NEXT FROM test_cursor INTO @tempInt
WHILE @@FETCH_STATUS = 0
BEGIN
SET @n += 1
PRINT @n
IF @n < 99
--will always loop regardless how many records in the table
--needs to put another FETCH here
CONTINUE
FETCH NEXT FROM test_cursor INTO @tempInt
END
CLOSE test_cursor
DEALLOCATE test_cursor4. NULL fails arithmetic comparisons
--NULL fails arithmetic comparisons
DECLARE @bbb BIT
SET @bbb = NULL
IF @bbb=0 PRINT '000'
ELSE IF @bbb=1 PRINT '111'
ELSE PRINT 'bbb'5. How to choose columns on conditions
--conditional column
DECLARE @tempTable (id INT, msg VARCHAR(20))
DECLARE @preferColumn1 bit = 1
--conditional column in SELECT
SELECT CASE WHEN @preferColumn1=1 THEN id ELSE msg END
FROM @tempTable
--conditional column in WHERE
WHERE (@preferColumn1=1 AND id<99) OR (@preferColumn1<>1 AND msg LIKE '%Hello%')