Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

SET Vs SELECT - Multiple values and wrong result

Jun 5 2012 12:00AM by Madhivanan   

Both SET and SELECT clauses can be used to assign values to the variables. While SET works for a single variable, SELECT works for more than a variable. If you want to assign values to multiple variables in single shot, SELECT can be used.


SELECT serves two perposes for variables

1 Assign values to variables
2 Return values from variables

Consider the following set of data

create table #t(id int, names varchar(100))
insert into #t(id,names)
select 1,'test1' union all
select 2,'test2' union all
select 3,'test3' union all
select 4,'test4' union all
select 5,'test5' 
Suppose you want to find an id for the name test1 and assign it to a variable. You can use the following methods
declare @id int
set @id=(select id from #t where names='test1')
select @id as id

select @id=id from #t where names='test1'
select @id as id
Both SET and SELECT return the following result
id
-----------
1
Now see what happens when you forgot to specify the WHERE condition.

Using SET
declare @id int
set @id=(select id from #t)
select @id as id
returns the following error
Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
id
-----------
NULL
Using SELECT
declare @id int
select @id=id from #t 
select @id as id
returns the following result
id
-----------
5
As you see the SELECT statement returns multiple ids which SET rejects it and throws an error message and old value is retained for the variable (in the above example it is NULL). The SELECT will not throw an error message but it will assign the value available last in the resultset (However there is no gaurantee if it is last. It changes when you use ORDER BY Clause).

So always make sure that the SELECT query returns single value when you write code that assigns value to a variable. Otherwsie the variable will have wrong/unexpected data.

Tags: SET,SELECT,SQL SERVER


Madhivanan
3 · 40% · 12947
16
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

13  Comments  

  • If I may I'd like to add a little side-note about another difference that a lot of people seem to run into unwillingly. When using just SELECT and the query returns nothing - e.g. when the table is empty or when the WHERE excludes all records - the @variable remains unchanged. It is NOT set to NULL as most people seem to expect.

    Example :

    declare @id int
    select @id = -1 -- init
    select @id = id from #t where names = 'no such a thing present'
    select @id a
    

    However, when combining this with the SET statement it WILL change the variable into NULL even though nothing got returned.

    declare @id int
    select @id = -1 -- init
    set @id=(select id from #t where names='no such a thing present')
    select @id as id
    

    A border-case I know and quite logical if you think about it IMHO, but I've seen many people burn their hands on when they use this in a loop and forget to re-init the @variable first. The SELECT returns nothing and they end up using the value from the previous iteration.

    commented on Jun 5 2012 3:23AM
    Roby Van Hoye
    221 · 1% · 210
  • Good point Roby Van Hoye. SET will set the variable a value NULL when there is no value coming out of the SELECT statement and old value will be retained when there is error. For SELECT the value will never be reset to NULL when select statement resturns nothing.

    commented on Jun 5 2012 3:34AM
    Madhivanan
    3 · 40% · 12947
  • Nice share Madhivanan Sir and nice and important side note Roby Van Hoye.

    Thanks for sharing

    commented on Jun 5 2012 4:42AM
    Suvendu Shekhar Giri
    171 · 1% · 289
  • @Roby, @Madhivanan:

    I can't reproduce this behavior (no value coming from a SELECT, @variable retains last value).

    This will reliably print out the names of the databases until there are no more databases. The exit criteria is the variable being set to NULL by a SELECT assignment of the variable.

    DECLARE @current_id [int] = NULL
    DECLARE @current_name [sysname]
    
    SELECT @current_id = MIN([database_id]) FROM [master].[sys].[databases]
    
    WHILE @current_id IS NOT NULL
        BEGIN
        	SELECT @current_name = [name] FROM [master].[sys].[databases] WHERE [database_id] = @current_id
    
        	PRINT @current_name
    
        	SELECT @current_id = MIN([database_id]) FROM [master].[sys].[databases] WHERE [database_id] > @current_id
        END
    
    commented on Jun 5 2012 5:41AM
    Marc Jellinek
    95 · 2% · 586
  • Marc Jellinek , It is becuase you have used aggregate function MIN for valiable assignment. Aggreates will always show you NULL when there is no data. Refer this post and look at point 13 http://beyondrelational.com/modules/2/blogs/70/posts/14865/null-null-null-and-nothing-but-null.aspx

    commented on Jun 5 2012 5:56AM
    Madhivanan
    3 · 40% · 12947
  • So better to use always "set", except in one case. i.e, assigning multiple values to multiple variables.

    commented on Jun 5 2012 6:38AM
    Ramkoti
    387 · 0% · 105
  • @Madhivanan: That's my point. A SELECT statement that doesn't return rows will NOT always use the previous value. Have to be careful with those absolute statements.

    commented on Jun 5 2012 6:39AM
    Marc Jellinek
    95 · 2% · 586
  • Marc Jellinek , If you re-read my post you can see that old value will be retained when there is error and SET option is used. If there is no error, it is not true.

    commented on Jun 5 2012 7:58AM
    Madhivanan
    3 · 40% · 12947
  • ramkoti, yes follow it

    commented on Jun 5 2012 8:01AM
    Madhivanan
    3 · 40% · 12947
  • @Madhivanan: Where is the error? There is no error raised when a SELECT statement doesn't return records. It's simply a no-op.

    DECLARE @db_name [sysname] = 'foo'
    
    SELECT
        @db_name = [name]
    FROM
        [master].[sys].[databases]
    WHERE [database_id] = 12340987145
    
    SELECT @db_name
    

    No error, but the variable retains it's previous value

    commented on Jun 5 2012 8:47AM
    Marc Jellinek
    95 · 2% · 586
  • Marc Jellinek , When I said "re-read my post" I meant the original blog post. When you look at the second example, the select statement returns multiple ids and SET throws an error and will not assign any value to the variable and the old value will be retained. In your latest example, the select statement does not return a name and SELECT will not assign any value to variable so that it retains the old value if any. I hope this clears this confusion.

    commented on Jun 5 2012 8:58AM
    Madhivanan
    3 · 40% · 12947
  • @Marc Jellinek

    The case where you try to fetch the database-id that comes after the last database actually DOES return a row. That's maybe not what you'd expect, but , Like madhivanan explained, a side-effect of using Aggregation functions.

    As a simple example you can look at the different result of these two queries :

     SELECT Min([database_id]) FROM sys.databases WHERE 1 = 2 -- feel free to replace 1 = 2 with [database_id] > 999999, effect is identical unless you have (had) MANY db's =)
     SELECT TOP 1 [database_id] FROM sys.databases WHERE 1 = 2 ORDER BY [database_id]
    

    on first glance they should always return the same value. In practice they don't as the first version will return NULL (rows affected = 1), yet the second version will return nothing (rows affected = 0). Assuming you assign the output to a variable like this :

     SELECT @id = Min([database_id]) FROM sys.databases WHERE 1 = 2
     SELECT TOP 1 @id = [database_id] FROM sys.databases WHERE 1 = 2 ORDER BY [database_id]
    

    The first one will always put NULL in @id, the second will will not touch whatever is stored in @id.

    In this situation, the statement

    A SELECT statement that doesn't return rows will always use the previous value.

    is an absolute one.

    commented on Jun 5 2012 1:23PM
    Roby Van Hoye
    221 · 1% · 210
  • ANSI Standard SQL has a row constructor assignment: SET () = ();

    Hopeully, MS will catch up with the rest of the SQL world.

    commented on Jun 6 2012 8:37AM
    jcelko
    452 · 0% · 87

Your Comment


Sign Up or Login to post a comment.

"SET Vs SELECT - Multiple values and wrong result" rated 5 out of 5 by 16 readers
SET Vs SELECT - Multiple values and wrong result , 5.0 out of 5 based on 16 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]