SQL Server Performance Forum – Threads Archive

Table Variable inside Exec

It is not possible to use table variable using Exec Declare @tablename varchar(50)
declare @T table(id int)
set @tablename=’@t’
insert into @t values(12)
select * from @t — will work
exec(‘Select * from @t’) –Error Must declare the variable ‘@t’
exec(‘Select * from ‘[email protected])–Error Must declare the variable ‘@t’ How can I use that? Madhivanan Failing to plan is Planning to fail

---

You can’t. However, you can use temporary table of table function inside dynamic sql.

---

Select @sql = "Update " + @Tablename + " Set LastNumber ="
+ Cast(@LastNumber as varchar(10)) +
" Where CODE =’" + @secondType + "’" execute (@sql)
I have done this

---

Dinesh, I dont understand. Can you modify the code I have given?
Madhivanan Failing to plan is Planning to fail

---

Declare @tablename varchar(50)
Declare @sql varchar(50)
declare @T table(id int)
set @tablename=’@t’
insert into @t values(12)
set @tablename = ‘Customers’
set @sql = ‘Select * from ‘[email protected]
exec(@sql)

---

This is what I tried earlier which results in Server: Msg 208, Level 16, State 1, Line 1
Invalid object name ‘Customers’. Madhivanan Failing to plan is Planning to fail

---

I tried this query in northwind database. i didn’t have any issues as Customers table exists in the NorthWind table
Check whether Customers table exists in the database which you are trying to run

---

Well.
This is not I wanted
You assigned table name to the string and used in Exec
But my question is to use table variable in Exec
Refer my question again Madhivanan Failing to plan is Planning to fail

---

Hi Madhivanan, backing on dinesh, I even tried what dinesh has suggested. It worked for me.
One more thing I would like to tell you is could you check the code for any existing tables.
If I am not wrong the "Invalid object name ‘customers’" error will occur only when the respective table is not there in the db. If my understanding is wrong please correct me. Regards,
Murali

---

Well.
It works as long as as we give table name and not table variable name Madhivanan Failing to plan is Planning to fail

---

Sorry Madhivan, I made a trivial mistake A variable declared outside of your dynamic sql will have a different scope. You have two options here, the first is put it all in the dynamic sql, i.e. declare your variable in the dynamic sql batch, which I think for you will mean putting the whole thing there (yuk). The other option is to use a temp table Sorry again

---

]]>