call stored procedure in cursor

Hi all
Can I call and use the stored procedure resultset in the
cursor like
DECLARE WeeklyYield_Cursor Cursor For
dbo.RPT_WeeklyYieldCycleTimeForWeekRange '301','327'
Open WeeklyYield_Cursor
Fetch next from WeeklyYield_Cursor into @.WeekID,@.LotID
While @.@.Fetch_Status = 0
begin
up_RPT_PrepareWeeklyDefectDetailData @.Lotid
up_RPT_UpdateWeeklyYieldCycleTime
Fetch next from WeeklyYield_Cursor into @.WeekID,@.LotID
end
CLOSE WeeklyYield_Cursor
DEALLOCATE WeeklyYield_Cursor
Plese help
AnandHi Anand,
You have to insert the results of the stored procedure into a temporary
table and then define the cursor on the temporary table:
CREATE TABLE #WeeklyYield (WeekID INT, LotID INT)
INSERT INTO #WeeklyYield
EXEC dbo.RPT_WeeklyYieldCycleTimeForWeekRange '301','327'
Keep in mind though that cursor are much slower than set based solutions, so
you might want to look if you can rewrite things. The least you should do is
declare your cursor as LOCAL FAST_FORWARD for optimal cursor performance.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Anand" <gurusanand1@.sifymail.com> wrote in message
news:079401c35705$e6e13e00$a301280a@.phx.gbl...
> Hi all
> Can I call and use the stored procedure resultset in the
> cursor like
> DECLARE WeeklyYield_Cursor Cursor For
> dbo.RPT_WeeklyYieldCycleTimeForWeekRange '301','327'
> Open WeeklyYield_Cursor
> Fetch next from WeeklyYield_Cursor into @.WeekID,@.LotID
> While @.@.Fetch_Status = 0
> begin
> up_RPT_PrepareWeeklyDefectDetailData @.Lotid
> up_RPT_UpdateWeeklyYieldCycleTime
> Fetch next from WeeklyYield_Cursor into @.WeekID,@.LotID
> end
> CLOSE WeeklyYield_Cursor
> DEALLOCATE WeeklyYield_Cursor
> Plese help
> Anand|||I assume you're asking if you can use
"dbo.RPT_WeeklyYieldCycleTimeForWeekRange '301','327'" as the cursor...
the answer is no...If you are using SQL Server 2000, you could use a
table function to return a selectable resultset. I've never actually done
it from a cursor, but have done it as part of otehr select statements.
Can't be all that different.
TG
Anand wrote:
> Hi all
> Can I call and use the stored procedure resultset in the
> cursor like
> DECLARE WeeklyYield_Cursor Cursor For
> dbo.RPT_WeeklyYieldCycleTimeForWeekRange '301','327'
> Open WeeklyYield_Cursor
> Fetch next from WeeklyYield_Cursor into @.WeekID,@.LotID
> While @.@.Fetch_Status = 0
> begin
> up_RPT_PrepareWeeklyDefectDetailData @.Lotid
> up_RPT_UpdateWeeklyYieldCycleTime
> Fetch next from WeeklyYield_Cursor into @.WeekID,@.LotID
> end
> CLOSE WeeklyYield_Cursor
> DEALLOCATE WeeklyYield_Cursor
> Plese help
> Anand
/n/n/n==================================*** Sent via DeveloperKB.com http://www.developerkb.com ***
For all your programming needs.
==================================