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 «««34567»»

Table Variable Expand / Collapse
Author
Message
Posted Tuesday, April 30, 2013 6:44 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
L' Eomot Inversé (4/30/2013)
edit: I think this is the first QotD that I've seen which managed to point up two BOL errors.


There were times when you could teach students by BOL...
Oh, good ol' times....
Post #1448285
Posted Tuesday, April 30, 2013 7:36 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 289, Visits: 321
Sergiy (4/30/2013)
antony-688446 (4/30/2013)
For those struggling with the difference between 'False' and 'Unknown', and how the IF statement works, consider what happens if we change the example code a little bit (I think this could have added a couple of wrong answers to the results )
DECLARE @ToCreate bit;

IF @ToCreate <> 1
DECLARE @Table TABLE
(id int, name varchar(50) )
ELSE
INSERT INTO @Table (id, name)
select 1, 'a';

SELECT * FROM @Table;

It now looks like the DECLARE should be executed, as @ToCreate is clearly not 1, but it isn't, as the comparison evaluates to 'Unknown'. This forces the ELSE part of the IF statement.

Hope this helps to clarify why Tom's whinge on BOL is important!

And, on the subject of missing information on BOL, I couldn't find any reference to when variables are created on the link provided...


Actually, DECLARE is executed.
It's executed in both version of the batch - original from the question and your, modifies one.
It will be executed even if you set @ToCreate to "non-executable" value:

DECLARE @ToCreate bit;
SET @ToCreate = 1
IF @ToCreate <> 1
DECLARE @Table TABLE
(id int, name varchar(50) )
ELSE
INSERT INTO @Table (id, name)
select 1, 'a';

SELECT * FROM @Table;

The whole point of the question was - DECLARE is executed regardless of the flow control logic in the code.
Any outcome of IF check - TRUE, FALSE or UNKNOWN - won't affect execution of DECLARE.

The comment would be correct if we put any other "run-time" command in place of DECLARE - SET, SELECT, etc.

ELSE is executed only when IF returns not TRUE (FALSE or NULL).


I understand that :) I didn't explain my example thoroughly enough - I was trying to provide an example to Tom's explanation of tri-state logic. In my example, it looks like the condition should be met, but it isn't...

Technically, the DECLARE isn't executed - see the other example around initialising a variable as part of the DECLARE. The statement is passed, and the variable is created, but the statement itself is NOT executed, so maybe I was correct. Depends on the definition of EXECUTED....
Post #1448288
Posted Tuesday, April 30, 2013 9:22 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
antony-688446 (4/30/2013)
I understand that :) I didn't explain my example thoroughly enough - I was trying to provide an example to Tom's explanation of tri-state logic. In my example, it looks like the condition should be met, but it isn't...

I understand that too.
And I agree with the logic of your explanation.
That's why I added this there:
The comment would be correct if we put any other "run-time" command in place of DECLARE - SET, SELECT, etc.


Technically, the DECLARE isn't executed - see the other example around initialising a variable as part of the DECLARE. The statement is passed, and the variable is created, but the statement itself is NOT executed, so maybe I was correct. Depends on the definition of EXECUTED....

Well, memory is allocated, references are created, for table variable even an object created in tempdb - some actions are taken.
I'd say it's executed.
Just not on the run time but during compilation stage.
Post #1448294
Posted Tuesday, April 30, 2013 9:58 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
antony-688446 (4/30/2013)
see the other example around initialising a variable as part of the DECLARE. The statement is passed, and the variable is created, but the statement itself is NOT executed


That example is actually very good to prove the point but may be very confusing because of the "shortcut" syntax.

if @k = 0 begin declare @j int =  2 end

Since @k = 1 the block "begin-end" is not executed.
Right?
Wrong. Otherwise variable @j would not be declared and we would get "must declare variable @j" error further in the code.
So, it is executed. Right?
Wrong. Because @j does not get initialised with value 2.
What's the... ?

Explanation is simple.
There are actually 2 statements combined together:
declare @j int set @j = 2

Now it all becomes clear:
- DECLARE is executed during compilation, outside of IF..THEN control;
- SET is executed during run time, under IF..THEN control.

Actually, this syntax is one of the worst and most dangerous things introduced by MS in SQL2008.
I saw extremely powerful servers brought down by improper use of this syntax.
Not just tables, not databases. Whole servers, with losing ability to connect to master database.

I probably need to write an article about that experience, if nobody did it before.
Post #1448301
Posted Wednesday, May 1, 2013 3:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 7,874, Visits: 9,613
Sergiy (4/30/2013)
Actually, this syntax is one of the worst and most dangerous things introduced by MS in SQL2008.
I saw extremely powerful servers brought down by improper use of this syntax.
Not just tables, not databases. Whole servers, with losing ability to connect to master database.

I probably need to write an article about that experience, if nobody did it before.

I think that would be a useful article. Might make people understand a little more about language design issues generally as well as in T-SQL.


Tom
Post #1448351
Posted Wednesday, May 1, 2013 6:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 10, 2014 6:19 AM
Points: 262, Visits: 919
L' Eomot Inversé (5/1/2013)
Sergiy (4/30/2013)
[quote]antony-688446 (4/30/2013)
Actually, this syntax is one of the worst and most dangerous things introduced by MS in SQL2008.
I saw extremely powerful servers brought down by improper use of this syntax.
Not just tables, not databases. Whole servers, with losing ability to connect to master database.

I probably need to write an article about that experience, if nobody did it before.

I think that would be a useful article. Might make people understand a little more about language design issues generally as well as in T-SQL.


I second that. It sounds like a great story.
Post #1448393
Posted Wednesday, May 1, 2013 10:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:55 PM
Points: 35,618, Visits: 32,214
VERY cool question, ol' friend. I really had to stop and think about it.

--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1448683
Posted Thursday, May 2, 2013 4:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:50 AM
Points: 1,337, Visits: 2,627
Nice question. Thanks.


Sujeet Singh
Post #1448759
Posted Thursday, May 2, 2013 8:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 4:31 AM
Points: 278, Visits: 242
nice question...

Manik
You cannot get to the top by sitting on your bottom.
Post #1448833
Posted Sunday, May 5, 2013 11:21 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:02 PM
Points: 2,157, Visits: 879
Good Discussion on IF...ELSE and Nulls.

I have to agree with Sean.

The IF isn't TRUE therefore execute the ELSE. This is what happened so no problem.

The Variable had no Value therefore it wasn't 1 therefore the IF condition wasn't met. Therefore use the ELSE.

Regards
David
Post #1449582
« Prev Topic | Next Topic »

Add to briefcase «««34567»»

Permissions Expand / Collapse