November 24, 2010 at 1:47 pm
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]
November 24, 2010 at 4:40 pm
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);November 24, 2010 at 5:01 pm
And it's also surprising to see how often this type of select statement is in a trigger, handling only one (random) record instead of all of the records being affected.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 24, 2010 at 5:09 pm
Oh yes and, of course you should not do this:
SELECT @variable1 = (SELECT column1 FROM table WHERE column2 = 'somevalue')
unless you want an error for selecting multiple rows in a subquery...
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);November 24, 2010 at 11:28 pm
mister.magoo (11/24/2010)
Oh yes and, of course you should not do this:
SELECT @variable1 = (SELECT column1 FROM table WHERE column2 = 'somevalue')unless you want an error for selecting multiple rows in a subquery...
I would say that would be the best way to do it. Getting an error because the subquery returned multiple rows is WAY better then randomly getting ONE value. If i get an error i can fix it, if it just keeps on going thats BAD. If you get an error you either did something wrong or something is terribly wrong in the DB (if its not supposed to be able to return more then one row). Either way... error = good.
/T
November 25, 2010 at 2:37 am
tommyh (11/24/2010)
mister.magoo (11/24/2010)
Oh yes and, of course you should not do this:
SELECT @variable1 = (SELECT column1 FROM table WHERE column2 = 'somevalue')unless you want an error for selecting multiple rows in a subquery...
I would say that would be the best way to do it. Getting an error because the subquery returned multiple rows is WAY better then randomly getting ONE value. If i get an error i can fix it, if it just keeps on going thats BAD. If you get an error you either did something wrong or something is terribly wrong in the DB (if its not supposed to be able to return more then one row). Either way... error = good.
/T
Hi Tommy, I appreciate your point, but at no point did I suggest allowing a random selection...I am just warning against the pitfalls of a subquery that could conceivably return multiple rows. I would heartily recommend doing something safer such as
DECLARE @myVariable VARCHAR(10)
SELECT TOP 1 @myVariable = column1 FROM myTable WHERE column2='something'
IF @@ROWCOUNT <> 1
BEGIN
-- put some error handling here
-- maybe setting a default value such as NULL
SET @myVariable = NULL
END
--carry on using @myVariable safe in the knowledge that is will be set correctly
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply