Scope of a Table Variable in Relation to sp_executesql 1

[Gooser]

I'll try to simplify here since the code is thousands of lines long and unnecessarily complex. (not my code.)

What I have is a stored procedure that builds SELECT statements based on literally hundreds of variables and then runs them. I don't wish to duplicate the logic (or even try to understand the logic) of the SP, I just want to take the output and put it into tables. SO, I have copied the SP and I am adding table variables and trying to populate them with the results of the SELECT statements.

I have declared a table named @egbs_1 and I have put the SELECT statement into a string which I am running with sp_executesql.

SET @newsql_1 = 'INSERT INTO @egbs_1 VALUES ( ' + @newsql_1 + ' )'

EXEC sp_executesql @newsql_1

SELECT * FROM @egbs_1

When I run it, I keep getting this error:

Msg 137, Level 15, State 2, Line 1
Must declare the variable '@egbs_1'.

SO, my questions are; Should my @egbs_1 table variable be out of scope? And, what am I missing here?

 

[gmmastros]

Yes. Your table variable is out of scope. Basically, most things are out of scope that is executed with Exec. Instead of using a table variable, you could try using a global temp table. Basically, replace @egsb_1 with ##egsb_1


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Gooser]

Isn't the overhead for that kind of large?

You see, if I ever get this working I plan to put it into a loop that will run it about 60,000 times, (as part of a one-time data conversion effort, the source database isn't normalized so this seems to be the best/quickest way to extract the data I need immediately.)

--Gooser

 

[gmmastros]

The overhead for a global temp table isn't that much different than the overhead for a table variable. Sure, in a 60k loop, you may notice a difference. I got the impression from your original post that this is a very complicated process. Changing to global temp tables would be the easiest way to get this work. Also, since this is a one-time data conversion effort, I probably wouldn't worry about it too much.

Of course, the alternative would be to rewrite the process.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Gooser]

Thanks, as usual, George!