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


Table Variable


Table Variable

Author
Message
Sergiy
Sergiy
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25927 Visits: 12473
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
SSC-Addicted
SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)SSC-Addicted (484 reputation)

Group: General Forum Members
Points: 484 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
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25927 Visits: 12473
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
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25927 Visits: 12473
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.
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26166 Visits: 12500
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
SSC Eights!
SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)SSC Eights! (846 reputation)

Group: General Forum Members
Points: 846 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 Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218075 Visits: 41995
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Divine Flame
Divine Flame
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4447 Visits: 2816
Nice question. Thanks.


Sujeet Singh
manik_anu
manik_anu
SSChasing Mays
SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)SSChasing Mays (639 reputation)

Group: General Forum Members
Points: 639 Visits: 313
nice question...

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

Group: General Forum Members
Points: 3524 Visits: 1183
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