Table Variable and Dynamic SQL

Question

Hi All, 

I have a temporary table in my stored procedure. I am using dynamic sql to update it in the stored procedure.
Instead of temporary table I want to use a 'table variable' but dynamic sql is not working along with table variable
(I get error message saying that the table variable needs to be declared though i declared it already.I think it is a scope issue).

Is there any work around for table variable and dynamic sql.Thanks a lot

Answer 1

Nope

---

Chuck

Answer 2

No, there is no workaround unless you're using SQL Server 2008 and up and want to pass this table as a READONLY parameter

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

There is a way to that. You have to define your TABLE VARIABLE inside your dynamic SQL not outside of that. 

Declare @strSQL nVARCHAR(1000)

Set @strSQL = 'Declare @a Table (ID Int) Insert Into @a Select ID From dbo.Employee Select * From @a'

---

Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

Answer 4

There is a way to that. You have to define your TABLE VARIABLE inside your dynamic SQL not outside of that. 

Declare @strSQL nVARCHAR(1000)

Set @strSQL = 'Declare @a Table (ID Int) Insert Into @a Select ID From dbo.Employee Select * From @a'

---

Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

But the table variable is not accessable from outside the dynamic SQL so it is useless

---

Chuck

Answer 5

There is a way to that. You have to define your TABLE VARIABLE inside your dynamic SQL not outside of that. 

Declare @strSQL nVARCHAR(1000)

Set @strSQL = 'Declare @a Table (ID Int) Insert Into @a Select ID From dbo.Employee Select * From @a'

---

Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

But the table variable is not accessable from outside the dynamic SQL so it is useless

---

Chuck

If you PUT that inside your SQL statement is accessible. 

Use AdventureWorks
Go
Declare @strSQL Nvarchar(1000) 
Set @strSQL = 'Declare @myTable Table (EmployeeID Int, Title Nvarchar(100)) Insert Into @myTable Select EmployeeID, Title From AdventureWorks.HumanResources.Employee Where HireDate <= ''' + '1998/01/01''' + ' Select * From @myTable'
Exec sp_ExecuteSQL @strSQL 

--output
EmployeeID	Title
1	Production Technician - WC60
2	Marketing Assistant
3	Engineering Manager

---

Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

Answer 6

OK - but to what end?  If that is really what he was trying to do, he would just select from the table.

I'm having a hard time coming up with a situation where defining a table variable inside a dynamic script would be needed.

---

Chuck

Answer 7

Yes, the dynamic SQL is not part of the procedure, but constitutes a scope of its own. You need to stick with your temp table.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

Is there any work around for table variable and dynamic sql.Thanks a lot

You have to use #temptable, ##globaltemptable or regular (work) table.

Related article:

http://www.sqlusa.com/bestpractices/dynamicsql/

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 9

>If you PUT that inside your SQL statement is accessible.

Arbi - Am I missing something?  @tablevariable cannot be used to communicate between dynamic SQL parent and child processes. Thanks.

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 10

Kalman, 

It is correct, it cannot be accessed from outside and cannot communicate between parent and child process. But it is possible to define that inside a dynamic SQL and use it. 

Unfortunately, it is NOT clear what he/she was trying to do. Maybe there would be some cases that it make sense to do something like that. 

As you saw in the sample, you can use it but that table variable will NOT be accessible from outside of your Dynamic SQL.

Use AdventureWorks
Go
Declare @strSQL Nvarchar(1000) 
Set @strSQL = 'Declare @myTable Table (EmployeeID Int, Title Nvarchar(100)) Insert Into @myTable Select EmployeeID, Title From AdventureWorks.HumanResources.Employee Where HireDate <= ''' + '1998/01/01''' + ' Select * From @myTable'
Exec sp_ExecuteSQL @strSQL 

--output
EmployeeID	Title
1	Production Technician - WC60
2	Marketing Assistant
3	Engineering Manager

---

Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.