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 «««23456

SET ROWCOUNT and table variable Expand / Collapse
Author
Message
Posted Wednesday, September 1, 2010 5:45 PM
SSC Eights!

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

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:50 PM
Points: 872, Visits: 952
good question. Thanks!
Post #979229
Posted Wednesday, September 8, 2010 9:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 11:35 AM
Points: 311, Visits: 284
Hugo Kornelis (8/26/2010)
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)


Kind of new for me - thanks - great explanation and examples
Post #982391
Posted Tuesday, September 21, 2010 10:25 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:44 PM
Points: 1,117, Visits: 1,231
I'll parrot the thanks. Good question and good examples which I definitely needed.

-Dan B



Post #990521
Posted Tuesday, September 21, 2010 10:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:44 PM
Points: 1,117, Visits: 1,231
Should have listened to my gut instinct on reading "do not run it on production server"


Post #990526
Posted Monday, March 19, 2012 10:45 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 10:52 PM
Points: 483, Visits: 244
Good question and the last name will not be reached.
Post #1269110
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse