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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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 :)
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,
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?

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
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

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.
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
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
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
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
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
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
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
my bad, my apology.
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.