probem with declare

Question

Post reply

probem with declare

Viewing 4 posts - 16 through 18 (of 18 total)

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

jwbart06 Hall of Fame Points: 3291 More actions · Answer 1

I am new to the forum, so any help would be great.

So, when is it a good time to SET and when is it not?

From the article I deduced it is bad to use select , because you have possible multiple rows. SET doesn't allow for multiple rows, values, if it does then I am reading it wrong.(In this example you are only returning one row so it is okay to use select?)

If we wanted to execute the quickest way I would say do this:

insert into u_contadores (b1, b1cont, turno)

values('00003','00003',(select MAX( [turno]) FROM [reporting].[dbo].[u_contadores]))

Also when I execute this insert I get a warning :

Warning: Null value is eliminated by an aggregate or other SET operation.

Any insight would be helpful.

Thank you

Sean Lange SSC Guru Points: 286562 More actions · Answer 2

As a general rule if you have to get the data from sql you are probably best served by using a select.



select @myVal = myVal from myTable where myPrimaryKey = 1

if you already now the value you can use set



set @myVal = 42

There really isn't a right or wrong way to populate variables with values. The example from above is prone to select errors too. If the following was not an aggregate and the select returned more than 1 row you are faced with the same challenge.



set @_type = (select max(turno) from u_contadores)

It all boils down what makes sense with the situation at hand...how many times is that the truth in sql server? 😀

Personally, I think things like the example above are visually hard to read. In contrast.



select @_type = max(turno) from u_contadores

is a LOT easier to read for me. At the end of the day it is more personal preference than right or wrong.

_______________________________________________________________

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/

The Dixie Flatline SSC Guru Points: 53353 More actions · Answer 3

From the article I deduced it is bad to use select , because you have possible multiple rows. SET doesn't allow for multiple rows, values, if it does then I am reading it wrong.(In this example you are only returning one row so it is okay to use select?)

I see your point, JW. In the example below, the SELECT would run without us really knowing which value from the data column in @sample would wind up populating it. On the other hand, the SET would simply fail and throw an error saying multiple rows were returned. So from that point of view, the SET is "safer" than the "SELECT". But either way, the query will have to be rewritten. In practice, when populating variable values from a table, queries should always be framed to return only a single row. (In my opinion, at least.) Maybe it just hasn't bitten me often enough to make me worry about it. 🙂



declare @sample table (data int)

insert into @sample

select 1 union all

select 2 union all

select 3 

declare @target int

set @target = (select data from @sample)

select @target = data from @sample

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

jwbart06 (7/15/2011)

As a syntax thing use SET to assign value to the variable

set @_type = (select max(turno) from u_contadores)

Although it does work the way you wrote it is a little more definite this way.

It made me revisit the difference between SET and SELECT,

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

thank you for the revival. 🙂

From the article you provided the link to...

Are standards important to you? If your answer is 'yes', then you should be using SET.

I'll recommend that it's not and almost never should be because even the ANSI standards keep changing. Besides, true portability is a myth because no maker of any type of RDBMS follows "the standards" 100%. Even the makers of ORM's can't get it right. 😉 Further, adhering to such a limited set of functionality is like requiring someone to only use the 4 basic math functions on their scientific calculator just because there's a chance in the future that someone with only a 4 function calculator will want to use their formulas. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions