我在sqlserver存储的poc中遇到错误 [英] i getting error in sqlserver stored poc

问题描述

操作完成之前已过超时时间，或者服务器没有响应.

我在下面给出的存储过程

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

当我们在那个时间执行存储的proc时，遇到此错误，请任何机构知道此答案发送给我

when we are executing the stored proc that time we are getting this error please any body know this answer send me

推荐答案

请发布您的存储的proc.这样我们就可以对其进行查看和分析.这将帮助我和所有其他用户.

please post your stored proc. so that we can view it and analyze it. this will help me and all other users.

如果您的查询花费了很长时间，那么肯定是超时错误.

通过增加查询执行超时来尝试.

您可能有 THIS [此 [

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.

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

向下看--- Mychanges

Look down to the ---Mychanges

这篇关于我在sqlserver存储的poc中遇到错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！