Click here to Skip to main content
15,889,216 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
The
timeout period elapsed prior to completion of the operation or the server is not responding.

my stored proc given below
ALTER  PROCEDURE [dbo].[Redeemvoucherverify]
(
     @amount as decimal 
    ,@mobileNO  Bigint   
    ,@SecurCode as int    
    ,@BillNO as varchar(10)
    ,@adminID as int 
    ,@Retvalue as int out 
) 
AS
BEGIN
set @Retvalue =0
declare @code int 
set @code=0 
select  @code = Securecode  from RedeemMaster Where Securecode =@SecurCode
if ( @code !=0 )
begin  
declare @amount1 int
declare @amount2 int
declare @a1 decimal,@a2 decimal,@a3 decimal ,@aa2 decimal,@aa3 decimal
set @mobileno=@mobileNO
set @BillNo=@BillNO
set @amount=@amount
set @SecurCode=@SecurCode
set @a1=@amount
set @aa2 =(select AllocatedAmount  from ConsumerMaster  where MobileNo =@mobileno)
While @aa2>=@a1@a1 and @a1!=0 
BEGIN
set @a2=(select top (1) UpdatedAmount from AllocateMaster  Where MobileNo=@mobileno and UpdatedAmount!=0 and   ExpireDate >=GETDATE() and Status=1 and AllocateFrom='C' and AllocateFromID=@adminID )
set @a3=(select AllocatedAmount  from ConsumerMaster  where MobileNo =@mobileno) 
if(@a1<@a2)
 begin
      Update RedeemMaster set Status=1,BillNo=@BillNO Where MobileNo=@mobileno and Securecode=@SecurCode
      insert into RedeemMaster (AllocatedID,Amount,MobileNo,BillNo,Securecode,Status) values ((select top (1) AllocateID from AllocateMaster  Where MobileNo=@mobileno and UpdatedAmount!=0 and   ExpireDate >=GETDATE() and Status=1 and AllocateFrom='C' and AllocateFromID=@adminID),@a1,@mobileno,@BillNo,@SecurCode,1)
      update RedeemMaster set CORF=(select CORF from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode),CORFID=(select CORFID from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode),Role=(select Role from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode),CreatedBy=(select CreatedBy from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode),CreatedDate=(select CreatedDate from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode) Where Amount=@a1
      Update top (1) AllocateMaster set UpdatedAmount=ISNULL(UpdatedAmount,0)-@a1,UtiltizedAmount=ISNULL(UtiltizedAmount,0)+@a1 Where MobileNo=@mobileno and UpdatedAmount!=0 and   ExpireDate >=GETDATE() and Status=1 and AllocateFromID=@adminID and AllocateFrom='C'                 
      Update ConsumerMaster set AllocatedAmount=ISNULL(AllocatedAmount,0)-@a1,RedeemedAmount =ISNULL(RedeemedAmount,0)+@a1 where MobileNo=@mobileno  and AllocatedAmount!=0
      set @a1=@a1-@a2
 end

if(@a1>=@a2)
  begin
      Update RedeemMaster set Status=1,BillNo=@BillNO Where MobileNo=@mobileno and Securecode=@SecurCode
       declare @a4 int
       set @a4=(select top (1) UpdatedAmount from AllocateMaster  Where MobileNo=@mobileno and UpdatedAmount!=0 and   ExpireDate >=GETDATE() and Status=1 and AllocateFrom='C' and AllocateFromID=@adminID)
       Update ConsumerMaster set AllocatedAmount=ISNULL(AllocatedAmount,0)-@a4,RedeemedAmount =ISNULL(RedeemedAmount,0)+@a4 where MobileNo=@mobileno  and AllocatedAmount!=0
       --Update RedeemMaster set Status=1,BillNo=@BillNO, AllocatedID =(select top(1)AllocateID from AllocateMaster Where MobileNo=@mobileno and Status=1 and ExpireDate>=GETDATE()and UpdatedAmount!=0 and Status=1) Where MobileNo=@mobileno and Securecode=@SecurCode
       insert into RedeemMaster (AllocatedID,Amount,MobileNo,BillNo,Securecode,Status) values ((select top (1) AllocateID from AllocateMaster  Where MobileNo=@mobileno and UpdatedAmount!=0 and   ExpireDate >=GETDATE() and Status=1),@a4,@mobileno,@BillNo,@SecurCode,1)
       update RedeemMaster set CORF=(select CORF from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode),CORFID=(select CORFID from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode),Role=(select Role from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode),CreatedBy=(select CreatedBy from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode),CreatedDate=(select CreatedDate from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode) Where Amount=@a4
       Update top (1) AllocateMaster set UpdatedAmount=ISNULL(UpdatedAmount,0)-@a4,UtiltizedAmount=ISNULL(UtiltizedAmount,0)+@a4 Where MobileNo=@mobileno and UpdatedAmount!=0 and   ExpireDate >=GETDATE() and Status=1 and  AllocateFromID=@adminID  and AllocateFrom='C'                 
       set @a1=@a1-@a2
  end         
IF @a1=0 or @a1<0
BREAK
ELSE
CONTINUE

END 

  
   set @RetValue= 1 
 end
      return @Retvalue
      
end





when we are executing the stored proc that time we are getting this error please any body know this answer send me
Posted
Updated 30-Oct-10 0:19am
v3
Comments
Mohd Wasif 29-Oct-10 8:18am    
Give your procedure
Rajesh Anuhya 29-Oct-10 9:10am    
can u provide me more details..,
Henry Minute 29-Oct-10 11:47am    
Unless you provide more details, it will be very difficult for anyone to help you. Is it a C#, VB.Net, C++ MFC or managed application? What does the stored proc look like? and so on.

please post your stored proc. so that we can view it and analyze it. this will help me and all other users.
 
Share this answer
 
If your query is taking a long then it must be time out error.

Try by increasing timeout of query exection.

You could have THIS[^] and THIS[^] Thread to be useful.

Please vote and Accept Answer if it Helped.
 
Share this answer
 
SQL
ALTER  PROCEDURE [dbo].[Redeemvoucherverify]
(
     @amount as decimal 
    ,@mobileNO  Bigint   
    ,@SecurCode as int    
    ,@BillNO as varchar(10)
    ,@adminID as int 
    ,@Retvalue as int out 
) 
AS
BEGIN

---Mychanges 
Create Table #tbl_RedeemMaster (CORF NVARCHAR(255,CORFID NVARCHAR(255),Role NVARCHAR(255),CreatedBy NVARCHAR(255),CreatedDate DATETIME)
----------

set @Retvalue =0
declare @code int 
set @code=0 
select  @code = Securecode  from RedeemMaster Where Securecode =@SecurCode
if ( @code !=0 )
begin  
declare @amount1 int
declare @amount2 int
declare @a1 decimal,@a2 decimal,@a3 decimal ,@aa2 decimal,@aa3 decimal
set @mobileno=@mobileNO
set @BillNo=@BillNO
set @amount=@amount
set @SecurCode=@SecurCode
set @a1=@amount
set @aa2 =(select AllocatedAmount  from ConsumerMaster  where MobileNo =@mobileno)
While @aa2>=@a1@a1 and @a1!=0 
BEGIN
set @a2=(select top (1) UpdatedAmount from AllocateMaster  Where MobileNo=@mobileno and UpdatedAmount!=0 and   ExpireDate >=GETDATE() and Status=1 and AllocateFrom='C' and AllocateFromID=@adminID )
set @a3=(select AllocatedAmount  from ConsumerMaster  where MobileNo =@mobileno) 
if(@a1<@a2)
 begin
      Update RedeemMaster set Status=1,BillNo=@BillNO Where MobileNo=@mobileno and Securecode=@SecurCode
      insert into RedeemMaster (AllocatedID,Amount,MobileNo,BillNo,Securecode,Status) values ((select top (1) AllocateID from AllocateMaster  Where MobileNo=@mobileno and UpdatedAmount!=0 and   ExpireDate >=GETDATE() and Status=1 and AllocateFrom='C' and AllocateFromID=@adminID),@a1,@mobileno,@BillNo,@SecurCode,1)
      
      ---Mychanges 
      Insert Into #tbl_RedeemMaster 
      (select CORF,CORFID,Role,CreatedBy,CreatedDate from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode)
      
      update RedeemMaster set CORF=tb.CORF,CORFID=,Role=tb.CORFID,CreatedBy=tb.CreatedBy,CreatedDate=tb.CreatedDate
      From #tbl_RedeemMaster tb
      Where Amount=@a1
      ---------------------
      
      Update top (1) AllocateMaster set UpdatedAmount=ISNULL(UpdatedAmount,0)-@a1,UtiltizedAmount=ISNULL(UtiltizedAmount,0)+@a1 Where MobileNo=@mobileno and UpdatedAmount!=0 and   ExpireDate >=GETDATE() and Status=1 and AllocateFromID=@adminID and AllocateFrom='C'                 
      Update ConsumerMaster set AllocatedAmount=ISNULL(AllocatedAmount,0)-@a1,RedeemedAmount =ISNULL(RedeemedAmount,0)+@a1 where MobileNo=@mobileno  and AllocatedAmount!=0
      set @a1=@a1-@a2
 end

if(@a1>=@a2)
  begin
      Update RedeemMaster set Status=1,BillNo=@BillNO Where MobileNo=@mobileno and Securecode=@SecurCode
       declare @a4 int
       set @a4=(select top (1) UpdatedAmount from AllocateMaster  Where MobileNo=@mobileno and UpdatedAmount!=0 and   ExpireDate >=GETDATE() and Status=1 and AllocateFrom='C' and AllocateFromID=@adminID)
       Update ConsumerMaster set AllocatedAmount=ISNULL(AllocatedAmount,0)-@a4,RedeemedAmount =ISNULL(RedeemedAmount,0)+@a4 where MobileNo=@mobileno  and AllocatedAmount!=0
       --Update RedeemMaster set Status=1,BillNo=@BillNO, AllocatedID =(select top(1)AllocateID from AllocateMaster Where MobileNo=@mobileno and Status=1 and ExpireDate>=GETDATE()and UpdatedAmount!=0 and Status=1) Where MobileNo=@mobileno and Securecode=@SecurCode
       insert into RedeemMaster (AllocatedID,Amount,MobileNo,BillNo,Securecode,Status) values ((select top (1) AllocateID from AllocateMaster  Where MobileNo=@mobileno and UpdatedAmount!=0 and   ExpireDate >=GETDATE() and Status=1),@a4,@mobileno,@BillNo,@SecurCode,1)
       update RedeemMaster set CORF=(select CORF from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode),CORFID=(select CORFID from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode),Role=(select Role from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode),CreatedBy=(select CreatedBy from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode),CreatedDate=(select CreatedDate from RedeemMaster Where RedeemedAmount=@amount and Securecode=@SecurCode) Where Amount=@a4
       Update top (1) AllocateMaster set UpdatedAmount=ISNULL(UpdatedAmount,0)-@a4,UtiltizedAmount=ISNULL(UtiltizedAmount,0)+@a4 Where MobileNo=@mobileno and UpdatedAmount!=0 and   ExpireDate >=GETDATE() and Status=1 and  AllocateFromID=@adminID  and AllocateFrom='C'                 
       set @a1=@a1-@a2
  end         
IF @a1=0 or @a1<0
BREAK
ELSE
CONTINUE

END 

  
   set @RetValue= 1 
 end
      return @Retvalue
      
end


Look down to the ---Mychanges
 
Share this answer
 
Comments
RDBurmon 1-Nov-10 1:50am    
srinualluri please confirm about the datatype i use while creating the temp variable

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