Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

SET ROWCOUNT and table variable Expand / Collapse
Author
Message
Posted Thursday, August 26, 2010 1:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 2:13 AM
Points: 897, Visits: 1,578
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.
Post #975416
Posted Thursday, August 26, 2010 2:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:35 AM
Points: 6,133, Visits: 8,397
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
Post #975424
Posted Thursday, August 26, 2010 2:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:35 AM
Points: 6,133, Visits: 8,397
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
Post #975426
Posted Thursday, August 26, 2010 2:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:35 AM
Points: 6,133, Visits: 8,397
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
Post #975431
Posted Thursday, August 26, 2010 2:34 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:22 AM
Points: 1,830, Visits: 6,646
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!
Post #975441
Posted Thursday, August 26, 2010 2:50 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 14, 2013 1:05 AM
Points: 96, 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
Post #975447
Posted Thursday, August 26, 2010 2:53 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:22 AM
Points: 1,830, Visits: 6,646
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!)
Post #975449
Posted Thursday, August 26, 2010 3:02 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 14, 2013 1:05 AM
Points: 96, 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 :)

Post #975456
Posted Thursday, August 26, 2010 3:11 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:22 AM
Points: 1,830, Visits: 6,646
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.
Post #975459
Posted Thursday, August 26, 2010 3:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 14, 2013 1:05 AM
Points: 96, Visits: 277
Yip, worrying indeed.

Lets hope sanity prevails and they leave this feature alone

Post #975462
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse