SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SET ROWCOUNT and table variable


SET ROWCOUNT and table variable

Author
Message
martin.whitton
martin.whitton
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1589 Visits: 1783
It's interesting that a variable can be declared multiple times if it's within a "while" loop (provided that it's only declared once in each iteration).

When I first read the question I thought that declaring @a within the loop would cause an error on the second iteration.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18591 Visits: 12426
da-zero (8/26/2010)
I do not really understand how the scope of the type variable works. It seems you can define it over and over again, without an error being thrown?

and
martin.whitton (8/26/2010)
It's interesting that a variable can be declared multiple times if it's within a "while" loop (provided that it's only declared once in each iteration).

When I first read the question I thought that declaring @a within the loop would cause an error on the second iteration.


The variable is actually declared once. Declarations are not relly executable statements. They are processed when a batch is parsed, not when it it executed. The declaration has to be positioned before the use in "left to right / top to bottom" order, not in execution order. That is why this works, even though the code path that containst the declaration is never executed.
IF 1 = 2
BEGIN;
PRINT 'Skip this';
DECLARE @a int;
END;
ELSE
BEGIN;
SET @a = 1;
END;
SELECT @a;



And this works as well, even though the part where the declaration sits is executed AFTER the assignment:
  GOTO Label2;
Label1:
DECLARE @b int;
SELECT @b;
GOTO Label3;
Label2:
SET @b = 3;
GOTO Label1;
Label3:
go


(Note that I do not endorse using GOTO in T-SQL code, nor deliberately writing spaghetti code for any other purposes than illustrating the difference between order of parsing and order of execution)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18591 Visits: 12426
stewartc-708166 (8/25/2010)
The only possible use for the float datatype that I have found is in the calculations used in astro-physics, where the distance between stars and galaxies is an approximation, at best

Add physics, chemistry and all other parts of science that deal with measured data. All measurements are always approximations.

When you have to deal with extremely large values (as in astro-physics) or extremely small values (like atomic weight or distance in chemistry), floating point data is the only reasonable choice. For other data, both floating point and fixed point canbe used though floating point often is better).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18591 Visits: 12426
Last comment, and then I'll shut up (for now)

Open Minded (8/26/2010)
isn't it possible to use approximation if one is not specific of the exact value of the approximation, like "less than" and "greater than" ?

Yes. A test of "IF @float > 1.0" is okay.
And if you really have to test for equality with floating point data, you have to consider how many of the decimals are relevant and then round: "IF ROUND(@float, 3) = 1.000" should work. If you change the WHILE in this question to "while round(@1,3) <> 1", the code finishes and returns a single value.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Toreador
Toreador
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3812 Visits: 8123
Floats can be useful when the same column needs to be able to hold either large values (lots of numbers before the decimal point) or very small values (lots of numbers after it).
There are of course loads of pitfalls to be aware of, but in general, so longer as you always round the number to an appropriate number of decimal places then things will work.
eg in this qotd
while @i <> 1
could be something like
while round(@i,8) <> 1

However, they are definitely better avoided if there's a reasonable alternative!
Pete Cox
Pete Cox
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 277
Nice question, made me think about what "approximate" means.

Is always true to say, as in the explanation, that because the value of float variable is approximate, it can never = 1?
It must equal some value or other and may under some conditions = 1 perhaps? Or not ? Still don't know really.

Or are we talking about the inapplicability of the equality operator ?

BTW, I think ROWCOUNT stops having this effect in versions of SQL server later than 2005
Toreador
Toreador
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3812 Visits: 8123
Pete Cox (8/26/2010)
BTW, I think ROWCOUNT stops having this effect in versions of SQL server later than 2005


Nope, still works in 2008 (luckily, or our code would break!)
Pete Cox
Pete Cox
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 277
My Local BoL 2005 quote follows


"Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT together with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. Also, for DELETE, INSERT, and UPDATE statements that currently use SET ROWCOUNT, we recommend that you rewrite them to use the TOP syntax. For more information, see DELETE (Transact-SQL), INSERT (Transact-SQL), or UPDATE (Transact-SQL)."


Then MSDN Online BoL has the same Quote for 2008.
(http://msdn.microsoft.com/en-us/library/ms188774.aspx)

So it looks like it "might" be removed in a later release, sometime, perhaps, maybe Smile
Toreador
Toreador
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3812 Visits: 8123
Interesting/worrying! We have lots of code that does something like
declare @i int
set @i = 10
set rowcount @i
insert into tab1
select * from tab2

in order to insert the top n rows from one table into another.
We can't easily use 'top' because it won't accept a variable, ie
select top @i...
won't work, so we'd need to use dynamic sql everywhere.

I've verified that this use of rowcount still works in 2008, and for interest it works with delete as well, ie
set rowcount 1
delete from table1

will only delete 1 row.
Pete Cox
Pete Cox
SSC-Enthusiastic
SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)SSC-Enthusiastic (182 reputation)

Group: General Forum Members
Points: 182 Visits: 277
Yip, worrying indeed.

Lets hope sanity prevails and they leave this feature alone
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search