被调用的存储过程运行并更新表2次而不是一次 [英] Called stored procedure runs and update table 2 times instead of once
问题描述
调用存储过程
--------------------------
IF @ ZEQUALPAY ='SIMPLE'
BEGIN
WHILE @ STARTPERIOD< = @ENDPERIOD
BEGIN
SET @CMONTH = @ZPERIOD
SET @CYEAR = @ZYEAR
SET @STARTPERIOD = CONVERT（NVARCHAR，@ ZCCYR）+右（'00'+转换（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）+右（'00'+转换（varchar（10），@ ZCCMTH），2）
结束
结束
被称为存储过程
------------------------
-------------- ----------
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
------------------------
------------------------
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
问题
--------
@RESULTS
1.是
返回值
1. 0
该程序提前运行，因此计算
SET @ACCNT = @ ACCNT + 1
关于三时间在达到以下条件之前，
从而使条件无效。
< b> SET @ACCNT 在表格 0 之前，如果它到达
条件，它正在读取 3 。
IF @ AMORAT_MTHS = 0 AND @ AMORATORIUM = 0 AND @ ACCNT< = @ ATERM_START
如何处理@RETURN，以便程序流程有序。
谢谢
我尝试过：
没有例子。这是一个特殊的问题
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
推荐答案
WHILE @STARTPERIOD<=@ENDPERIOD
运行两次
running two times
这篇关于被调用的存储过程运行并更新表2次而不是一次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
