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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy