set vs. select weirdness - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

set vs. select weirdness

Author

Topic

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-10 : 17:47:33

I just happened upon this today, took me a while to debug! my formerly high opinion of select is now lessened...


declare @i int, @j int
set @i = 12; set @j = 12;

-- now set @i with a select that is not satisfied, 
-- it's value is still 12 after the select!
select @i=id from sysobjects where 1=0        

-- @j is set to null here, as expected
set @j=(select id from sysobjects where 1=0) 

select @i,@j

elsasoft.org

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-10 : 18:05:00

They work the way I would expect.

The first select returned zero rows, so it did not change @i.

Notice that value for @j is NULL if you use SET or SELECT in the code below. The reason is that a subquery returns a scalar, not a rowset, so it will be null.


declare @j int

set @j = 12;
select @j=(select id from sysobjects where 1=0) 

select [@j]=@j

set @j = 12;
set @j=(select id from sysobjects where 1=0) 

select [@j]=@j

Results:
@j          
----------- 
NULL

(1 row(s) affected)

@j          
----------- 
NULL

(1 row(s) affected)

CODO ERGO SUM

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-10 : 18:59:41

eh. i guess i have to agree, but i don't like it!

i know of no other language where you can turn an assignment into a noop in this way, and leave the operand unaffected. it simply rubs me the wrong way.

elsasoft.org

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-10 : 19:47:13

Hehe.. If you want to think of it in terms of other languages; think of it in terms of short circuiting. :)

[url]http://www.mathworks.com/access/helpdesk/help/techdoc/index.html?/access/helpdesk/help/techdoc/ref/logicaloperatorsshortcircuit.html[/url]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-11 : 01:52:39

quote:
Originally posted by jezemine

eh. i guess i have to agree, but i don't like it!

i know of no other language where you can turn an assignment into a noop in this way, and leave the operand unaffected. it simply rubs me the wrong way.

elsasoft.org

I think the same applies to other DBMSs as well

Also refer http://sqlblog.com/blogs/denis_gobo/archive/2007/10/24/3079.aspx

Madhivanan

Failing to plan is Planning to fail

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-11 : 08:54:38

heh. good one.

elsasoft.org

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-11 : 09:07:11

why does that rub you the wrong way?
those 2 statements you run are completly different.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-11 : 09:52:50

they are different, but not completely different. they both act the same when there is a match.

I guess the root of my problem with it is that this is even allowed in SQL:

select @i=id from mytable where...

as MVJ says, the select returns a rowset, but @i is a scalar. they are different types. so the fact that you are allowed to equate them is irritating to me. I come from a background of C/C++/C# so that's my perspective. You can't do this in C# for example:

int i=0;
int[] j = new int[10];
i = j; // oops!

seems like the most you should be allowed to do in sql is this:

select top 1 @i=id from mytable where...

that way you are equating two similar types. even in this case though, @i wouldn't be set to NULL when there's no match...

In the end I guess I just wasn't expecting this behavior. If it's not surprising to anyone else, that's ok.

elsasoft.org

nr
SQLTeam MVY

12543 Posts

Posted - 2008-01-11 : 10:01:01

It's a sql serverextension.
A consequence of allowing

select @i = @i + id from mytable
(and
update tbl
set i = @i, @i = i+1
)
to loop through the set
consider
select @i = @i + id from mytable
if you change that to
select @i = id from mytable
it becomes obvious what it will do.

Not saying this is a good thing but it is a useful feature.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources