filling table variable using dynamic sql

[olichap]

Hello,

I've declared a variable as Table and am trying to fill it. This works fine:

insert into @tblZeroUnit
select FacilityNbr, orderid, detailSeqNbr, SKU, SkuOriginalqty, skushipqty, skuclass, skuuom from database_name.dbo.orderdet where orderid = @orderid and skushipqty = 0

However the name of the database is a variable itself, thus I need to accomplish the same task using dynamic sql. I've tried this:

set @sqltxt = 'insert into @tblZeroUnit
select FacilityNbr, orderid, detailSeqNbr, SKU, SkuOriginalqty, skushipqty, skuclass, skuuom
from ' + @DBNAME + 'dbo.orderdet
where orderid = ' + @orderid + ' and skushipqty = 0'
exec(@sqltxt)

It tells me I have to declare the variable @tblZeroUnit. Anyone know how, or if, I can make this work? I know I could accomplish the same thing using a standard temp table but would rather use the variable if possible.

Thanks,

O'

 

[mrdenny]

Not using a table variable.

When you run dynamic SQL it runs in it's own little world, and can't access variables that are declared in the calling code.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)