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.
-- 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.