Nested Cursors SQL Stored Proc works standalone, but not when called from VB2010 1

BigBruceJ · Jan 31, 2013

SQL Stored Procedure used for Financial Reporting extracts runs fine standalone, but when called by VB2010 fails intermittantly, at different locations (lines where failure occurs are highlighted below). When the @GLClass parameter results in less than 1000 records, the stored proc works just fine, but if the @GLClass parameter results in more, the stored proc fails and at random locations.

Code:

CREATE PROCEDURE [ids].[cst_Insert_GLOutput](
	@GLRptID		INT,	 
	@GLSessionID	INT,
	@GLControlDesc  VARCHAR(100),
	@GLDivision     INT = -1,	 
	@GLClass        INT,
	@Company        VARCHAR(1),
	@GLItemDescr	VARCHAR(253),	 
	@GLLevel1		VARCHAR(100),
	@GLLevel2		VARCHAR(100),
	@GLLevel3		VARCHAR(100),
	@GLLevel4		VARCHAR(100),
	@GLLevel5		VARCHAR(100),
	@GlLevel6		VARCHAR(100),
	@GlLevel7		VARCHAR(100),	 
	@GlLevel8		VARCHAR(100),	 
	@GlLevel9		VARCHAR(100),	 
	@GlLevel10		VARCHAR(100),	 
	@GLItemAmount1	NUMERIC(12,2),	 
	@GLItemAmount2	NUMERIC(12,2),	 
	@GLItemAmount3	NUMERIC(12,2),	 
	@GLItemAmount4	NUMERIC(12,2),	
	@GLItemAmount5	NUMERIC(12,2),	
	@GLItemAmount6	NUMERIC(12,2),	
	@GLItemAmount7	NUMERIC(12,2),	
	@GLItemAmount8	NUMERIC(12,2),	
	@GLRowCode	    VARCHAR(10),
	@GLPrintCode	VARCHAR(10),	 
	@RevSign		VARCHAR(3),	 
	@GLFilter   	VARCHAR(253),	 
	@GLFormula      VARCHAR(253),
	@GLItemInfo1    VARCHAR(50),
	@GLItemInfo2    VARCHAR(50),
	@GLItemDate     VARCHAR(20),
	@GLReport       VARCHAR(1),
	@GLAcctMonth    NUMERIC(4),
	@GLAcctYear     VARCHAR(4),
	@AccumRules1    VARCHAR(12),
	@AccumRules2    VARCHAR(12),
	@AccumRules3    VARCHAR(12),
	@AccumRules4    VARCHAR(12),
	@AccumRules5    VARCHAR(12),
	@AccumRules6    VARCHAR(12),
	@AccumRules7    VARCHAR(12),
	@AccumRules8    VARCHAR(12),
	@AccumRules9    VARCHAR(12),
	@AccumRules10   VARCHAR(12)
	)	 
AS
--============================================================================
-- Created By: Bruce Jenkins 08/24/2012
--============================================================================
-- III - 01/01/1900 - Change Comments
--============================================================================

  DECLARE @NewId		INT;    
  DECLARE @00 as numeric(10,2) = 0,
		  @00P as numeric(10,2) = 0,
		  @01 as numeric(10,2)= 0,
		  @01P as numeric(10,2)= 0,
		  @02 as numeric(10,2)= 0,
		  @02P as numeric(10,2)= 0,
		  @03 as numeric(10,2)= 0,
		  @03P as numeric(10,2)= 0,
		  @04 as numeric(10,2)= 0,
		  @04P as numeric(10,2)= 0,
		  @05 as numeric(10,2)= 0,
		  @05P as numeric(10,2)= 0,
		  @06 as numeric(10,2)= 0,
		  @06P as numeric(10,2)= 0,
		  @07 as numeric(10,2)= 0,
		  @07P as numeric(10,2)= 0,
		  @08 as numeric(10,2)= 0,
		  @08P as numeric(10,2)= 0,
		  @09 as numeric(10,2)= 0,
		  @09P as numeric(10,2)= 0,
		  @10 as numeric(10,2)= 0,
		  @10P as numeric(10,2)= 0,
		  @11 as numeric(10,2)= 0,
		  @11P as numeric(10,2)= 0,
		  @12 as numeric(10,2)= 0,
		  @12P as numeric(10,2)= 0,
		  @Year as VARCHAR(4) = '',
		  @Div as int = 0; 
 DECLARE  @SubDivision as VARCHAR(30) = '';
 DECLARE  @DIVName as VARCHAR(50) = '';
 DECLARE  @GLItemAcct8 as VARCHAR(8)= '';
 DECLARE  @GLTRNAmount as numeric(12,2);
 DECLARE  @GLTRNPrevAmount as numeric(12,2);
 DECLARE  @GLTRNMonth  as Numeric(4);
 DECLARE  @GLTRNDate   as VARCHAR(20);
 DECLARE  @GLTRNDesc   as VARCHAR(50);
 DECLARE  @USETRANMONTH1 as BIT= 0;
 DECLARE  @USETRANMONTH2 as BIT= 0;
 DECLARE  @USETRANMONTH3 as BIT= 0;
 DECLARE  @USETRANMONTH4 as BIT= 0;
 DECLARE  @USETRANMONTH5 as BIT= 0;
 DECLARE  @USETRANMONTH6 as BIT= 0;
 DECLARE  @COMPAREMONTH as NUMERIC(2,0)
 DECLARE  @COMPAREMONTHSTR as VARCHAR(4)
 DECLARE  @COMPAREYEAR as VARCHAR(4)
 DECLARE  @GLTRNID as INT = 0
 DECLARE  @GLItemAmount9 as numeric(10,2) = 0.00
 DECLARE  @GLItemAmount10 as numeric(10,2) = 0.00
 DECLARE  @RPT as VARCHAR(1) = ''
 
 DECLARE  @CY as BIT     = 'FALSE'
 DECLARE  @PY as BIT     = 'FALSE'
 DECLARE  @CM as BIT     = 'FALSE'
 DECLARE  @PM as BIT     = 'FALSE'
 DECLARE  @MON as BIT    = 'FALSE'
 DECLARE  @YTD as BIT    = 'FALSE'
 DECLARE  @DET as BIT    = 'FALSE'
 DECLARE  @SUM as BIT    = 'FALSE'
 DECLARE  @CYOFFSET as INT = 0
 DECLARE  @CMOFFSET as INT = 0
 DECLARE  @SPECIFIEDMONTH as INT = 0
 DECLARE  @SPECIFIEDDIVISION as INT = -1
 DECLARE  @LOOP as INT = 1
 DECLARE  @VALIDACCTFETCH as INT = 1
 DECLARE  @WORKACCUM VARCHAR(12) = '000000000000'
 DECLARE  @HoldAcctYear VARCHAR(4)
 DECLARE  @HoldAcctMonth Numeric(4)
 DECLARE  @TRANTYPE VARCHAR(1)  
 DECLARE  @GLITEMKey as VARCHAR(8) = ''
 DECLARE  @MGAC as VARCHAR(2) = ''
 DECLARE  @NBRAccounts as INT = 0

BEGIN TRY
		 SET @HoldAcctMonth = @GLAcctMonth
		 SET @HoldAcctYear  = @GLAcctYear

 	 	 CREATE TABLE #Temp0 ( 
			mnth INT, 
			Balance NUMERIC(10,2),
			PYBalance NUMERIC(10,2),
			TranType VARCHAR(1),
			Div INT 
		 ) 

		SELECT @NbrAccounts = COUNT(*)
			FROM UC_Jan16_1700.dbo.tmpglmas gm
			INNER JOIN 
					(SELECT [AcctNo] = x.GLAcctNo,
							[Div]    = x.Division,
							[SDiv]   = isnull(sd.SubDivName,'Unknown' + CONVERT(varchar(2),x.subdivision)),
							[DivName]= isnull(dv.DivName,'Unknown' + convert(varchar(2),x.Division)),
							[GLClass] = aa.glclass1ID
						    
					FROM ids.GLRptAssignAccts aa 
					LEFT JOIN ids.GLRptXref x on x.GLRptXrefID = aa.GLRptXrefID
					LEFT JOIN ids.SubDivisions sd on x.SubDivision = sd.SubDivision
					LEFT JOIN UC_Jan16_1700.ids.Divisions dv on dv.Division = x.division and dv.Division not in ('A','B','C','-')
					WHERE aa.GLClass1ID = @GLCLass) ac ON ac.AcctNO = gm.glmas_acct_no
			WHERE gm.glmas_glno =  1 
			GROUP by ac.glclass
	 
		DECLARE GLAcct Cursor for
        Select Distinct  [GL_Acct_8] = gm.glmas_acct_no, 
						 [GLDivisionName] = ac.DivName,
                         [GLDivision] = ac.Div,
                         [SubDivision] = ac.SDiv,
				         [GL_Acct_Desc]= gm.glmas_desc, 
				         [GLReport] = gm.glmas_Report,
				         [janbal] = gm.glmas_janbal,  [janprv]  = gm.glmas_janprv,
						 [febbal] = gm.glmas_febbal,  [febprv]  = gm.glmas_febprv, 
						 [marbal] = gm.glmas_marbal,  [marprv]  = gm.glmas_marprv,
						 [aprbal] = gm.glmas_aprbal,  [aprprv]  = gm.glmas_aprprv,
						 [maybal] = gm.glmas_maybal,  [mayprv]  = gm.glmas_mayprv,
						 [junbal] = gm.glmas_junbal,  [junprv]  = gm.glmas_junprv,
						 [julbal] = gm.glmas_julbal,  [julprv]  = gm.glmas_julprv,
						 [augbal] = gm.glmas_augbal,  [augprv]  = gm.glmas_augprv,
						 [sepbal] = gm.glmas_sepbal,  [sepprv]  = gm.glmas_sepprv,
						 [octbal] = gm.glmas_octbal,  [octprv]  = gm.glmas_octprv,
						 [novbal] = gm.glmas_novbal,  [novprv]  = gm.glmas_novprv,
						 [decbal] = gm.glmas_decbal,  [decprv]  = gm.glmas_decprv
		FROM UC_Jan16_1700.dbo.tmpglmas gm
		INNER JOIN 
				(SELECT [AcctNo] = x.GLAcctNo,
					    [Div]    = x.Division,
					    [SDiv]   = isnull(sd.SubDivName,'Unknown' + CONVERT(varchar(2),x.subdivision)),
					    [DivName]= isnull(dv.DivName,'Unknown' + convert(varchar(2),x.Division))
				FROM ids.GLRptAssignAccts aa 
				LEFT JOIN ids.GLRptXref x on x.GLRptXrefID = aa.GLRptXrefID
				LEFT JOIN ids.SubDivisions sd on x.SubDivision = sd.SubDivision
				LEFT JOIN UC_Jan16_1700.ids.Divisions dv on dv.Division = x.division and dv.Division not in ('A','B','C','-')
				WHERE aa.GLClass1ID = @GLCLass) ac ON ac.AcctNO = gm.glmas_acct_no
		WHERE gm.glmas_glno =  @Company 
		order by gm.glmas_acct_no 		 
		
		OPEN GLAcct  
							
		FETCH NEXT FROM GLAcct INTO @GLItemAcct8, @DivName, @GLDivision, @SubDivision, @GLItemDescr, @GLReport,  
									@01, @01P, @02, @02P, @03, @03P, @04, @04P, @05, @05P,
									@06, @06P, @07, @07P, @08, @08P, @09, @09P, @10, @10P,
									@11, @11P, @12, @12P
							
		
		IF @@FETCH_STATUS = 0
			BEGIN						
				SET @ValidAcctFetch = 1
			END
		else
			BEGIN
				SET @ValidAcctFetch = 999999999
			END

		WHILE @VALIDACCTFETCH <= @NBRAccounts
		BEGIN
[highlight #F57900]				SET @00 = @12P
				SET @GLITEMKey = @GLItemAcct8

				DELETE from  #Temp0 															
	
	
				INSERT INTO #Temp0 VALUES
[/highlight]				 (0, @00,@00P, 0, @GLDivision) 
				,(1, @01,@01P, 0, @GLDivision) 
				,(2, @02,@02P, 0, @GLDivision)
				,(3, @03,@03P, 0, @GLDivision)
				,(4, @04,@04P, 0, @GLDivision) 
				,(5, @05,@05P, 0, @GLDivision) 
				,(6, @06,@06P, 0, @GLDivision)
				,(7, @07,@07P, 0, @GLDivision)
				,(8, @08,@08P, 0, @GLDivision)
				,(9, @09,@09P, 0, @GLDivision)
				,(10,@10,@10P, 0, @GLDivision)
				,(11,@11,@11P, 0, @GLDivision)
				,(12,@12,@12P, 0, @GLDivision)
 

				 WHILE @LOOP < 11	
					BEGIN	
						SET @RPT = @GLREPORT
						SET @WORKACCUM =	 
						case when @LOOP = 1	then @AccumRules1
							 when @LOOP = 2 then @AccumRules2
							 when @LOOP = 3 then @AccumRules3 
							 when @LOOP = 4 then @AccumRules4
							 when @LOOP = 5 then @AccumRules5
							 When @LOOP = 6 then @AccumRules6
							 When @LOOP = 7 then @AccumRules7
							 when @LOOP = 8 then @AccumRules8
							 WHEN @LOOP = 9 then @AccumRules9
							 WHEN @LOOP = 10 then @AccumRules10 end
								 IF  @WORKACCUM <> '000000000000' 
										 --"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
										 --''' PROCESS ACCUMULATOR ONE
										 --"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
									BEGIN
												SET @CY  = 'FALSE'
												SET @PY  = 'FALSE'
												SET @CM  = 'FALSE'
												SET @PM  = 'FALSE'
												SET @MON = 'FALSE'
												SET @YTD = 'FALSE'
												SET @DET = 'FALSE'
												set @SUM = 'FALSE'
												set @MGAC = ''
												SET @GLAcctMonth = @HoldAcctMonth
												SET @GLAcctYear = @HoldAcctYear
							
					
							
												IF SUBSTRING(@WORKACCUM,1,1) = '1'
													BEGIN
														SET @CY = 'TRUE'
													END
												IF SUBSTRING(@WORKACCUM,1,1) = '2'
													BEGIN
														SET @PY = 'TRUE'
													END
												IF SUBSTRING(@WORKACCUM,2,1) = '1'
													BEGIN
														SET @CM = 'TRUE'
													END	
												IF SUBSTRING(@WORKACCUM,2,1) = '2'
													BEGIN
														SET @PM = 'TRUE'
													END		
							 
 												IF SUBSTRING(@WORKACCUM,3,1) = '1'
													BEGIN
														SET @MON = 'TRUE'
													END	
												IF SUBSTRING(@WORKACCUM,3,1) = '2'
													BEGIN
														SET @YTD = 'TRUE'
													END		
									
 												IF SUBSTRING(@WORKACCUM,4,1) = '1'
													BEGIN
														SET @DET = 'TRUE'
														SET @SUM = 'FALSE'
														--SET @RPT = 'I'
													END	
												IF SUBSTRING(@WORKACCUM,4,1) = '2'
													BEGIN
														SET @SUM = 'TRUE'
														SET @DET = 'FALSE'
													END		
											
												IF substring(@GLItemAcct8,1,4) = '3051' -- Retained Earnings always monthly
													BEGIN
														SET @YTD = 'FALSE'
														SET @MON = 'TRUE'
													END
												
												
												SET @MGAC = SUBSTRING(@WORKACCUM,6,1)
												IF @MGAC = '1'
													BEGIN
														PRINT @MGAC
													END
							 
												if SUBSTRING(@WORKACCUM,8,1) <> '0'
													BEGIN
														 SET @SPECIFIEDMONTH = 
															Case SUBSTRING(@WORKACCUM,8,1)
																when '1' then 1
																when '2' then 2
																when '3' then 3
																when '4' then 4
																when '5' then 5
																when '6' then 6
																when '7' then 7
																when '8' then 8
																when '9' then 9
																when 'A' then 10
																when 'B' then 11
																when 'C' then 12
															END
															SET @GLAcctMonth = @SPECIFIEDMONTH
													END
								
												IF SUBSTRING(@WORKACCUM,10,1) = 'D'  -- Division filter feature turned on
													BEGIN
														SET @SPECIFIEDDIVISION = 
															CASE SUBSTRING(@WORKACCUM,9,1)
																WHEN '0' then 0
																WHEN '1' THEN 1
																WHEN '2' THEN 2
																WHEN '3' THEN 3
																WHEN '4' THEN 4
																WHEN '5' THEN 5
																WHEN '6' THEN 6
																WHEN '7' THEN 7
																WHEN '8' THEN 8
																WHEN '9' THEN 9
																WHEN 'A' THEN 10
																WHEN 'B' THEN 11
																WHEN 'C' THEN 12
																WHEN 'D' THEN 13
																WHEN 'E' THEN 14
																WHEN 'F' THEN 15
																WHEN 'G' THEN 16
																WHEN 'H' THEN 17
																WHEN 'I' THEN 18
															END
													END
						 
							 
												IF @PM = 'TRUE'
													BEGIN
														SET @GLAcctMonth = @GLAcctMonth - 1
														if @GLAcctMonth = 0
															BEGIN
																Set @GLAcctMonth = 12
																set @GLAcctYear = @GLAcctYear - 1
															END
													END

									
											   IF @PY = 'TRUE'
													BEGIN
														SET @GLAcctYear = @GLAcctYear - 1
													END

				 
 											if @MON = 'TRUE' and @SUM = 'FALSE'  -- Month Transactions Only - Detail
												BEGIN
													 if @GLAcctMonth = 1 and @PY = 'TRUE'
														BEGIN
														 if @RPT = 'B'  
																	BEGIN
																		DECLARE GLTrans cursor for
																		 SELECT [GLActMonth] = case When @GLAcctMonth < 10 then substring(@GLAcctYear,3,2) +'0' + convert(varchar(2),@GLAcctMonth)
																										 else substring(@GLAcctYear,3,2) + convert(varchar(2),@GLAcctMonth)
																									end,
																				[GLTrnAmount] = tt.PYBalance,
																				[GLTRNPrevAmount] = tt.PYBalance,
																				[TRANTYPE] = tt.TranType,
																				[GLDivision]  = tt.Div,
																				[GlRefDesc] = 'Month Balance',
																				[GLTrnDate] =  case When @GLAcctMonth < 10 then '0' + convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
																									else convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
																							   end,
																				[GLTrnID] = -1
																
																		 FROM #Temp0 tt
													 					 WHERE tt.mnth = @GLAcctMonth  
																		 ORDER BY gltrndate      
																
											 						END

													 			
													 			
						 								 ELSE    -- Report = I
																	BEGIN
																		DECLARE GLTrans Cursor for
																		SELECT [GLActMonth]  =   t.gltrn_act_mth,
																			   [GLTrnAmount] =   t.gltrn_amount,
																			   [GLTRNPrevAmount] = 0,
																			   [TRANTYPE] = 1,
																			   [GLDivision]  =   x.division,
																			   [GLRefDesc]   =   t.gltrn_ref_desc, 
																			   [GLTrnDate]   =   convert(varchar(20),t.gltrn_trandt,101),
																			   [GLTrnID]     =   t.id_num
														 
																		FROM UC_Jan16_1700.dbo.tmpgltrn t
																		Left Join UC_Jan16_1700.dbo.tmpglmas m on m.glmas_acct_no = t.gltrn_acct_no
																		left join ids.GLRptXref x on x.GLAcctNo = t.gltrn_acct_no
																		GROUP BY t.gltrn_act_mth, t.gltrn_amount, t.gltrn_ref_desc, t.gltrn_trandt, t.gltrn_ytdrec,
																				 m.glmas_report, t.gltrn_acct_no, t.AcctMonth, t.acctyear, t.gltrn_glno, t.gltrn_upd, t.id_num, x.division 
																		HAVING t.gltrn_acct_no = @GLItemAcct8 and
																			  t.gltrn_amount   <> 0 and
																			  t.gltrn_ref_desc > '' and
																			  t.AcctMonth      = @GLAcctMonth   and
																			  t.AcctYear       = @GLAcctYear   and
																			  t.gltrn_upd      = 'Y' and
																			  --t.gltrn_ytdrec   = 'Y' and
																			  t.gltrn_glno     = 1 and 
																			  ((x.division  = @SPECIFIEDDIVISION) OR
																				(@SPECIFIEDDIVISION = -1)) 
										 							  ORDER BY gltrndate      
																	END
														END
													ELSE
														BEGIN
														 if @RPT = 'B'  
														 			
																	BEGIN
																	 DECLARE GLTrans Cursor for
																	 SELECT [GLActMonth] =  case When @GLAcctMonth < 10 then substring(@GLAcctYear,3,2) +'0' + convert(varchar(2),@GLAcctMonth)
																										 else substring(@GLAcctYear,3,2) + convert(varchar(2),@GLAcctMonth)
																									end,
																			[GLTrnAmount] = tt.balance,
																			[GLTrnPrevAmount] = tt.PYBalance,
																			[TRANTYPE]   = tt.TranType,
																			[GLDivision] = tt.Div,
																			[GlRefDesc] = 'Prior Period Balance',
																			[GLTrnDate] = case When @GLAcctMonth < 10 then '0' + convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
																									else convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
																							   end,
																			[GLTrnID]   = -1
																	  
																	 FROM #Temp0 tt
																	 WHERE tt.mnth = @GLAcctMonth -1 
																	 
																	 UNION
																		
																	(SELECT [GLActMonth]  =   t.gltrn_act_mth,
																			[GLTrnAmount] =   t.gltrn_amount,
																			[GLTRNPrevAmount] = 0,
																			[TRANTYPE]    = 1,
																			[GLDivision]  =   x.division,
																			[GLRefDesc]   =   t.gltrn_ref_desc, 
																			[GLTrnDate]   =   convert(varchar(20),t.gltrn_trandt,101),
																			[GLTrnID]     =   t.id_num
																	 FROM UC_Jan16_1700.dbo.tmpgltrn t
																			LEFT JOIN UC_Jan16_1700.dbo.tmpglmas m on m.glmas_acct_no = t.gltrn_acct_no
																			LEFT JOIN ids.GLRptXref x on x.GLAcctNo = t.gltrn_acct_no
																	 GROUP BY t.gltrn_act_mth, t.gltrn_amount, t.gltrn_ref_desc, t.gltrn_trandt, t.gltrn_ytdrec,
																			  m.glmas_report, t.gltrn_acct_no, t.AcctMonth, t.acctyear, t.gltrn_glno, t.gltrn_upd, t.id_num, x.division
																	 HAVING t.gltrn_acct_no = @GLItemAcct8 and
																		  t.AcctMonth       = @GLAcctMonth and
																		  t.AcctYear        = @GLAcctYear and
																		  t.gltrn_amount    <> 0 and
																		  t.gltrn_ref_desc  > '' and
																		  t.gltrn_upd      = 'Y' and
																		  --t.gltrn_ytdrec   = 'Y' and
																		  m.glmas_report    = 'B' and 
																		  t.gltrn_glno      = 1)
										 							 END
												 			
												 			
												 			
												 			
					 								 ELSE    -- Report = I
																BEGIN
																	DECLARE GLTrans Cursor for
																	SELECT [GLActMonth]  =   t.gltrn_act_mth,
																		   [GLTrnAmount] =   t.gltrn_amount,
																		   [GLTRNPrevAmount] = 0,
																		   [TRANTYPE] = 1,
																		   [GLDivision]  =   x.division,
																		   [GLRefDesc]   =   t.gltrn_ref_desc, 
																		   [GLTrnDate]   =   convert(varchar(20),t.gltrn_trandt,101),
																		   [GLTrnID]     =   t.id_num
													 
																	FROM UC_Jan16_1700.dbo.tmpgltrn t
																	Left Join UC_Jan16_1700.dbo.tmpglmas m on m.glmas_acct_no = t.gltrn_acct_no
																	left join ids.GLRptXref x on x.GLAcctNo = t.gltrn_acct_no
																	GROUP BY t.gltrn_act_mth, t.gltrn_amount, t.gltrn_ref_desc, t.gltrn_trandt, t.gltrn_ytdrec,
																			 m.glmas_report, t.gltrn_acct_no, t.AcctMonth, t.acctyear, t.gltrn_glno, t.gltrn_upd, t.id_num, x.division 
																	HAVING t.gltrn_acct_no = @GLItemAcct8 and
																		  t.gltrn_amount   <> 0 and
																		  t.gltrn_ref_desc > '' and
																		  t.AcctMonth      = @GLAcctMonth   and
																		  t.AcctYear       = @GLAcctYear   and
																		  t.gltrn_upd      = 'Y' and
																		  --t.gltrn_ytdrec   = 'Y' and
																		  t.gltrn_glno     = 1 and 
																		  ((x.division  = @SPECIFIEDDIVISION) OR
																			(@SPECIFIEDDIVISION = -1)) 
									 							  ORDER BY gltrndate      
																END
														END												
												END

 											if @MON = 'TRUE' and @SUM = 'TRUE'  -- Month Transactions Only - Summarized
												BEGIN
													 if @RPT = 'B'  
																BEGIN
																	DECLARE GLTrans Cursor for

																	 SELECT [GLActMonth]    =   case When @GLAcctMonth < 10 then substring(@GLAcctYear,3,2) +'0' + convert(varchar(2),@GLAcctMonth)
																									 else substring(@GLAcctYear,3,2) + convert(varchar(2),@GLAcctMonth)
																								end,
																			[GLTrnAmount] = sum(tt.balance),
																			[GLTRNPrevAmount] = sum( tt.PYBalance),
																			[TRANTYPE] = max(tt.TranType),
																			[GLDivision]  = max(tt.Div),
																			[GlRefDesc] = 'YTD Balance',
																			[GLTrnDate]  =   case  When @GLAcctMonth < 10 then '0' + convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
																								   else convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
																								end,
																			[GLTrnID] = -1
																	  
																	 FROM #Temp0 tt 
																     WHERE tt.mnth  = @GLAcctMonth  																	 
											
																 END
													ELSE    -- Report = I
																BEGIN
																   DECLARE GLTrans Cursor for
																	SELECT     [GLActMonth] =   case When @GLAcctMonth < 10 then substring(@GLAcctYear,3,2) +'0' + convert(varchar(2),@GLAcctMonth)
																									 else substring(@GLAcctYear,3,2) + convert(varchar(2),@GLAcctMonth)
																								end,
																			   [GLTRnAmount] = SUM(t.gltrn_amount),
																			   [GLTRNPrevAmount] = 0,
																			   [TRANTYPE] = 1,
																			   [GLDivision] = @GLDIVISION, 
																			   [GLRefDesc]  = '',
																			   [GLTrnDate] = case  When @GLAcctMonth < 10 then '0' + convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
																								   else convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
																								end,
																			   [GLTrnID]   = -99
																	FROM   UC_Jan16_1700.dbo.tmpgltrn AS t 
																		LEFT OUTER JOIN ids.GLRptXref AS x ON x.GLAcctNo = t.gltrn_acct_no 
																		LEFT OUTER JOIN UC_Jan16_1700.Dbo.tmpglmas AS m ON m.glmas_acct_no = t.gltrn_acct_no
																	WHERE     (t.gltrn_acct_no = @GLItemAcct8) AND 
																			  (t.AcctMonth  =@GLAcctMonth) AND 
																			  (t.AcctYear = @GLAcctYear) AND 
																			  (t.gltrn_ref_desc > '') AND 
																			  (t.gltrn_upd = 'Y') AND 
																			  --(t.gltrn_ytdrec = 'Y') AND 
																			  (m.glmas_glno = 1) AND 
																			  (t.gltrn_amount <> 0) and 
																		  ((x.division  = @SPECIFIEDDIVISION) OR
																			(@SPECIFIEDDIVISION = -1))
																	GROUP BY t.gltrn_acct_no  
																END
												END





										IF @YTD = 'TRUE' and @SUM = 'FALSE'
												BEGIN
													 if @RPT = 'B'  
																BEGIN
																	DECLARE GLTrans Cursor for
																	 SELECT [GLActMonth] =  case When @GLAcctMonth < 10 then substring(@GLAcctYear,3,2) +'0' + convert(varchar(2),@GLAcctMonth)
																									 else substring(@GLAcctYear,3,2) + convert(varchar(2),@GLAcctMonth)
																								end,
																			[GLTrnAmount] = tt.balance,
																			[GLTRNPrevAmount] = tt.PYBalance,
																			[TRANTYPE] = tt.TranType,
																			[GLDivision]  = tt.Div,
																			[GlRefDesc] = 'Monthly Balance',
																			[GLTrnDate] =  case When @GLAcctMonth < 10 then '0' + convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
																								else convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
																						   end,
																			[GLTrnID] = -1
																	 FROM #Temp0 tt
																	 WHERE tt.mnth <=  @GLAcctMonth  
													 
																	 ORDER BY gltrndate      
												
																END
													ELSE    -- Report = I
																BEGIN

																   DECLARE GLTrans Cursor for
																	SELECT [GLActMonth]  =   t.gltrn_act_mth,
																		   [GLTrnAmount] =   t.gltrn_amount,
																		   [GLTRNPrevAmount] = 0,
																		   [TRANTYPE] = 1,
																		   [GLDivision]  =   x.division,
																		   [GLRefDesc]   =   t.gltrn_ref_desc, 
																		   [GLTrnDate]   =   convert(varchar(20),t.gltrn_trandt,101),
																		   [GLTrnID]     =   t.id_num
																	FROM UC_Jan16_1700.dbo.tmpgltrn t
																	Left Join UC_Jan16_1700.dbo.tmpglmas m on m.glmas_acct_no = t.gltrn_acct_no
																	left join ids.GLRptXref x on x.GLAcctNo = t.gltrn_acct_no
																	GROUP BY t.gltrn_act_mth, t.gltrn_amount, t.gltrn_ref_desc, t.gltrn_trandt, t.gltrn_ytdrec,
																			 m.glmas_report, t.gltrn_acct_no, t.AcctMonth, t.acctyear, t.gltrn_glno, t.gltrn_upd, t.id_num, x.division
																	HAVING t.gltrn_acct_no = @GLItemAcct8 and
																		  t.gltrn_amount   <> 0 and
																		  t.gltrn_ref_desc > '' and
																		  t.AcctMonth      <= @GLAcctMonth   and
																		  t.AcctYear       = @GLAcctYear   and
																		  t.gltrn_upd      = 'Y' and
																		 -- t.gltrn_ytdrec   = 'Y' and
																		  t.gltrn_glno     = 1 and 
																		  ((x.division  = @SPECIFIEDDIVISION) OR
																			(@SPECIFIEDDIVISION = -1))

 																 ORDER BY gltrndate      
																END
												END

										IF @YTD = 'TRUE' and @SUM = 'TRUE'
												BEGIN
													 if @RPT = 'B'  
																BEGIN
																	DECLARE GLTrans Cursor for

																	 SELECT [GLActMonth]    =   case When @GLAcctMonth < 10 then substring(@GLAcctYear,3,2) +'0' + convert(varchar(2),@GLAcctMonth)
																									 else substring(@GLAcctYear,3,2) + convert(varchar(2),@GLAcctMonth)
																								end,
																			[GLTrnAmount] = sum(tt.balance),
																			[GLTRNPrevAmount] = sum( tt.PYBalance),
																			[TRANTYPE] = max(tt.TranType),
																			[GLDivision]  = max(tt.Div),
																			[GlRefDesc] = 'YTD Balance',
																			[GLTrnDate]  =   case  When @GLAcctMonth < 10 then '0' + convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
																								   else convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
																								end,
																			[GLTrnID] = -1
																	  
																	 FROM #Temp0 tt 
																     WHERE tt.mnth <= @GLAcctMonth  																	 
											
																 END
													ELSE    -- Report = I
																BEGIN
																   DECLARE GLTrans Cursor for
																	SELECT     [GLActMonth] =   case When @GLAcctMonth < 10 then substring(@GLAcctYear,3,2) +'0' + convert(varchar(2),@GLAcctMonth)
																									 else substring(@GLAcctYear,3,2) + convert(varchar(2),@GLAcctMonth)
																								end,
																			   [GLTRnAmount] = SUM(t.gltrn_amount),
																			   [GLTRNPrevAmount] = 0,
																			   [TRANTYPE] = 1,
																			   [GLDivision] = @GLDIVISION, 
																			   [GLRefDesc]  = '',
																			   [GLTrnDate] = case  When @GLAcctMonth < 10 then '0' + convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
																								   else convert(varchar(2),@GLAcctMonth) + '/01/20' + SUBSTRING(@GlAcctYear,3,2)
																								end,
																			   [GLTrnID]   = -99
																	FROM       UC_Jan16_1700.dbo.tmpgltrn AS t 
																		LEFT OUTER JOIN ids.GLRptXref AS x ON x.GLAcctNo = t.gltrn_acct_no 
																		LEFT OUTER JOIN UC_Jan16_1700.dbo.tmpglmas AS m ON m.glmas_acct_no = t.gltrn_acct_no
																	WHERE     (t.gltrn_acct_no = @GLItemAcct8) AND 
																			  (t.AcctMonth <=@GLAcctMonth) AND 
																			  (t.AcctYear = @GLAcctYear) AND 
																			  (t.gltrn_ref_desc > '') AND 
																			  (t.gltrn_upd = 'Y') AND 
																			  --(t.gltrn_ytdrec = 'Y') AND 
																			  (m.glmas_glno = 1) AND 
																			  (t.gltrn_amount <> 0) and 
																		  ((x.division  = @SPECIFIEDDIVISION) OR
																			(@SPECIFIEDDIVISION = -1))
																	GROUP BY t.gltrn_acct_no  
																END
												END






							
											OPEN GLTrans   
							
											FETCH NEXT FROM GLTrans INTO @GLTRNMonth, @GLTRNAmount,@GLTRNPrevAmount, @TRANTYPE, @GLDIVISION, @GLTRNDesc, @GLTRNDate, @GLTRNID
								
											if @@FETCH_STATUS = -1 -- NO RECORDS FOUND IN THIS SELECT
												BEGIN
												---'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
												---'''' DEFAULT Level7 8 9 and 10                                           ''
												---'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''		
													SET @GLLevel7 = 'SWE-All Divisions';
													SET @GLLevel8 =  
														CASE 
															WHEN Rtrim(@DivName) = 'Corporate' then 'SG&A'
															ELSE @SubDivision
														END;
													SET @GLLevel9 =  
														CASE
															WHEN @GLDivision = 0 then @SubDivision
															ELSE @DIVName
														END;
													SET @GLLevel10 = @GLItemAcct8
													---''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''	
													---''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''	
										
										
										
	   												IF ISNULL(@GLDIVISION,99) < 99 and ISNULL(@GLDIVISION,99) > -1  --- VALID DIVISION
														BEGIN	
															BEGIN TRANSACTION;
																INSERT INTO ids.GLRptOutput(GLRptID, GLSessionID, GLControlDescr, Division, GLItemAcct, GLItemAcct8,
																			GLItemDescr, GLLevel1, GLLevel2, GLLevel3, GLLevel4, GLLevel5, GLLevel6,  GLLevel7, GLLevel8, GLLevel9, GLLevel10,
																			GLItemAmount1, GLItemAmount2, GLItemAmount3, GLItemAmount4, GLItemAmount5, 
																			GLItemAmount6, GlItemAmount7, GlItemAmount8, GLItemAmount9, GlItemAmount10, GLRowCode, GLPrintCode, GLMapCode, 
																			GLFilter, GLFormula, GLItemInfo1, GLItemInfo2, GLItemDate)
																	VALUES(@GLRptID, @GLSessionID, @GLControlDesc, isnull(@GLDIVISION,99), @GLItemKey, @GLItemAcct8, 
																		   @GLItemDescr, @GLLevel1, @GLLevel2, @GLLevel3, @GLLevel4, @GLLevel5, @GlLevel6,  @GlLevel7, @GlLevel8, @GlLevel9, @GlLevel10,
																		   @GLItemAmount1, @GLItemAmount2, @GLItemAmount3, @GLItemAmount4, @GLItemAmount5, 
																		   @GlItemAmount6, @GlItemAmount7, @GLItemAmount8, @GLItemAmount9, @GlItemAmount10, @GLRowCode, @GLPrintCode, @RevSign, 
																		   @GLFilter, @GLFormula, @GLItemInfo1, isnull(@GLItemInfo2,''), isnull(@GlItemDate,''));			
																SET @NewId = SCOPE_IDENTITY();
											
																IF @NewId < 1 
																	BEGIN
																		RAISERROR(60000, 16, 1, 'Insert failed to return the new Record Id. Contact IT via Help Desk to resolve this issue.');  
																	END
															COMMIT TRANSACTION;
															END
												END
						 
											---'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
											---''' TRANSACTION RECORDS WERE FOUND 
											---'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
						 
						 
											WHILE @@FETCH_STATUS = 0
												BEGIN      

												SET @GLItemInfo2   = @GLTRNMonth;
												SET @GLItemDate    = @GLTRNDate; 
								
												SET @NewId = 0;	
												SET @GLItemInfo1   = @GLTRNDesc;
												SET @COMPAREMONTHSTR  = @GLTRNMonth
												SET @COMPAREMONTH  = CONVERT(NUMERIC(2),SUBSTRING(@COMPAREMONTHSTR,3,2))
												SET @COMPAREYEAR      = YEAR(@GLTRNDate)
								
												IF @TRANTYPE = 0 -- Read activity from balance
													BEGIN
														IF @PY = 'True'  -- Prior Year Balance requested
															BEGIN
																SET @GLTRNAmount = @GLTRNPrevAmount
															END
													END
								
												---''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
												---''' Place Amount in Correct Accum Field
												---''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

												IF @LOOP = 1 or @MGAC = '1'
													BEGIN
														SET @GLItemAmount1 = isnull(@GLTRNAmount,0)
														SET @GLTRNAmount = 0
													END
												IF @LOOP = 2 or @MGAC = '2'
													BEGIN
														SET @GLItemAmount2 = isnull(@GLTRNAmount,0)
														SET @GLTRNAmount = 0
													END
												IF @LOOP = 3 or @MGAC = '3'
													BEGIN
														SET @GLItemAmount3 = isnull(@GLTRNAmount,0)
														SET @GLTRNAmount = 0
													END
												IF @LOOP = 4 or @MGAC = '4'
													BEGIN
														SET @GLItemAmount4 = isnull(@GLTRNAmount,0)
														SET @GLTRNAmount = 0
													END
												IF @LOOP = 5 or @MGAC = '5'
													BEGIN
														SET @GLItemAmount5 = isnull(@GLTRNAmount,0)
														SET @GLTRNAmount = 0
													END
												IF @LOOP = 6 or @MGAC = '6'
													BEGIN
														SET @GLItemAmount6 = isnull(@GLTRNAmount,0)
														SET @GLTRNAmount = 0
													END
												IF @LOOP = 7 or @MGAC = '7'
													BEGIN
														SET @GLItemAmount7 = isnull(@GLTRNAmount,0)
														SET @GLTRNAmount = 0
													END
												IF @LOOP = 8 or @MGAC = '8'
													BEGIN
														SET @GLItemAmount8 = isnull(@GLTRNAmount,0)
														SET @GLTRNAmount = 0
													END
												IF @LOOP = 9 or @MGAC = '9'
													BEGIN
														SET @GLItemAmount9 = isnull(@GLTRNAmount,0)
														SET @GLTRNAmount = 0
													END
												IF @LOOP = 10 or @MGAC = 'A'
													BEGIN
														SET @GLItemAmount10 = isnull(@GLTRNAmount,0)
														SET @GLTRNAmount = 0
													END
		


												---'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
												---'''' DEFAULT Level7 8 9 and 10                                           ''
												---'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''		
													SET @GLLevel7 = 'SWE-All Divisions';
													SET @GLLevel8 =  
														CASE 
															WHEN Rtrim(@DivName) = 'Corporate' then 'SG&A'
															ELSE @SubDivision
														END;
													SET @GLLevel9 =  
														CASE
															WHEN @GLDivision = 0 then @SubDivision
															ELSE @DIVName
														END;
													SET @GLLevel10 = @GLItemAcct8
												---'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
												---''''                                           ''
												---'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''		
							
	   											IF ISNULL(@GLDIVISION,99) < 99 and ISNULL(@GLDIVISION,99) > -1 
													BEGIN	
													BEGIN TRANSACTION;
									

														INSERT INTO ids.GLRptOutput(GLRptID, GLSessionID, GLControlDescr, Division, GLItemAcct, GLItemAcct8,
																	GLItemDescr, GLLevel1, GLLevel2, GLLevel3, GLLevel4, GLLevel5, GLLevel6,  GLLevel7, GLLevel8, GLLevel9, GLLevel10,
																	GLItemAmount1, GLItemAmount2, GLItemAmount3, GLItemAmount4, GLItemAmount5, 
																	GLItemAmount6, GlItemAmount7, GlItemAmount8, GLItemAmount9, GLItemAmount10, GLRowCode, GLPrintCode, GLMapCode, 
																	GLFilter, GLFormula, GLItemInfo1, GLItemInfo2, GLItemDate)
															VALUES(@GLRptID, @GLSessionID, @GLControlDesc, isnull(@GLDIVISION,99), @GLItemKey, @GLItemAcct8, 
																   @GLItemDescr, @GLLevel1, @GLLevel2, @GLLevel3, @GLLevel4, @GLLevel5, @GlLevel6,  @GlLevel7, @GlLevel8, @GlLevel9, @GlLevel10,
																   @GLItemAmount1, @GLItemAmount2, @GLItemAmount3, @GLItemAmount4, @GLItemAmount5, 
																   @GlItemAmount6, @GlItemAmount7, @GLItemAmount8, @GLITEMAmount9, @GLItemAmount10, @GLRowCode, @GLPrintCode, @RevSign, 
																   @GLFilter, @GLFormula, @GLItemInfo1, isnull(@GLItemInfo2,''), isnull(@GlItemDate,''));
									
														SET @NewId = SCOPE_IDENTITY();
									
														IF @NewId < 1 
															BEGIN
																RAISERROR(60000, 16, 1, 'Insert failed to return the new Record Id. Contact IT via Help Desk to resolve this issue.');  
															END
													  COMMIT TRANSACTION;
													  END
												set @GLItemAmount1 = 0;
												set @GLItemAmount2 = 0;
												set @GLItemAmount3 = 0;
												set @GLItemAmount4 = 0;
												set @GLItemAmount5 = 0;
												set @GLItemAmount6 = 0;
												set @GLItemAmount7 = 0;
												set @GLItemAmount8 = 0;
												set @GLItemAmount9 = 0;
												set @GLItemAmount10 = 0;
								
												FETCH NEXT FROM GLTrans INTO @GLTRNMonth, @GLTRNAmount,@GLTRNPrevAmount, @TRANTYPE, @GLDIVISION, @GLTRNDesc, @GLTRNDate, @GLTRNID

												END
												Close GLTrans
												deallocate GLTrans
									END			
								---'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
								---''''' END ACCUMULATER ONE
								---'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
	
					SET @LOOP = @LOOP + 1
					END

		FETCH NEXT FROM GLAcct INTO @GLItemAcct8, @Divname, @GLDivision, @SubDivision, @GLItemDescr, @GLReport, 
									@01, @01P, @02, @02P, @03, @03P, @04, @04P, @05, @05P,
									@06, @06P, @07, @07P, @08, @08P, @09, @09P, @10, @10P,
									@11, @11P, @12, @12P
		IF @@FETCH_STATUS = 0
			BEGIN						
				SET @ValidAcctFetch = @ValidAcctFetch + 1
			END
		ELSE
			BEGIN
				SET @ValidAcctFetch = 999999999
			END

		
		SET @LOOP = 1

	END
	close GLAcct
	deallocate GlAcct
	drop table #Temp0

	SELECT [ErrorNumber] = 0, 
			[ErrorMessage] = 'New ids.GLRptOutput records have been Inserted.',
			[NewID] = @NewId,
			[GLRptID] = @GLRptID;





END TRY
BEGIN CATCH
	If @@TRANCOUNT > 0
		BEGIN
			ROLLBACK TRANSACTION
		END
	INSERT INTO ErrLog (Number, Severity, ErrState, 
			ErrSource, ErrLine, ErrMsg, Form, AppID)
		SELECT ISNULL(ERROR_NUMBER(),-1), ISNULL(ERROR_SEVERITY(),0), ISNULL(ERROR_STATE(),0), 
			ISNULL(ERROR_PROCEDURE(),'Unknown.'), ISNULL(ERROR_LINE(),0), 
			ISNULL(ERROR_MESSAGE(),'ROLLBACK: Failed to Insert ids.GLRptOutput Record.'), OBJECT_NAME(@@PROCID), 1;

	SELECT [ErrorNumber] = ISNULL(ERROR_NUMBER(),-1), 
			[ErrorMessage] = ISNULL(ERROR_MESSAGE(),'ROLLBACK: ids.GLRptOutput Record failed to Insert.'), 
			[ProcName] = OBJECT_NAME(@@PROCID), 
			[ErrorLine] = ISNULL(ERROR_LINE(),0), 
			[AppID] = 5,
			[GLRptOutputID] = @NewId,
			[GLRptID] = @GLrptID;
END CATCH

TysonLPrice · Feb 1, 2013

What do you mean by fails? Returns an "F"? In other words what is the error message?

BigBruceJ · Feb 1, 2013

The SP simply did not return anything. The Catch was not executed. Nothing. But I found the answer! TIMEOUT!
I had a time out of 3 minutes.... Changed it to 6 Minutes and voila! It works just fine.

gmmastros · Feb 1, 2013

Looking at this monster... I'm not surprised.

Are you interested in making this faster? Try adding FAST_FORWARD FORWARD_ONLY READ_ONLY to the declare cursor statement.

Near line 158:

Change:
DECLARE GLAcct Cursor for

To:

DECLARE GLAcct Cursor FAST_FORWARD FORWARD_ONLY READ_ONLY for

Please understand that I haven't worked with cursors in over 10 years. Overall, your best solution would be to re-write this so that you don't need any cursors or while loops. Given the size of this monster, this will not be easy to do. Anyway... you should play around a little with those 3 options I mentioned to see if it improves the performance. If it does, can you please post back the new execution time. I'm curious to know what sort of impact this may have on performance.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

BigBruceJ · Feb 1, 2013

Thanks George,

I made the changes, and overall it made about 45 second improvment. (that is good!)

As far as retooling this proc without Cursors, I welcome any Ideas! This Stored proc produces the extract for a major companies financial reports. When you understand the complexity of these reports, you will understand that for each account you have to do many different types of functions to load detail trans etc etc. Don't think there is a query that can do this. and it is much faster to have SQL do it in a Proc with cursors than volleying back and forth between VB2010 and SQL

FYI

TysonLPrice · Feb 1, 2013

The SP simply did not return anything. The Catch was not executed. Nothing. But I found the answer! TIMEOUT!
I had a time out of 3 minutes.... Changed it to 6 Minutes and voila! It works just fine."

Something must be missing from the error handling if it timed out and the application could not detect it. Maybe the SP is hiding it's tracks?

gmmastros · Feb 1, 2013

Bruce,

Believe me. I understand. That's why I mentioned it, but didn't push it too hard either. I did say, "Given the size of this monster...".

There are other cursor declarations. Line 386, 410, 443, 491, etc...

You could try the same trick with the other cursors in an attempt to speed up the process.

There are other things you can do to help speed things up. It's probably best to work from the inside out. Specifically, I am referring to the inner most cursors/loops. Inner loops are executed more often than outer loops, so making them faster will likely have a big impact on overall performance.

One thing I have noticed is that multiple set operations are slower than a single select that does the same thing. For example, lines 704 to 715 are...

[tt] SET @GLLevel7 = 'SWE-All Divisions';
SET @GLLevel8 =
CASE
WHEN Rtrim(@DivName) = 'Corporate' then 'SG&A'
ELSE @SubDivision
END;
SET @GLLevel9 =
CASE
WHEN @GLDivision = 0 then @SubDivision
ELSE @DIVName
END;
SET @GLLevel10 = @GLItemAcct8
[/tt]

Try replacing that with:

Code:

SELETCT @GLLevel7 = 'SWE-All Divisions',
        @GLLevel8 = CASE WHEN Rtrim(@DivName) = 'Corporate' then 'SG&A'
                         ELSE @SubDivision
                         END,
        @GLLevel9 =  CASE WHEN @GLDivision = 0 then @SubDivision
                          ELSE @DIVName
                     END,
        @GLLevel10 = @GLItemAcct8

Something small like this doesn't usually make much difference, but when it is executed repeatedly, it might. Also understand that this is just one example in your code. There are a lot of other places in the code that are similar to this.

And this... lines 773 to 822:

[tt]
IF @LOOP = 1 or @MGAC = '1'
BEGIN
SET @GLItemAmount1 = isnull(@GLTRNAmount,0)
SET @GLTRNAmount = 0
END
IF @LOOP = 2 or @MGAC = '2'
BEGIN
SET @GLItemAmount2 = isnull(@GLTRNAmount,0)
SET @GLTRNAmount = 0
END
IF @LOOP = 3 or @MGAC = '3'
BEGIN
SET @GLItemAmount3 = isnull(@GLTRNAmount,0)
SET @GLTRNAmount = 0
END
IF @LOOP = 4 or @MGAC = '4'
BEGIN
SET @GLItemAmount4 = isnull(@GLTRNAmount,0)
SET @GLTRNAmount = 0
END
IF @LOOP = 5 or @MGAC = '5'
BEGIN
SET @GLItemAmount5 = isnull(@GLTRNAmount,0)
SET @GLTRNAmount = 0
END
IF @LOOP = 6 or @MGAC = '6'
BEGIN
SET @GLItemAmount6 = isnull(@GLTRNAmount,0)
SET @GLTRNAmount = 0
END
IF @LOOP = 7 or @MGAC = '7'
BEGIN
SET @GLItemAmount7 = isnull(@GLTRNAmount,0)
SET @GLTRNAmount = 0
END
IF @LOOP = 8 or @MGAC = '8'
BEGIN
SET @GLItemAmount8 = isnull(@GLTRNAmount,0)
SET @GLTRNAmount = 0
END
IF @LOOP = 9 or @MGAC = '9'
BEGIN
SET @GLItemAmount9 = isnull(@GLTRNAmount,0)
SET @GLTRNAmount = 0
END
IF @LOOP = 10 or @MGAC = 'A'
BEGIN
SET @GLItemAmount10 = isnull(@GLTRNAmount,0)
SET @GLTRNAmount = 0
END
[/tt]

could be replaced with:

Code:

IF @LOOP = 1 or @MGAC = '1'
  BEGIN
    SET @GLItemAmount1 = isnull(@GLTRNAmount,0)
    SET @GLTRNAmount = 0
  END
[!]Else[/!] IF @LOOP = 2 or @MGAC = '2'
  BEGIN
    SET @GLItemAmount2 = isnull(@GLTRNAmount,0)
    SET @GLTRNAmount = 0
  END
[!]Else[/!] IF @LOOP = 3 or @MGAC = '3'
  BEGIN
    SET @GLItemAmount3 = isnull(@GLTRNAmount,0)
    SET @GLTRNAmount = 0
  END
[!]Else[/!] IF @LOOP = 4 or @MGAC = '4'
  BEGIN
    SET @GLItemAmount4 = isnull(@GLTRNAmount,0)
    SET @GLTRNAmount = 0
  END
[!]Else[/!] IF @LOOP = 5 or @MGAC = '5'
  BEGIN
    SET @GLItemAmount5 = isnull(@GLTRNAmount,0)
    SET @GLTRNAmount = 0
  END
[!]Else[/!] IF @LOOP = 6 or @MGAC = '6'
  BEGIN
    SET @GLItemAmount6 = isnull(@GLTRNAmount,0)
    SET @GLTRNAmount = 0
  END
[!]Else[/!] IF @LOOP = 7 or @MGAC = '7'
  BEGIN
    SET @GLItemAmount7 = isnull(@GLTRNAmount,0)
    SET @GLTRNAmount = 0
  END
[!]Else[/!] IF @LOOP = 8 or @MGAC = '8'
  BEGIN
    SET @GLItemAmount8 = isnull(@GLTRNAmount,0)
    SET @GLTRNAmount = 0
  END
[!]Else[/!] IF @LOOP = 9 or @MGAC = '9'
  BEGIN
    SET @GLItemAmount9 = isnull(@GLTRNAmount,0)
    SET @GLTRNAmount = 0
  END
[!]Else[/!] IF @LOOP = 10 or @MGAC = 'A'
  BEGIN
    SET @GLItemAmount10 = isnull(@GLTRNAmount,0)
    SET @GLTRNAmount = 0
  END

Arguably, you could say that I am splitting hairs here. Without the ELSE statement, SQL Server will continue to evaluate all of the expressions even after it has made the correct assignment. If @Loop = 1, your code would evaluate the first condition, make the assignment and then evaluate the rest of the conditions too. With my code, the first condition would evaluate, the assignment would be made, and then execution would jump to the bottom of the block so that the other conditions are not evaluated.

If this code were my responsibility, I would absolutely make these changes. You know... the simple stuff. I wouldn't be at all surprised if you could cut the execution time in 1/2 (or better) by making these simple changes.

Please understand that I am suggesting "simple" changes only. By simple, I mean, nothing that would change any of the business logic, which is no doubt very complicated.

Again... if you do implement all of these changes, I would be very interested to know what impact it has on overall execution time.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Nested Cursors SQL Stored Proc works standalone, but not when called from VB2010 1

BigBruceJ

Programmer

TysonLPrice

Programmer

BigBruceJ

Programmer

gmmastros

Programmer

BigBruceJ

Programmer

TysonLPrice

Programmer

gmmastros

Programmer

Similar threads

Part and Inventory Search

Sponsor