Assigning Variables When No Row Exists

A SQL Server tidbit that I never knew before today. This was a test on SQL 2000. I’d be interest to know if you get the same result on something more current.

[Update: still just like this on SQL 2008]

What do you suppose this returns?


declare @x varchar(90)
select @x = 'asfdfs'

-- there is no row in Orders with OrderID = 4550768, 
-- but assume the table name and column names are valid.
select @x = OrderName from Orders where OrderID = 4550768

select @x

declare @x varchar(90)

select @x = 'asfdfs'

-- there is no row in Orders with OrderID = 4550768,

-- but assume the table name and column names are valid.

select @x = OrderName from Orders where OrderID = 4550768

select @x

A – NULL
B – asfdfs
C – an error message
D – OrderName

Correct Answer: B.

I would have expected A. It turns out that I had a variable assignment in a loop, and from time to time, it wasn’t getting assigned a new value. The problem was on rows where the select statement returns no rows. In that case, my varible still had the value from its previous iteration.

Articles By Category

Articles and Pages Tagged…

Assigning Variables When No Row Exists