Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    4

    Question Unanswered: Get Value from CURSOR

    I'm writing a stored procedure that involves looping through a recordset and using the values from the recordset as parameters for a second stored procedure. Here's what I've got so far...

    My question is, how do I get the value out of the Cursor? There's only one field.

    Declare @Day as int
    Declare @Plant as varchar(30)

    SET NOCOUNT ON

    CREATE Table #Temp (Facility varchar(30), ProductCategory nvarchar(3), Target int, Quantity int, Percentage decimal(10,2), Production_Date smalldatetime,As_Of_Time smalldatetime)

    Declare Facility_Cursor CURSOR
    For Select Distinct(Facility) From ProductionHistory
    OPEN Facility_CURSOR
    Declare @Facility_Cursor as sysname

    FETCH NEXT From Facility_CURSOR into @Facility_Cursor

    WHILE @@FETCHSTATUS = 1

    --YESTERDAY
    Set @Day = -2

    Insert Into #Temp
    exec sp_GetDailyProductionByPlantAndCategory @Day, @Facility, 'NAP'

    --TODAY
    SET @Day = -1
    Insert Into #Temp
    exec sp_GetDailyProductionByPlantAndCategory @Day, @Facility, 'NAP'

    FETCH NEXT FROM Facility_CURSOR into @Facility_Cursor

    CLOSE Facility_Cursor
    DEALLOCATE Facility_CURSOR

    SET NOCOUNT OFF

    Select * From #Temp ORDER BY Production_Date, Facility, ProductCategory DESC

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    I see that you are trying to pass a variable "@Facility" to your sproc without defining it, and the results of the cursor are being placed into @Facility_Cursor. If you change the variable declaration to @Facility and then FETCH NEXT From Facility_CURSOR into @Facility it just might work!

  3. #3
    Join Date
    Feb 2005
    Posts
    4
    That did the trick. I knew it was something simple. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •