Temp Tables and Dynamic SQL

Question

Temp Tables and Dynamic SQL

geoff hopkins

Ten Centuries

Points: 1147
More actions
November 15, 2005 at 6:18 am

#167016

Do people have any tips for working around the issue of having to use dynamic SQL and referencing temp tables?

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

If you're using temp tables in order to get values out of dynamic sql, look at sp_executesql. It lets you pass parameters to dynamic sql, both input and output parameters.

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

geoff hopkins Ten Centuries Points: 1147 More actions · Answer 2

I have data in a temp table and need to use dynamic sql to delete/insert data into the main table...

I may be doing something wrong with the dynamic sql if so this is why I am using it.

--

I use the dynamic sql to select which database to write to...

e.g "SELECT * FROM" + @DB_NAME + ".dbo.Table1"

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

Why do you not know what database the table is in?

Could you expalin in a bit more detail the situation and what you're trying to do?

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

geoff hopkins Ten Centuries Points: 1147 More actions · Answer 4

There are several databases the user may wish to write to ... I understand it may sound convoluted but you will just have to trust me on that one there are firm reasons why this has had to be designed in this way....

The simpilest way I can explain is to say I have data in a temp table that has been created from OPENXML that needs to be written to a table within a user specified database, so the only way I could think of this is by passing a parameter to the sproc.

I just need a method or a workaround of using temptables with dynamic sql.

I have tried using dynamic sql to construct the full sproc but concerned of going over the 4000 limit when xml files are involved

noeld SSC Guru Points: 96590 More actions · Answer 5

The easiest way about it is probably to create a global temporary table (##)

then with the same stored procedure in each database call it as:

exec dbname.dbo.procname @param1, @param2 ...

* Noel

geoff hopkins Ten Centuries Points: 1147 More actions · Answer 6

that was the fall back plan, but will require a sproc for each database, which I did not like the sound of... thanks for the help....

noeld SSC Guru Points: 96590 More actions · Answer 7

actually is very easy to do it. If you put it on the model all new databases will inherit the procedure but if the databases are already in place you are pretty much left with Dynamic sql or what I suggested.

good luck

* Noel

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

Why a gobal temporary table?

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