Temp Tables and Dynamic SQL

Question

Temp Tables and Dynamic SQL

kalhara

SSC Veteran

Points: 252
More actions
February 9, 2008 at 3:03 pm

#183764

Can I pass a table variable to a dynamic sql which I'm executing through sp_executesql

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 1

I'm not entirely sure I understand your question, but I'll throw this out there to see if it can help your problem.

There is a slight difference between temp tables (create table #temp) and table variables (Declare @temp table) which is the fact that #temp tables are visible in the scope of a procedure called from the procedure that created the table.

This also applies to dynamically executed sql. You can create and populate a #temp table, and it will be visible within the scope of the SQL string executed with sp_executesql.

For example

--legal

create table #temp(a int)

--populate with some values

exec sp_executesql 'Select * From tablea INNER JOIN #temp on tablea.a = #temp.a'

--illegal

declare @temp table(a int)

--populate with some values

exec sp_executesql 'Select * From tablea INNER JOIN @temp t on tablea.a = t.a'

I hope this makes sense, and I hope I understood your question.

kalhara SSC Veteran Points: 252 More actions · Answer 2

Thanks jermy

I messed up with my wordings what I want to do is to pass a temporary table variable to a dynamic sql execution.

Ian Yates SSCoach Points: 19738 More actions · Answer 3

As Jeremy explained, you cannot do that. You'll need to use a temporary table rather than a table variable. Temporary tables have a wider scope - they'll be visible by any child procedures or child dynamic SQL code.

Thus you can have

procedure A -- creates temporary table. Calls procedure B

procedure B -- does some work with the temp table. Executes some dynamic SQL

Dyn SQL -- Further manipulates temp table

You cannot have

procedure A --calls some dynamic SQL

Dyn SQL --creates temp table. (SQL will then free the temp table as it goes out of scope at the end of the dyn SQL)

procedure A --attempts to use temp table. ERROR

Your situation would be like the first one so it will work if you use a temporary table. Look up "create table" in SQL Books Online or search for articles on this site about temporary tables.

The syntax is roughly

declare @myTable table( colA int primary key clustered, colB varchar(50), colC uniqueidentifier )

becomes

create table #myTable( colA int primary key clustered, colB varchar(50), colC uniqueidentifier ).

Then when you're done with it you can explicitly drop it by doing

drop table #myTable

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

All good replies... but they all seem to miss the mark a bit. This question seems to be more about what you can and cannot do with variables in Sp_ExecuteSQL...

Sp_ExecuteSQL is specially made so that you can, in fact, pass local variables to the dynamic SQL without a scope violation... and the question, as I understand it, is can those local variables be table variables?

I dunno... yet... 😉

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 5

Good evening,

http://support.microsoft.com/kb/305977/en-us

You cannot use the EXEC statement or the sp_executesql stored procedure to run a dynamic SQL Server query that refers a table variable, if the table variable was created outside the EXEC statement or the sp_executesql stored procedure. Because table variables can be referenced in their local scope only, an EXEC statement and a sp_executesql stored procedure would be outside the scope of the table variable. However, you can create the table variable and perform all processing inside the EXEC statement or the sp_executesql stored procedure because then the table variables local scope is in the EXEC statement or the sp_executesql stored procedure.

I have also tried using a tabvar but I got the following error (the variable was defined though!)

Msg 137, Level 15, State 2, Line 10

Must declare the scalar variable "@TabVar".

Best Regards,

Chris Büttner

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

Now we're talking! Verifiable documentation from MS (usually) works for me! Thanks, Chris!

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Of course, not being always swayed by "official" documentation, I've tried it... only to be frustrated by the very same error messages as Kalhara was. I'd have to agree that it's just not possible to do with table variables.

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

It's possible that this will be allowed in SQL 2008, as one of the changes in 2008 is to allow passing of table variables to stored procedures.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

GSquared SSC Guru Points: 260824 More actions · Answer 9

You have the additional option (beyond temp tables), of converting the table variable to XML and passing that to the dynamic SQL.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

kalhara SSC Veteran Points: 252 More actions · Answer 10

Thanks all for all the good replies. I have decided to go with temporary tables. The "The Curse and Blessings of Dynamic SQL" is a good one to have a look http://www.sommarskog.se/dynamic_sql.html#storedprocedures

Seems SQL Server 2008 coming up with some goodies

Cheers

Kalhara

TraderSam SSCrazy Points: 2931 More actions · Answer 11

Just out of curiousity, since you can't use a table variable in dynamic SQL, could you create a UDF that returns a table that was populated based on some param passed in, and then use that UDF in the dynamic SQL?

What you would be doing is using the UDF in place of the local table variable. I have done this before, but not in dynamic SQL.

If it was easy, everybody would be doing it!;)