SELECT @local_variable

Question

Post reply

SELECT @local_variable

Naidu PK

SSCrazy

Points: 2481
More actions
July 28, 2015 at 8:30 pm

#320085

Comments posted to this topic are about the item SELECT @local_variable
Thanks,
Naveen.
Every thought is a cause and every condition an effect
July 29, 2015 at 4:18 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 18 total)

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

Richard Warr SSCertifiable Points: 6966 More actions · Answer 1

Richard Warr

SSCertifiable

Points: 6966

July 29, 2015 at 2:23 am

#1815640

Nice question - I did not see that coming!

SqlMel SSCrazy Points: 2901 More actions · Answer 2

Good question and something to keep in mind always.

I ALWAYS use Top 1 when assigning values to variables even though in all cases I can think of I use a where clause that is supposed to return only one value. It's just a habit I've created.

---------------
Mel. 😎

Naidu PK SSCrazy Points: 2481 More actions · Answer 3

SqlMel (7/29/2015)
I ALWAYS use Top 1 when assigning values to variables even though in all cases I can think of I use a where clause that is supposed to return only one value. It's just a habit I've created.

I keep using Top 1 clause in assigning variables when I know that my select output is same value

Thanks,
Naveen.
Every thought is a cause and every condition an effect

sestell1 SSChampion Points: 10230 More actions · Answer 4

SqlMel (7/29/2015)
Good question and something to keep in mind always.
I ALWAYS use Top 1 when assigning values to variables even though in all cases I can think of I use a where clause that is supposed to return only one value. It's just a habit I've created.

This could mask a problem if something leads to more than one row being returned.

An alternative might be something like

SET @X = (SELECT Value FROM #Values WHERE ....)

This would fail if more than one row is returned, rather than potentially assigning an erroneous value.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

July 29, 2015 at 7:14 am

#1815695

Finally an nice easy one, thanks.

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

SqlMel (7/29/2015)
Good question and something to keep in mind always.
I ALWAYS use Top 1 when assigning values to variables even though in all cases I can think of I use a where clause that is supposed to return only one value. It's just a habit I've created.

Just make sure that when you use TOP you also use an ORDER BY. Otherwise it is rather pointless. 😉

Great question Naveen.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Revenant SSC-Forever Points: 42467 More actions · Answer 7

Revenant

SSC-Forever

Points: 42467

July 29, 2015 at 8:59 am

#1815758

Great question, indeed. Thanks, Naveen!

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 8

SQL_Hunt

SSC-Dedicated

Points: 33518

July 29, 2015 at 9:37 am

#1815777

Was an easy one. Thnx for the question.

Thanks.

TomThomson SSC Guru Points: 104773 More actions · Answer 9

Interesting question. Works in current versions but shows bad practise.

Every SQL Server version I have known would do this, but in theory the value returned could be any row of the table - the optimiser could detect that no row-set is returned by the first select so as soon as the engine reads one value it can use that for the assignment. Even if a rowset were returned, the the rowset could be sorted as the last action of the select statement, ie after the assignment, so not assigning the value from last row according to the ORDER BY clause.

So (at least in theory) it's bad practice to rely on getting 1 here. A clever optimser would make the first select assign 2 (the first row and only selected) and discard the ORDER BY clause. That's what people keep on telling me might happen, and I regard it as a failure of the language definition that meaningless ORDER BY clauses are note precluded by the syntax definition, and I haven't seen any argument that an order clause like this one is meaningful.

Personally I think that for simple cases like this one it would be far more sensible (and deliver better perormance) to write "SELECT @x = MIN(Value) FROM #values" instead of something with a meaningless and potentially ineffective order by clause.

Tom

Naidu PK SSCrazy Points: 2481 More actions · Answer 10

Sean Lange (7/29/2015)
Just make sure that when you use TOP you also use an ORDER BY. Otherwise it is rather pointless. 😉

I agree Sean, and this is a way to pick the first value instead of default last one.

Thanks,
Naveen.
Every thought is a cause and every condition an effect

SqlMel SSCrazy Points: 2901 More actions · Answer 11

I mostly do this to read parameter values that I have set up in tables so in all the cases I can think of I only get returned one row for the query because the syntax is mostly like:

select @value = value from parameter_table where parameter_id = 999

sestell1 (7/29/2015)
SqlMel (7/29/2015)
Good question and something to keep in mind always.
I ALWAYS use Top 1 when assigning values to variables even though in all cases I can think of I use a where clause that is supposed to return only one value. It's just a habit I've created.
This could mask a problem if something leads to more than one row being returned.
An alternative might be something like
SET @X = (SELECT Value FROM #Values WHERE ....)
This would fail if more than one row is returned, rather than potentially assigning an erroneous value.

---------------
Mel. 😎

SqlMel SSCrazy Points: 2901 More actions · Answer 12

Of course. I learned that lesson a looooong time ago 😀

Sean Lange (7/29/2015)
SqlMel (7/29/2015)
Good question and something to keep in mind always.
I ALWAYS use Top 1 when assigning values to variables even though in all cases I can think of I use a where clause that is supposed to return only one value. It's just a habit I've created.
Just make sure that when you use TOP you also use an ORDER BY. Otherwise it is rather pointless. 😉
Great question Naveen.

---------------
Mel. 😎

SqlMel SSCrazy Points: 2901 More actions · Answer 13

...Even if a rowset were returned, the the rowset could be sorted as the last action of the select statement, ie after the assignment, so not assigning the value from last row according to the ORDER BY clause.

VERY interesting point. I had not thougt about this.

---------------
Mel. 😎