Click here to Skip to main content
15,894,405 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Calling Stored procedure
--------------------------

IF @ZEQUALPAY='SIMPLE'
BEGIN

WHILE @STARTPERIOD<=@ENDPERIOD
BEGIN

SET @CMONTH = @ZPERIOD
SET @CYEAR = @ZYEAR

SET @STARTPERIOD = CONVERT(NVARCHAR,@ZCCYR)+ Right('00' + Convert(varchar(10),@ZCCMTH), 2)

EXEC [dbo].[sp_simple] @ZIDNO,@ZLOANUMBER,@CMONTH,@CYEAR,@RETURN

IF @ZPERIOD = 12
BEGIN
SET @ZCCMTH = 1
SET @ZCCYR = @ZYEAR+1
END
ELSE
IF @ZPERIOD <> 12
BEGIN
SET @ZCCMTH =@ZPERIOD+1
SET @ZCCYR =@ZYEAR
END

SET @ZPERIOD = @ZCCMTH
SET @ZYEAR = @ZCCYR

SET @STARTPERIOD = CONVERT(NVARCHAR,@ZCCYR)+ Right('00' + Convert(varchar(10),@ZCCMTH), 2)


END
END














Called Stored Procedure
------------------------
------------------------
SQL
ALTER PROCEDURE [dbo].[sp_simple]
	(
	@ZIDNO          Varchar(20),
	@ZLOANUMBER     Varchar(20),
	@CMONTH         Int,
	@CYEAR          Int,
	@RETURN		   varchar(4) OUTPUT     
	)
	AS

	BEGIN

	DECLARE @AIDNO       varchar(20),
	@ALOANTYPE   varchar(20),
	@ADEDUCT     money,
	@ATERM_START DECIMAL(22,10),
	@ACCNT       smallint ,
	@BINT_NO     varchar(20),
	@BINT_NAME   varchar(50),
	@BRATES     DECIMAL(22,10)
  
	SELECT @AIDNO    = A.IDNO        ,
	@ALOANTYPE      =  A.LOANTYPE   ,
	@ADEDUCT        =  A.DEDUCT     ,
	@AOUTSTAND      =  A.OUTSTAND   ,
	@ACOMPCOUNT     =  A.COMPCOUNT  ,
	@AINT_NO        =  A.INT_NO     ,
	@ACCNT          =  A.CCNT       ,
	@ASACCRUED      =  A.SACCRUED ,
	@ANUMDAYS       =  A.NUMDAYS  ,
	@BINT_NO         =  B.INT_NO   ,
	@BINT_NAME       =  B.INT_NAME   ,
	@BRATES          =  B.RATES
	FROM LOANS A JOIN INTEREST B
	ON A.INT_NO = B.INT_NO WHERE  REPAY_MODE='SIMPLE INTEREST' AND COMPCOUNT>=1
	AND A.IDNO=@ZIDNO AND A.LOANUMBER=@ZLOANUMBER
  
		 
	SET @ACCNT = @ACCNT+1	
		
		
		IF @APAY_TYPE  ='MONTHLY'
		BEGIN		
		
		IF @AMORAT_MTHS=0 AND @AMORATORIUM=0 AND @ACCNT <=@ATERM_START


Problem
--------
@RESULTS

1. YES


Return Value

1. 0

The program runs ahead hence the calculation of

SET @ACCNT = @ACCNT+1

is done about three times before the condition below is reached,
thereby defeating the condition.


SET @ACCNT is in 0 from the table how ever before it gets to where the
condition for it to be tested it is reading 3.



IF @AMORAT_MTHS=0 AND @AMORATORIUM=0 AND @ACCNT <=@ATERM_START


How do I handle the @RETURN so that the flow of the program will be orderly.


Thanks

What I have tried:

There are no examples. This is a peculiar problem
Posted
Updated 20-Feb-17 20:57pm
v10
Comments
Richard Deeming 20-Feb-17 12:32pm    
WHILE @STARTPERIOD <= @ENDPERIOD
BEGIN 
    SET @CMONTH = @ZPERIOD      
    SET @CYEAR  = @ZYEAR  
    EXEC [dbo].[sp_simple] @ZIDNO, @ZLOANUMBER, @CMONTH, @CYEAR, @RETURN
END


Since you never update either @STARTPERIOD or @ENDPERIOD within that loop, how are you expecting it to stop?
Member 12770648 20-Feb-17 14:32pm    
Its been updated I left it out

1 solution

WHILE @STARTPERIOD<=@ENDPERIOD


running two times
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900