table variables aren’t visible to inner levels in the call stack...

This is a definition and related sample code:
This code demonstrates that table variables aren’t visible to inner levels in the call stack. The attempt to refer to the table variable from the dynamic batch generates error:
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@T1".

Code:
DECLARE @T1 AS TABLE
(
col1 INT NOT NULL
);
INSERT INTO @T1(col1) VALUES(10);
EXEC('SELECT col1 FROM @T1;');     -- <-- is this the inner level?

Open in new window


Question: Is there any way to modify this code to avoid the error?
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Yup, using temporary tables :)
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
But no way to use table variable?

Could you please write an example to do a similar intended task using table variable?

Another similar question is coming up.

Thanks,
0
Scott PletcherSenior DBACommented:
Yes, local variables are available only to the local scope / batch.

As to the "is this an inner level?" q, let's find out:

CREATE TABLE #T1
(
col1 INT NOT NULL
);
SELECT @@NESTLEVEL AS outer_level;
INSERT INTO #T1(col1) VALUES(10);
EXEC('SELECT @@NESTLEVEL AS inner_level; SELECT col1 FROM #T1');     -- <-- is this the inner level?
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
No, you can't use a table variable, but there's NO harm in that.  Contrary to popular belief, both table variables and temp tables have almost exactly the same usage in tempdb.
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Scott,

Your comment is full of good insights and requires many reads to totally understand it. Really appreciate it.

Mike
0
Anoo S PillaiCommented:
Looking from a different angle -

You can use a table variable, provided the variable is defined in the same scope. Example follows -
EXEC('  DECLARE @T1 AS TABLE ( col1 INT NOT NULL );
		INSERT INTO @T1(col1) VALUES(10);
		SELECT col1 FROM @T1;');

Open in new window

In addition to the point that Scott already mentioned, think about logging, locking and statistics when doing a comparison between temporary tables and table variables. May be a link worth a look -- Here
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Hi Anoo,

Thank you for the tip. I am filling in a table comparing temp tables, table variables, and table expressions with focus on 8 areas (Scope, DDL after creation, indexes, rollback, statistics, physical presence, and use.). I will post this for review and comments shortly with questions on empty and incomplete areas.

I will send a message to you in case you like to take a look at it.

Thanks again,

Mike
0
Anoo S PillaiCommented:
Mike,

Please do drop a message. Would like to have a look at it and thank you for the initiative for the community.

By the way , which is the 8th area  :) ?

Thanks,
Anoo
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
The 8th one is "use" I should have said application. Please see:

http://www.experts-exchange.com/questions/28698801/Temp-table-table-variable-and-table-expression-comparision.html

BTW, I am studying for 70-461 exam and along the way making some notes for my future use as quick reference.

Thanks,

Mike
0
Anoo S PillaiCommented:
All the best for your exam, and the effort that you put for the preparation. I think you are mentioning about 7 points ( not 8 ) .

1) Scope,
2) DDL after creation,
3) indexes,
4) rollback,
5) statistics,
6) physical presence, and
7) use.

Will go though the other post. Suggest you to convert this as an article once it is finalized.

Anoo
0
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
my bad, my apology.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.