Question about the behavior asigning values to variables

Question

Question about the behavior asigning values to variables

freyes

SSC Enthusiast

Points: 129
More actions
September 12, 2011 at 1:27 am

#257152

I found an isue when i was programing a stored procedure.
I belived that the behavior in both cases must be the same, but no.
In the first case i init @var1 with the value 3, when i try to asign the value who results from the execution of the query, the results is null, because the query returns no results. This is the correct behavior for me (may be i'm wrong)
DECLARE @var1 int
SET @var1=3
SET @var1=(SELECT TOP 1
CantidadVenta
FROM dbo.ZonasGeograficas_TiposInmueble
WHEREcodigozonageo='C00000000000051'
ANDcodigotipoinmueble='F010')
PRINT @var1
In the second case i try to get the same result asigning the value to the variable inside the query, but the result is not the same, after the query the value for @var1 remains 3.
DECLARE @var1 int
SET @var1=3
SELECT TOP 1
@var1=CantidadVenta
FROM dbo.ZonasGeograficas_TiposInmueble
WHEREcodigozonageo='C00000000000051'
ANDcodigotipoinmueble='F010'
PRINT @var1
What do you think is the correct behavior?
May be a bug in SQL Server?
If not this means if we want to asign more than one value to variables we must execute as many times the query as variables we want to fill....

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

The rows returned by SQL will never be ordered. If you an ordered list, you MUST explicitly use ORDER BY clause. The behavior u are seeing is an classic example of that rule. I don't see an ORDER BY clause in query. Introduce ORDER BY, you will see consistent result always 🙂

matt.bowler SSCrazy Points: 2677 More actions · Answer 2

These sites deal with the behaviour where nulls are concerned.

http://ryanfarley.com/blog/archive/2004/03/01/390.aspx

http://vyaskn.tripod.com/differences_between_set_and_select.htm

But cold coffee is right as well - if you want granular control over what is returned via the top clause then you need to specify an order by clause.

freyes SSC Enthusiast Points: 129 More actions · Answer 3

The order and the TOP 1 does't mather.

If you takes off the TOP 1 the behavior it's still the same.

This Top 1 it's only for safety in the code.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

freyes (9/12/2011)
The order and the TOP 1 does't mather.

Bro ( or sis 😀 ) , BOTH matters. TOP needs ORDER BY to give consistent result.Period.

HowardW One Orange Chip Points: 29109 More actions · Answer 5

I think you're missing the OP's point.

The reason for the problem is that the set command is explicitly setting the variable to a result set that evaluates to NULL and the second is not running the variable assignment at all.

If you assign a value within a SELECT statement, the assignment only takes place if there are rows, otherwise the code literally does nothing.

matt.bowler SSCrazy Points: 2677 More actions · Answer 6

In other words use set rather than select to assign to the variable in this case - assuming that you want a null assigned if that was the result. If not case two will leave the variable at the default value if the query returns null. Choose the behaviour that's right for you 😉

freyes SSC Enthusiast Points: 129 More actions · Answer 7

ColdCofee, I agree with you about the importance of the order in the TOP 1 , but this is not the question in this post, the post is about the difference in the behavior between the asignement of the values in variables with the command set or the commad select and his consecuences independently if i use order top or group by.

The consecuences are for example if you need to fill more than one variable with the results of one query, with the command set you need to execute several times the same query:

set @var1=(select field1 from tableA where.....

set @var2=(select field2 from tableA where.....

set @var3=(select field3 from tableA where.....

Instead of execute only one time the query:

select @var1=field1,

@var2=field2,

@var3=field3

from TableA where.....

The answer about the asignement is not performed because the query don't answer any records as sense, but this behavior has consecuences al performance level (in the example 3 executios vs 1 execution).

For the moment i found a way to get the same results :

select @var1=sum(field1),

@var2=sum(field2),

@var3=sum(field3)

from TableA where.....

I hope if you find the same problem can help you.

Thanks for all answers

HowardW One Orange Chip Points: 29109 More actions · Answer 8

Why do your variables already have values that you want to NULL? Surely they should be NULL before the assignment.

Whilst using an aggregate function will likely work for numeric data types (you really better guarantee there's only ever 1 row or you'll get some very odd results), I certainly wouldn't do it from a performance perspective,which seems to be your objection to running multiple SET's. If you are going to use aggregate functions, at least use MIN/MAX, rather than SUM.

Why not just have one select statement that NULL's all the variables, then another one that sets them in a SELECT?

mr_opto Ten Centuries Points: 1214 More actions · Answer 9

This is behaving exactly how I'd expect, I understand what you're saying about call numbers... but this isn't an insurmountable issue.

I think your looking for a technical issue / solution when you should be considering how your code is processing more from a logic stand point, by either checking the values of the variables or using an EXISTS check for the contents of the record that you're looking up on and dealing with the result as appropriate.

Regards,

JC