18 May 2006 13:59 tonyrogerson

T-SQL Value assignment SET vs SELECT

On occasion advice is given never to use SELECT when you are assigning values because it allows more than one row to be returned and that puts a question on what values the variables get assigned to. SET on the other hand will fail if more than one row is returned.

 

For example, the query below using SELECT actually returns 3 rows and @reserved gets set to the value of (randomly) one of those 3 rows.

declare @reserved int

select @reserved = reserved
from sysobjects so
   inner join sysindexes si on si.id = so.id
where so.name = 'sysobjects'
  and so.type = 'S'

print '@@rowcount = ' + cast( @@rowcount as varchar(10) )
print '@reserved = ' + cast( @reserved as varchar(10) )

On the other hand using SET will give an error and @reserved will be left as it was before the SET statement executed, I must admit I thought it would get set to NULL until I tried it!

declare @reserved int
set @reserved =
0
set @reserved = (
 
      select
reserved
      from sysobjects so
            inner join sysindexes si on si.id = so.
id
      where so.name = 'sysobjects
'
          and so.type = 'S' )

print '@@rowcount = ' + cast( @@rowcount as varchar(10) )
print '@reserved = ' + cast( @reserved as varchar(10)
)

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.

@@rowcount = 0

 

The SET statement is great if you are only assigning one value and that’s where I tend to use it, however if you want to assign many values from a singleton select to many variables then you’d need to have multiple SET statements and multiple executions of the same query.

 

Check out we are using SELECT to assign multiple variables, but we still get the logic error of multiple rows being returned…

declare @reserved int,
        @rowcnt int,
        @used int

select @reserved = reserved,
       @rowcnt = rowcnt,
       @used = used
from sysobjects so
   inner join sysindexes si on si.id = so.id
where so.name = 'sysobjects'
  and so.type = 'S'

print '@@rowcount = ' + cast( @@rowcount as varchar(10) )
print '@reserved = ' + cast( @reserved as varchar(10) )
print '@rowcnt = ' + cast( @rowcnt as varchar(10) )
print '@used = ' + cast( @used as varchar(10) ) 

Now to do the same with SET you’d need to do this…

declare @reserved int,
        @rowcnt int,
        @used int

set
@reserved = ( 
   
select reserved
   from sysobjects so
      
inner join sysindexes si on si.id = so.id
   where so.name = 'sysobjects'
     and so.type = 'S' )

set @rowcnt = ( 
   select rowcnt
   from sysobjects so
      
inner join sysindexes si on si.id = so.id
where so.name = 'sysobjects'
  and so.type = 'S' )

set @used = ( 
   select used
   from sysobjects so
      inner join sysindexes si on si.id = so.id
   where so.name = 'sysobjects'
     and so.type = 'S' )

print '@@rowcount = ' + cast( @@rowcount as varchar(10) )
print '@reserved = ' + cast( @reserved as varchar(10) )
print '@rowcnt = ' + cast( @rowcnt as varchar(10) )
print '@used = ' + cast( @used as varchar(10) )

I don’t like having to rerun the query, it causes additional IO and CPU just for the sake of getting round the multi row problem that SELECT has, but, there is another solution!

declare @reserved int,
        @rowcnt int,
        @used int

select @reserved = reserved,
       @rowcnt = rowcnt,
       @used = used
from sysobjects so
      inner join sysindexes si on si.id = so.id
where so.name = 'sysobjects'
  and so.type = 'S'

if @@rowcount > 1
   raiserror( 'Could not assign because multiple rows returned', 16, 1 )

print '@@rowcount = ' + cast( @@rowcount as varchar(10) )
print '@reserved = ' + cast( @reserved as varchar(10) )
print '@rowcnt = ' + cast( @rowcnt as varchar(10) )
print '@used = ' + cast( @used as varchar(10) )

Ok, so its not really the same as SET, remember SET does not effect the original value if the SET does not work it just continues processing which to my mind is a bad thing anyway. I guess you’d really want to trap @@ERROR > 0 when using SET and trap @@TRANCOUNT > 1 and @@ERROR when using SELECT so either way you need to do some error checking!

 

Filed under:

Comments

# T-SQL Variables: Multiple Value Assignment

Tony Rogerson brings us an interesting blog post about T-SQL variable assignment and SET vs. SELECT.&amp;nbsp;...

# SET Vs SELECT

31 May 2006 06:28 by Vinod Kumar's Blog

I have seen multiple post on this very topic. And most importantly its a blanket statement that dont...

# Subquery returned more than 1 value

31 May 2006 18:00 by Tony Rogerson's ramblings

I've had so many referrals from google on this error because of my previous blog entry on the T-SQL Value...

# T-SQL Variables: Multiple Value Assignment

13 July 2006 06:03 by Adam Machanic

Originally posted here.

Tony Rogerson brings us an interesting blog post about T-SQL variable assignment...

# re: T-SQL Value assignment SET vs SELECT

22 September 2006 09:17 by RojiPT

Difference Between SET and SELECT

# When is the MAX value not the TOP value

27 October 2006 14:46 by SimonS SQL Server Stuff

Have a look and try the following statements, and try and think what the expected results are. The key...

# When is the MAX value not the TOP value

27 October 2006 14:46 by SimonS' SQL Server Stuff

Have a look and try the following statements, and try and think what the expected results are. The key...

# When is the MAX value not the TOP value

12 August 2007 01:20 by SimonS SQL Server Stuff

Have a look and try the following statements, and try and think what the expected results are. The key

# re: T-SQL Value assignment SET vs SELECT

01 November 2007 14:05 by Madhivanan

# Defensive database programming: SET vs. SELECT.

25 January 2009 18:48 by Alexander Kuznetsov

Comparing SET vs. SELECT is a very popular topic, and much of what I have to say has been said before.