| J O Holloway 2002-10-30, 6:23 pm |
| I'm running an openquery using Exec in my procedure, and I'm having a little
trouble getting the data. The data is coming through just fine, but I can't
seem to "grab" it for later use. I want to use the result for further
processing.
The particular offending line is:
exec('select out_cost from openquery (WBW, '''+@ThePhrase+''') ')
I build the sql string up, and then run the exec statement. The data is
being returned just fine (it's just a single datum, a cost of an item). I
want to then use that datum to update a field in another table, but I'm
having trouble. I can't seem to set the returned value to any local
variable, nor can I insert it into a table for later use. It's like it's
slippery, or something; it shows up fine in the QA grid, but I can't USE it.
I'm sure I'm forgetting something simple, and I've been through BOL for a
few hours with no progress.
Any help would be much appreciated. TIA.
The code is below. I'm working on it in Query Analyzer (MS SQL Server 2000
on Win2K Server). The openquery is going to a linked Sybase ASA 7.x server.
---
declare @storenumber integer
declare @itemnumber integer
declare @modnumber integer
declare @qtynumber integer
declare @effectivedate datetime
declare crsrGetCost cursor for
select storenum, itemnum, modifier, qty, tick_date
from TableOfItems
where cost = 0
group by storenum, itemnum, modifier, qty, tick_date
open crsrGetCost
fetch next from crsrGetCost
into @storenumber, @itemnumber, @modnumber, @qtynumber, @effectivedate
while @@fetch_status = 0
begin
declare @ThePhrase varchar(200)
set @ThePhrase =
'call tt.tbd.sp_get_cost(' +
cast(@storenumber as varchar(3)) + ', ' +
cast(@itemnumber as varchar(15)) + ', ' +
cast(@modnumber as varchar(3)) + ', ' +
cast(@qtynumber as varchar(5)) + ', ''''' +
cast(@effectivedate as varchar(12)) + ''''')'
exec('select out_cost from openquery (WBW, '''+@ThePhrase+''') ')
fetch next from crsrGetCost
into @storenumber, @itemnumber, @modnumber, @qtynumber, @effectivedate
end
close crsrGetCost
deallocate crsrGetCost
|