Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table Variable


Table Variable

Author
Message
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5846 Visits: 11406
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....
Doze
antony-688446
antony-688446
Old Hand
Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)

Group: General Forum Members
Points: 342 Visits: 371
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 Smile 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....
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5846 Visits: 11406
antony-688446 (4/30/2013)
I understand that Smile 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.
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5846 Visits: 11406
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.
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10731 Visits: 12019
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

Mike Dougherty-384281
Mike Dougherty-384281
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

Group: General Forum Members
Points: 316 Visits: 944
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.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45147 Visits: 39923
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Divine Flame
Divine Flame
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: 1571 Visits: 2801
Nice question. Thanks.


Sujeet Singh
manik_anu
manik_anu
Old Hand
Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)Old Hand (378 reputation)

Group: General Forum Members
Points: 378 Visits: 300
nice question...

Manik
You cannot get to the top by sitting on your bottom.
David Conn
David Conn
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3039 Visits: 1119
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
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