Table Variables and EXEC

EndersG

Registered User.
Local time
Today, 10:00
Joined
Feb 18, 2000
Messages
84
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.
 
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
 
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.
 
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?
 

Users who are viewing this thread

Back
Top Bottom