Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pats2kDynasty
Starting Member
16 Posts |
 Posted - 2009-07-18 : 06:30:03
|
| I am using sp_executesql in my SP because the calling SP is providing the table name (@pTable). I am pretty sure that it works thru the EXEC (below), but if I run the select (last line) it will give me an invalid object error. How do I query the temp table (#Results) to find out? SET @sql = N'SELECT * INTO #Results FROM ' + @pTable + ' WHERE parentID = @pSourceDID'SET @paramDef = N'@pSourceDID varchar(11)'EXEC sp_executesql @sql, @paramDef, @pSourceDID=@pSourceDID SELECT * FROM #Results |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-07-18 : 06:33:30
|
your temp table #Result is created inside the dynamic SQL and it it out of scope and get dropped at the end of the sp_executesql.create the temp table #Result first and then use it in your dynamic sqlcreate #Result( . . . )set @sql = N'INSERT INTO #Result SELECT * FROM ' + @pTable + ' WHERE parentID = @pSourceDID'
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
pats2kDynasty
Starting Member
16 Posts |
Posted - 2009-07-18 : 06:39:53
|
| The problem is that the calling SP send in the table name via @pTable so I never know what the fields will be and therefore I can not declare the table. |
|
|
|
pats2kDynasty
Starting Member
16 Posts |
Posted - 2009-07-18 : 07:05:51
|
| I did find this one article - quote below - (http://sqlserver-qa.net/blogs/t-sql/archive/2008/11/13/5068.aspx) but this seems like alot of work if you have 10-12 tables with varying number of fields. "...If the table structure is unknown in advance then you can still create it at the outer level with a dummy column, and then inside a dynamic sql batch alter and add the columns you need/drop the dummy column, followed by insert in another dynamic batch." |
|
|
|
pats2kDynasty
Starting Member
16 Posts |
Posted - 2009-07-18 : 07:26:35
|
| Found my answer. I need to use a global table (##Results). Here is the quote below and the link (http://www.sommarskog.se/dynamic_sql.html#unknowncolumns). "CREATE TABLE with Unknown ColumnsSometimes I see persons on the newsgroups that are unhappy, because they create a temp table from dynamic SQL, and then they can't access it, because it disappeared when the dynamic SQL exited. When told that they have to create the table outside the dynamic SQL, they respond that they can't, because they don't know the structure of the table until run-time.One solution is to create a global temp table, one with two # in the name, for instance ##temp. Such a table is visible to all processes (so you may have to take precautions to make the name unique), and unless you explicitly drop it, it exists until your process exits.But the real question is: what are these guys up to? If you are working with a relational database, and you don't know the structure of your data until run-time, then there is something fundamentally wrong. As I have never been able to fully understand what the underlying business requirements are, I can't really provide any alternatives. But I would suggest that if you need to go this road, you should seriously consider to run your SQL from a client program. Because, all access to that table would have to be through dynamic SQL, and composing dynamic SQL strings is easier in languages with better string capabilities, be that C#, VB or Perl." |
|
|
|
|
|
|
|
|
Get result of #table when using sp_executesql