C# Tip Article

This is not C# topic, but you might be interested since it is pretty tricky case.

In T-SQL, when one selects a column value and assigns to local variable, if WHERE condition of the query has no row, the query does not assign any new value to the local variable. This can be particularly problematic if the query is used in a loop.

Here is a problematic query example.

-- BUGBUG
DECLARE @AccountNo VARCHAR(10), @CustomerId INT, @ROWS INT, @I INT
SET @AccountNo = NULL
SET @I = 0

DECLARE @Customers TABLE
(
	RowId int primary key identity(1,1),
	CustomerId int
)
INSERT @Customers VALUES (101)
INSERT @Customers VALUES (201)
INSERT @Customers VALUES (301)

SELECT @ROWS = COUNT(0) FROM @Customers

WHILE @I < @ROWS
BEGIN  
	SET @I = @I + 1

	SELECT @AccountNo = a.AccountNo   ---BUG HERE
	FROM @Customers c
	JOIN Accounts a ON c.CustomerId = a.CustomerId
	WHERE RowId = @I

	PRINT 'SEND $100 TO ' + @AccountNo
END

In the example above, @Customers table has 3 rows (CustomerId: 101, 201, 301). And Accounts table has the following 2 rows (CustomerId: 101, 301).

In WHILE loop, it fetches a CustomerId from @Customers table sequentially and then selects Accounts.AccountNo of the customer and assign the column value to @AccountNo variable. In case of CustomerId 101, it can correctly get accountNo (101-101-1001) from Accounts table. But for CustomerId 201, there is no matching row, so SELECT does not assign any value to @AccountNo. This is where error can occur. At this point, @AccountNo has previous value which is 101-101-1001. So 2nd loop actually sends money to previous one (101-101-1001).

How to fix? Initialize @AccountNo vairable in the loop before SELECT statement.