请更正商店程序例程

问题描述

1.  @ZBACK is the number of times the routine will run

2.  @ZIDNO and   @ZLOANUMBER   is the particular record which is      

    selected from the 2 linked tables a. LOANS and b. INTEREST


Request
______


Can you please provide a better way to perform the undernoted routine

ALTER Procedure [dbo].[sp_Construction]
(
		@ZIDNO           VarChar(50),
                @ZAPPRS_NO       VarChar(50),
                @ZLOANUMBER      VarChar(50),
                @ZBACK           SMALLINT        , 
                @RETURN          VARCHAR(4)OUTPUT
 )
 
 

	SET @ZCCNT =1
	
	
	WHILE @ZCCNT <= @ZBACK 
				
	BEGIN


	CREATE TABLE #VOANS
	(
	AIDNO       varchar(20),
	ACOMPCOUNT  smallint ,
	BINT_NO     varchar(20),
	BINT_NAME   varchar(50),
	BRATES      numeric(18,4)
)



	INSERT INTO #VOANS
	SELECT A.IDNO     AS AIDNO         ,
	A.COMPCOUNT     AS  ACOMPCOUNT  ,
	B.INT_NO        AS  BINT_NO     ,
	B.INT_NAME      AS  BINT_NAME   ,
	B.RATES         AS  BRATES
	FROM LOANS A JOIN INTEREST B
	ON A.INT_NO = B.INT_NO WHERE  B.INT_NO=@ZINT_NAME AND A.IDNO =@ZIDNO AND A.LOANUMBER= @ZLOANUMBER


	DECLARE NNLOANS CURSOR FOR SELECT  AIDNO  ,
				    ACOMPCOUNT  ,
				   BINT_NO     ,
				   BINT_NAME   ,
				   BRATES FROM #VOANS

	        
	OPEN NNLOANS

	FETCH NEXT FROM NNLOANS  INTO    @AIDNO     ,
				   @ACOMPCOUNT   ,
				   @BINT_NO     ,
				   @BINT_NAME  ,
				   @BRATES         
				
									   
	WHILE @@FETCH_STATUS = 0
	BEGIN	

			 UPDATE LOANS  SET 
					OUTSTAND  =       @AADVLOAN-@MMPRINC, 
					TOTPAYMT  =       @MMDEDUCT 
					 WHERE IDNO =@AIDNO AND LOANUMBER=@ALOANUMBER
		
	
	END
	
		
	SET @ZCCNT =@ZCCNT+1
			
	END
	
				
	CLOSE NNLOANS
		
	DEALLOCATE NNLOANS

推荐答案

这篇关于请更正商店程序例程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！