TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
kowsi
NA
4
1.1k
while loop is not working in sql server 2012
Jul 27 2017 2:12 AM
DECLARE @lnCnt INT,
@inc INT=1,
@rcptcnt INT,
@rcptinc INT=1,
@Lnno VARCHAR(25)='',
@RcptNo VARCHAR(30)='',
@CllnAmt NUMERIC(18, 2)= 0,
@Effdt DATETIME='1900-01-01',
@InstAmt NUMERIC(18, 2),
@balance NUMERIC(18, 2)=0,
@InstlmtDueDt DATETIME,
@process CHAR(1)
SELECT @rcptcnt = Count(LnRcptNo)
FROM #Instcoll
WHERE Isnull(process, '') <> 'Y'
WHILE @rcptinc <= @rcptcnt
BEGIN
SELECT @balance = TranTypeAmt FROM #Instcoll WHERE pk_id = @inc AND process <> 'Y'
IF @balance > 0
SELECT @Effdt = Min(effdt) FROM #Instcoll WHERE Isnull(process, '') <> 'Y'
SELECT @CllnAmt = TranTypeAmt,@RcptNo = LnRcptNo,@lnno = lnno,@balance = TranTypeAmt
FROM #Instcoll WHERE Isnull(process, '') <> 'Y' AND effdt = @Effdt
SELECT @InstlmtDueDt = Min(InstlmtDueDt) FROM #custDue WHERE process <> 'Y'
SELECT @InstAmt = TotInstlmtAmt,@LnNo = LnNo
FROM #custDue WHERE process <> 'Y'
AND InstlmtDueDt = @InstlmtDueDt
UPDATE #Instcoll SET balance = @balance - @instamt, process = 'Y'
FROM #Instcoll WHERE LnRcptNo = @RcptNo AND effdt = @effdt
UPDATE #custDue SET process = 'Y', status = 'U',CollRcpt = @RcptNo,CollDt = @effdt,collamt = @balance
WHERE LnNo = @LnNo AND InstlmtDueDt = @InstlmtDueDt
SET @balance=@balance - @instamt
if @balance=0
break;
select @balance
WHILE @balance > 0
BEGIN
SELECT @balance, @instamt, * FROM #Instcoll
WHERE pk_id = @inc --and process <> 'Y'
SET @balance =@balance - @instamt
SELECT @balance, @effdt, @InstlmtDueDt, @RcptNo
END
SET @rcptinc=@rcptinc + 1
END
Attachment:
26-Jul_script.rar
Reply
Answers (
1
)
How to Perform Multiple Task in T-SQL
Insert result set of sp into a temp table