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 |
|
Blastrix
Posting Yak Master
208 Posts |
 Posted - 2003-02-19 : 23:17:26
|
| I am absolutely stumped here guys. I have a dynamic SQL query that needs to insert some data into a table variable, and I can't get it to work at all. Here is how I'm executing the code:exec sp_executesql @Query, N'@tmpAds table (tmpID int Identity(1, 1), AdID int) output', @tmpAds = @Ads outputI get the error "Must declare the variable '@Ads'", even though @Ads is definitely declared in the stored procedure, and works fine in every other part. What am I doing wrong here? Is it not possible to pass a table variable through sp_executesql? I know I could use a temp table, but I started trying it with a table variable, and it's just irking me now. Any insight?Thanks,Steve |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-19 : 23:21:44
|
| The table variable will not be in scope for the dynamic SQL. Use a temp table. |
 |
|
|
Blastrix
Posting Yak Master
208 Posts |
Posted - 2003-02-19 : 23:31:09
|
| But isn't that the idea with setting the query to have an output table parameter; to have the resulting table of the query be returned to the calling code? Does it not work like an integer or varchar would? Because both of those work as expected, and I would expect the scope to be the same.Steve |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-20 : 00:04:37
|
| No. Table variables must be created within a procedure, they cannot be passed into, out of, or otherwise manipulated outside the scope of the procedure in which they're created.Just use a temp table. They are not Satan's spawn despite what everyone seems to say about them being so. |
|
|
|
|
|
|
|
|
Dynamic SQL with Table Variable Parameter?