June 23, 2003 at 11:03 am
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,
@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
June 24, 2003 at 12:24 am
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
June 24, 2003 at 2:14 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