SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Procedure Returns Twice Result sometimes.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ravininave
Yak Posting Veteran

India
99 Posts

Posted - 07/09/2013 :  14:49:54  Show Profile  Reply with Quote
In my app, I receive payment withdrawal request in ForRec Table and I've the list of Vendors who will give them a Payment. They are in ForSend Table.

ForRec Table Structure: (Whose request I receive for Withdrawal)
SNo,CommNo,AppMstID,RecDate,Tag,Amount,Paid

ForSend Table Structure :(Who will Pay the Amount)
SNo, AppMstID,SendDate,Amount,Paid,Tag

Now, when I got request I runs a procedure which finds record from ForRec Table suppose I find AppMstID = 20 (applicant no.) which has to withdraw Rs. 50000 then It goes to ForSend Table and find the data from top and issue the payment.
Suppose in ForSend Table AppMstID 1 has assign Rs. 35000 and AppMstID 2 has assign Rs. 40000 to issue then in this case we have to send request of 35000 to ID 1 and Rs. 15000 to ID 2.

for this I'd written a procedure like :




DECLARE @Percent DECIMAL(18,2);SET @Percent = 10;
DECLARE @RowNo INT,@CurrCode INT, @RecCode INT, @MaxSNo INT, @CurrLevel INT, @BalAmt INT, @SendBal INT, @GiveAmt INT,@SNo INT,@RSNo INT, @AchiverTag INT
SET @RowNo = 1;
DECLARE @DateTable AS TABLE (RowNo INT IDENTITY(1,1),mSNo INT,AppCode INT, SendBal INT, AchiverTag INT)
INSERT INTO @DateTable
SELECT  SNo , ForRec.AppMstID, (COALESCE(Amount,0) - COALESCE(Paid,0)), COALESCE(ForRec.IsAchiverTag,0) FROM ForRec INNER JOIN AppMast ON ForRec.AppMstID = AppMast.AppMstID 
WHERE COALESCE(ForRec.IsAchiverTag,0) = 0  AND 
RecDate <= DATEADD(MINUTE,330,GETUTCDATE()) AND (COALESCE(Amount,0) - COALESCE(Paid,0)) > 0 AND Tag = 0 AND AppMast.AppMstActivate = 1 ORDER BY SNo; -- AND (((COALESCE(Amount,0) - COALESCE(Paid,0)) % 500) = 0) 

SELECT * FROM @DateTable
WHILE @RowNo < = ( SELECT COUNT(RowNo) FROM @DateTable)
BEGIN		
	PRINT @RowNo;
	SET @SendBal = 0; SET @AchiverTag = 0;
	SELECT @RSNo = mSNo , @CurrCode= AppCode, @SendBal = COALESCE(SendBal,0), @AchiverTag = AchiverTag FROM @DateTable WHERE RowNo = @RowNo;	
	WHILE @SendBal > 0
	BEGIN		
		SET @RecCode = 0; SET @BalAmt = 0;
		/******* START OF FOR ACHIVERS *********/
		IF @AchiverTag = 1
		BEGIN
			SELECT Top 1 @SNo = ForSend.Sno, @RecCode = ForSend.AppMstID, @CurrLevel = ForSend.uLevel, @BalAmt = CASE WHEN CAST(COALESCE(ForSend.Amount,0)AS DECIMAL(18,2)) * (@Percent / CAST(100 AS DECIMAL(18,2)) ) < 500 THEN COALESCE(500,0) - COALESCE(ForSend.Paid,0) ELSE CAST(CAST(COALESCE(ForSend.Amount,0)AS DECIMAL(18,2)) * (@Percent / CAST(100 AS DECIMAL(18,2))) AS INT) - COALESCE(ForSend.Paid,0)END FROM ForSend  INNER JOIN AppMast ON  ForSend.AppMstID = AppMast.AppMstID   WHERE ForSend.Tag = 0 AND AppMast.AppMstActivate =  1 AND (@CurrCode <> ForSend.AppMstID) AND ((CAST(COALESCE(ForSend.Paid,0) AS DECIMAL(18,2))/ CAST(COALESCE(ForSend.Amount,0)AS DECIMAL(18,2)))* CAST(100 AS DECIMAL(18,2)) < CAST(10 AS DECIMAL(18,2)) OR CAST(COALESCE(ForSend.Paid,0) AS DECIMAL(18,2)) < CAST(500 AS DECIMAL(18,2))) 
			AND(CASE WHEN CAST(COALESCE(ForSend.Amount,0)AS DECIMAL(18,2)) * (CAST(10 AS DECIMAL(18,2))/ CAST(100 AS DECIMAL(18,2))) < CAST(500 AS DECIMAL(18,2)) THEN COALESCE(500,0) - COALESCE(ForSend.Paid,0) ELSE CAST(CAST(COALESCE(ForSend.Amount,0)AS DECIMAL(18,2)) * (CAST(10 AS DECIMAL(18,2))/ CAST(100 AS DECIMAL(18,2))) AS INT) - COALESCE(ForSend.Paid,0)END) > 0
			--AND(CASE WHEN CAST(COALESCE(ForSend.Amount,0)AS DECIMAL(18,2)) * (CAST(10 AS DECIMAL(18,2))/ CAST(100 AS DECIMAL(18,2))) < CAST(500 AS DECIMAL(18,2)) THEN COALESCE(500,0) - COALESCE(ForSend.Paid,0) ELSE CAST(CAST(COALESCE(ForSend.Amount,0)AS DECIMAL(18,2)) * (CAST(10 AS DECIMAL(18,2))/ CAST(100 AS DECIMAL(18,2))) AS INT) - COALESCE(ForSend.Paid,0)END) % 500 = 0
			ORDER BY ForSend.SendDate; --ForSend.SendDate  <= DATEADD(MINUTE,330,GETUTCDATE()) AND
		END
		/******* END OF FOR ACHIVERS *********/
		/****** START OF FOR ALL USERS *********/
		ELSE
		BEGIN
	 		SELECT TOP 1 @SNo = ForSend.Sno, @RecCode = ForSend.AppMstID, @CurrLevel = ForSend.uLevel, @BalAmt = (COALESCE(ForSend.Amount,0) - COALESCE(ForSend.Paid,0))  FROM ForSend  INNER JOIN AppMast ON  ForSend.AppMstID = AppMast.AppMstID   WHERE ForSend.SendDate  <= DATEADD(MINUTE,330,GETUTCDATE()) and ForSend.Tag = 0 AND (COALESCE(ForSend.Amount,0) - COALESCE(ForSend.Paid,0)) > 0 AND AppMast.AppMstActivate =  1 AND (@CurrCode <> ForSend.AppMstID) ORDER BY ForSend.SNO;--AND (COALESCE(ForSend.Amount,0) - COALESCE(ForSend.Paid,0))% 500 = 0
		END
		/****** END OF FOR ALL USERS *********/
		IF COALESCE(@RecCode ,0) > 0 AND @SendBal > 0 AND @BalAmt > 0
		BEGIN		
			SET @GiveAmt = 0	
			SET @GiveAmt = CASE WHEN @BalAmt <= @SendBal THEN @BalAmt ELSE @SendBal END;						
			Select @MaxSNo = MAX(COALESCE(SNo,0)) + 1 FROM RequestMast;
			
			INSERT INTO RequestMast (SNo,AppSNo, AppMstID,RecID,FromTime,ToTime,ApproveTag,uLevel,Amount, SendSNo )VALUES(@MaxSNo,@RSNo , @CurrCode,@RecCode, DATEADD(MINUTE,330,GETUTCDATE()),DATEADD(MINUTE,4650,GETUTCDATE()),0,@CurrLevel, @GiveAmt, @SNo);
			 					
			UPDATE ForRec SET paid  = COALESCE(paid,0) + @GiveAmt  WHERE AppMstID = @CurrCode  AND Tag = 0 AND SNo = @RSNo;					 			
			UPDATE ForRec SET Tag = 1 WHERE Amount = Paid AND SNo = @RSNo;
			
			UPDATE ForSend SET Paid = COALESCE(Paid,0) + @GiveAmt WHERE AppMstID = @RecCode  AND SNo = @SNo AND Tag = 0;
			UPDATE ForSend SET Tag = 1 WHERE Amount  = Paid AND SNo = @SNo;  
			SET @SendBal = @SendBal - @GiveAmt;
			IF @SendBal <= 0
			BEGIN
				BREAK;
			END
		END
		ELSE
		BEGIN		
			BREAK;
		END		
	END
	SET @RowNo = @RowNo + 1
END





Sometimes, this works fine but sometimes it issues the amount twice or thrice.
Suppose ID 1 has a limit of Rs. 35,000 to issue then it issues 70,000 from his own a/c

I'm not able to find any mistake in above code.
Can anyone suggest me the problem.?

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/09/2013 :  16:37:17  Show Profile  Reply with Quote
Not having the ability to run and look at your code and with the code being somewhat lengthy, it is hard for me to figure out why you are getting duplicates. The only thing I can suggest is to put print statements in your code to see under what conditions it repeats loops that it should not. If this is being used by multiple users simultaneously, that also can cause the type of issues that you are seeing.

If you can post sample data and table DDLs in a consumable format (i.e., something that a person can copy and paste to their SSMS and run), you might get more concrete and more prompt responses. See here if you need help in generating DDL's and sample data: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

ravininave
Yak Posting Veteran

India
99 Posts

Posted - 07/09/2013 :  16:58:25  Show Profile  Reply with Quote
Okay James K, I will post sample data and DDLs. Meanwhile tell me what should we do if there are multiple users. This procedure executes multiple times whenever anyone puts the request for withdrawal.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 07/09/2013 :  17:02:15  Show Profile  Reply with Quote
quote:
Originally posted by ravininave

Okay James K, I will post sample data and DDLs. Meanwhile tell me what should we do if there are multiple users. This procedure executes multiple times whenever anyone puts the request for withdrawal.

Does the procedure execute multiple times, or does the procedure execute once, but the amount deducted is incorrect?

If the procedure is getting executed multiple times, you have to looka the calling program to see if they are doing something incorrect that causes the multiple invocations.
Go to Top of Page

ravininave
Yak Posting Veteran

India
99 Posts

Posted - 07/09/2013 :  17:13:12  Show Profile  Reply with Quote
Employees who will Send Amount:
CREATE TABLE [dbo].[ForSend](
[SNo] [int] NULL,
[AppMstID] [int] NULL,
[SendDate] [datetime] NULL,
[Amount] [int] NULL,
[Paid] [int] NULL,
[Tag] [int] NULL,
[uLevel] [int] NULL,
[CancTag] [int] NULL,
[BlockTag] [int] NULL,
[CommNo] [int] NULL
) ON [PRIMARY]

Insert into ForSend (SNo , AppMstID , SendDate , Amount , Paid,Tag) values (1,45,'2013-06-08 20:52:24.793',2000,0,0)
Insert into ForSend (SNo , AppMstID , SendDate , Amount , Paid,Tag) values (2,46,'2013-06-08 20:52:24.793',10000,0,0)
Insert into ForSend (SNo , AppMstID , SendDate , Amount , Paid,Tag) values (3,47,'2013-06-08 20:52:24.793',3000,0,0)

--Request For Withdrawal amoount

CREATE TABLE [dbo].[ForRec](
[SNo] [int] NULL,
[CommNo] [int] NULL,
[AppMstID] [int] NULL,
[RecDate] [datetime] NULL,
[Tag] [int] NULL,
[Amount] [int] NULL,
[Paid] [int] NULL,
[Qty] [int] NULL,
[uLevel] [int] NULL,
[IsAchiverTag] [int] NULL
) ON [PRIMARY]


Insert into ForRec (SNo , AppMstID , RecDate ,Tag, Amount , Paid) values (1,20,'2013-06-08 20:52:24.793',0,6000,0)
Insert into ForRec (SNo , AppMstID , RecDate ,Tag, Amount , Paid) values (1,88,'2013-06-08 20:52:24.793',0,8000,0)

--Table where I've to put all the Result.
CREATE TABLE [dbo].[RequestMast](
[SNo] [int] NULL,
[CommNo] [int] NULL,
[AppSNo] [int] NULL,
[AppMstID] [int] NULL,
[RecID] [int] NULL,
[FromTime] [datetime] NULL,
[ToTime] [datetime] NULL,
[Amount] [int] NULL,
[ApproveTag] [int] NULL,
[AcceptDate] [datetime] NULL,
[uLevel] [int] NULL,
[TimeExtend] [int] NULL,
[BlockTag] [int] NULL,
[SendSNo] [int] NULL,
[RejectDate] [datetime] NULL
) ON [PRIMARY]

So, In this case

ID No. 20 had sent a request to withdraw 6000 so system will withdraw 2000 from ID 45 and remaining 4000 from id 46.
ID No. 88 had send a request of 8000 so system will withdraw remining 6000 from id 46 and 2000 from id 47.

It will put data in Requestmast table.
Go to Top of Page

ravininave
Yak Posting Veteran

India
99 Posts

Posted - 07/09/2013 :  17:14:39  Show Profile  Reply with Quote
Whenever, there is the withdrawal request, this procedure executes. Sometimes multiple persons executes the with request. Sometimes, it generates perfectly but sometimes it generates twice or thrice.

Edited by - ravininave on 07/09/2013 17:15:52
Go to Top of Page

ravininave
Yak Posting Veteran

India
99 Posts

Posted - 07/09/2013 :  18:22:18  Show Profile  Reply with Quote
Hey one more thing, whenever I run the procedure it took so much time and then I came to know that for some reason I've to add Index and after that my proc is executing fastly. So, can this be the reason to execute it twice or thrice.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000