variable assignment to SELECT, and empty result sets

• rschoedel

  SSC Veteran

  Points: 271

  More actions

  November 24, 2010 at 1:47 pm

  #97605

  I've been using SQL for long enough that I shouldn't be suprised by what appears to be a fairly elemetary built-in behavior. But then, humility is a virtue, and you're never too young to learn, right?!

  I'll describe what came as a surprise to me, and I wanted to ask if others had a good pattern for checking for this.

  When a T-SQL variable is first assigned in the fashion depicted below, we'd expect that @variable1 either gets the value of column1, or else it remains NULL if the 'somevalue' entry of column2 was never located.

  [font="Courier New"]SELECT @variable1 = column1 FROM table WHERE column2 = 'somevalue'[/font]

  And in the first case that is correct. But once a variable such as @variable1 gets a value, if this type of query is done subsequent times, the results seem different. It will either get the located value, or it will retain its previous value (as opposed to NULL) if no records meet the where condition. (This behavior is actually the same as in the typical case -- it's just that variables are initialized to NULL upon declaration).

  However, if you use this form, it will work as expected:

  [font="Courier New"]SELECT @variable1 = (SELECT column1 FROM table WHERE column2 = 'somevalue')[/font]

  To see for yourself, this snippet of code creates a temporary table from the xp_msver system procedure, and then runs a couple of queries to illustrate this.

  CREATE TABLE #msver (

  index_valINT,

  nameVARCHAR(50),

  internal_valueINT,

  character_valueVARCHAR(255) )

  INSERT #msver

  EXEC master.dbo.xp_msver

  select * from #msver

  declare @key1 int, @key2 int, @key3 int

  select @key1 as [Key 1], @key2 as [Key 2]

  -- @key1 is found

  select @key1 = index_val from #msver where Name = 'Platform'

  select @key1 as [Key 1]

  -- @key1 not found, retains previous value

  select @key1 = index_val from #msver where name = 'Foo'

  select @key2 = (select index_val from #msver where name = 'Foo')

  select @key1 as [Key 1], @key2 as [Key 2]

  -- @key1 gets the last value found when multiple records returned

  select @key1 = index_val from #msver

  select @key1 as [Key 1]

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  November 24, 2010 at 4:40 pm

  #1255393

  I can't say I have a "pattern" for checking for this - you either spot it or you don't...

  However, there is another gotcha here that I see a lot of people falling into:

  If the select returns multiple rows, you have no way of knowing or predicting which row's value from column1 will be in the variable after the statement has executed.

  It is surprising how many times I have seen this kind of select when people can't understand why their query is not working as they expect.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]