Table Variables and EXEC

[EndersG]

Can someone explain to me why this does not work?

I declared a table variable as follows:
DECLARE @sqlSTRING varchar(3000)
DECLARE @MyTable TABLE
(EmpName varchar (50), EmpAge int, EmpJobID varchar(10))


I tried to run the following EXEC:
SET @sqlSTRING = 'INSERT INTO @MyTABLE (EmpName, EmpAge, EmpJobID)'
SET @sqlSTRING = @sqlSTRING + ' SELECT EmpName, EmpAge, EmpJobID FROM MyCompany'
EXEC (@sqlSTRING)

MyCompany is a table in the database with approx 10 records.

I get the error:
Server: Msg 137, Level 15, State 2, Line 1
Must declare the variable '@MyTable

But as you can see, I already declared it.

Could it be that SQL Server 2K doesn't recognize @MyTable when you try to pass it as a string using the EXEC function? If that's the case, what are my options? I NEED to dynamically create this Trans-SQL statement.

 

[SQL_Hell]

Hi there

Can you not just do this?

DECLARE @sqlSTRING varchar(3000)
DECLARE @MyTable table(EmpName varchar (50), EmpAge int, EmpJobID varchar(10))

INSERT INTO @MyTable (EmpName, EmpAge, EmpJobID)
SELECT EmpName, EmpAge, EmpJobID FROM MyCompany

I dont see the need for exec'ing a strign of sql here

 

[pdx_man]

Using the EXEC (@sqlSTRING) creates a brand new transaction, which we want it to do so SQL can rollback the statement within @sqlSTRING should it fail. The scope of this transaction is outside the scope of the calling transaction. You need to declare the variable within that transaction.

 

[EndersG]

Thanks for the information, pdx. It makes sense when you explain it that way.

What's interesting is that the same example works when you use a temporary table instead of a table variable. Is this because temp tables exists throughout the session whereas temp variables exists throughout the batch?