nested cursors

  • Somehow the 2nd stored procedure that I call within the first stored procedure is closing the cursor for the first stored procedure-what am I missing??

    First stored procedure:

    CREATE procedure sp_InputAll

    AS

    set nocount on

    DECLARE @Jan varchar(15)

    DECLARE @Feb varchar(15)

    DECLARE @mar varchar(15)

    DECLARE @Apr varchar(15)

    DECLARE @May varchar(15)

    DECLARE @Jun varchar(15)

    DECLARE @Jul varchar(15)

    DECLARE @Aug varchar(15)

    DECLARE @Sep varchar(15)

    DECLARE @Oct varchar(15)

    DECLARE @Nov varchar(15)

    DECLARE @Dec varchar(15)

    DECLARE@Tot varchar(20)

    DECLARE @year int

    DECLARE @brand_cd char(2)

    DECLARE @cmplt_code char(2)

    DECLARE @fetch_status1 int

    DECLARE BRANDS CURSOR FOR

    SELECT

    brand_cd

    FROM

    brand

    ORDER BY

    brand_name

    OPEN BRANDS

    FETCH NEXT FROM BRANDS

    INTO @brand_cd

    set @fetch_status1=@@FETCH_STATUS

    WHILE @fetch_status1=0

    BEGIN

    set @year=datepart(year,getdate())-1

    ----Execution of 2nd stored procedure:

    EXECUTE sp_ReturnMonthly

    @brand_cd,

    @year,

    @cmplt_code = @cmplt_code OUTPUT,

    @Jan = @Jan OUTPUT,

    @Feb = @Feb OUTPUT,

    @mar = @mar OUTPUT,

    @Apr = @Apr OUTPUT,

    @May = @May OUTPUT,

    @Jun = @Jun OUTPUT,

    @Jul = @Jul OUTPUT,

    @Aug = @Aug OUTPUT,

    @Sep = @Sep OUTPUT,

    @Oct = @Oct OUTPUT,

    @Nov = @Nov OUTPUT,

    @Dec = @Dec OUTPUT,

    @Tot = @Tot OUTPUT

    -----THIS IS WHERE ERROR OCCURS: "CURSOR NOT OPEN"

    FETCH NEXT FROM BRANDS

    INTO @brand_cd

    set @fetch_status1=@@FETCH_STATUS

    END

    CLOSE BRANDS

    DEALLOCATE BRANDS

    2nd stored procedure:

    CREATE procedure sp_ReturnMonthly

    @brand_cd as char(2),

    @year as int,

    @cmplt_code as char(2) output,

    @Jan as varchar(15) output,

    @Feb as varchar(15) output,

    @mar as varchar(15) output,

    @Apr as varchar(15) output,

    @May as varchar(15) output,

    @Jun as varchar(15) output,

    @Jul as varchar(15) output,

    @Aug as varchar(15) output,

    @Sep as varchar(15) output,

    @Oct as varchar(15) output,

    @Nov as varchar(15) output,

    @Dec as varchar(15) output,

    @Tot as varchar(15) output

    as

    DECLARE @currentYearSum int

    DECLARE @previousYearSum int

    DECLARE @string1 VARCHAR(10)

    DECLARE @string2 VARCHAR(10)

    DECLARE @string3 VARCHAR(10)

    DECLARE@month int

    DECLARE @fetch_status2 int

    DECLARE Cmplt_Code CURSOR FOR

    select

    cmplt_code

    from

    cmplt_code

    where

    brand=@brand_cd and

    cmplt_code <> '99'

    order by cmplt_code

    OPEN Cmplt_Code

    FETCH NEXT FROM Cmplt_Code

    INTO @cmplt_code

    set @fetch_status2=@@FETCH_STATUS

    WHILE ( @fetch_status2= 0)

    BEGIN

    select @cmplt_code

    WHILE (@Month <> 14)

    BEGIN

    print @month

    ---ETC,ETC.----

    set @month=@month+1

    END

    set @month=1

    FETCH NEXT FROM Cmplt_Code

    INTO @cmplt_code

    set @fetch_status2=@@FETCH_STATUS

    END

    CLOSE Cmplt_Code

    DEALLOCATE Cmplt_Code

  • I am not sure if this always works, but could you try removing the print and select statements in the following code (for second SP)

    WHILE ( @fetch_status2= 0)

    BEGIN

    select @cmplt_code

    WHILE (@Month <> 14)

    BEGIN

    print @month

    Do Let me know if it worked for you

    Edited by - mandard on 06/24/2003 12:24:39 AM

  • Thinking outside the box, why do you have to use a cursor in this case. From what I can see you are collating data for each of the brands in the first cursor. How about dropping the brand data into a temporary # table with empty slots for the collated data from the second proc. Your second procedure will be able to see the # table and you can work down that populating the empty slots. (if you can avoid a cursor in the second proc even better, maybe a derived table in the FROM clause)

    This will be more effecient than the two cursor solution and will avoid the problem you're having with premature closure.....

    I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961


    I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply