C# Tips

C# Tip Article

SQL: Select Column When No Row Found

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.

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

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


	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

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.