在sql server中使用temprorary表 [英] working with temprorary tables in sql server

问题描述

我有一个存储过程.....



已成功执行。



in这个我使用temrary表的概念....



但是当我在网页上工作时它不起作用..它给出错误...



请告诉我们这个问题...任何帮助请...

i have a stored procedure .....

which executed successfully .

in this i use the concept of temrary table ....

but it don't working when i work through web page ..it gives error ...

pls tell whats the problem in it ...any help pls ...

alter PROCEDURE [dbo].[sp_InsertIntoTempPaymentnew]      
(      
@Center_Id int,      
@Data_Inserted_By int,      
@Company_U_Id int      
)      
AS      
BEGIN      
   
   
   SELECT     ClientLoanSubscriptionInfo.Center_Id,ClientLoanSubscriptionInfo.Proposal_ID, ClientLoanSubscriptionInfo.SubGroup_Id, CenterInfo.AssignedStaff, ClientLoanSubscriptionInfo.Client_Id,       
                      subloan_product.installment_amount,       
                      SUM(subloan_product.installment_amount + ClientLoanSubscriptionInfo.Principle_Arrear + ClientLoanSubscriptionInfo.Interest_Arrear - ClientLoanSubscriptionInfo.Advance) as TodaysDueAmt      
                       , DayOpenDayCloseInfo.CenterCollectionDate,ClientLoanSubscriptionInfo.Company_U_Id,ClientLoanSubscriptionInfo.AdvancePeriod  
                       
                   into #MYTEMPTABLE
FROM         ClientLoanSubscriptionInfo INNER JOIN      
                      subloan_product ON ClientLoanSubscriptionInfo.SubProduct_id = subloan_product.Subloan_Product_Id INNER JOIN      
                      CenterInfo ON ClientLoanSubscriptionInfo.Center_Id = CenterInfo.Center_Id INNER JOIN      
                      DayOpenDayCloseInfo ON CenterInfo.Center_Id = DayOpenDayCloseInfo.Center_Id      
WHERE     (ClientLoanSubscriptionInfo.Center_Id = @Center_Id) AND (ClientLoanSubscriptionInfo.Loan_Status = 1) AND (DayOpenDayCloseInfo.CollectionByLoanOfficerStatus = 0) AND       
                      (DayOpenDayCloseInfo.DayCloseByAdminStatus = 0)      
GROUP BY ClientLoanSubscriptionInfo.Center_Id, ClientLoanSubscriptionInfo.SubGroup_Id, ClientLoanSubscriptionInfo.Client_Id, subloan_product.installment_amount,       
                      CenterInfo.AssignedStaff, DayOpenDayCloseInfo.CenterCollectionDate,ClientLoanSubscriptionInfo.Company_U_Id ,ClientLoanSubscriptionInfo.Proposal_ID ,ClientLoanSubscriptionInfo.AdvancePeriod     


 
 update #MYTEMPTABLE set TodaysDueAmt=0 where AdvancePeriod>0 


   
      
      
      
INSERT INTO [dbo].[TempPaymentShceduleInfo]      
           ([Center_Id]     
           ,[Proposal_ID]    
           ,[SubgroupId]      
           ,[Staff_Id]      
           ,[Client_Id]      
           ,[DefaultInstallmentAmt]      
           ,[TodaysDueAmt],[DayOpen],[Company_U_Id]      
      )      
      
      
      select [Center_Id],[Proposal_ID],[SubgroupId] , [Staff_Id],[Client_Id],[DefaultInstallmentAmt],
[TodaysDueAmt],[DayOpen],[Company_U_Id] from  #MYTEMPTABLE
      

      
      
      
UPDATE [dbo].[TempPaymentShceduleInfo]      
   SET       
      [PaymentAmtCollected] = 0      
      ,[LoanOfficerPaymentStatus] =0      
      ,[DayCloseByAdminStatus] = 0      
      ,[IsMobileData] =0      
      ,[Data_Inserted_By] = 1      
      ,[Date_Of_Insertion] = getdate()      
      ,[IsActive] = 1,CountUpdate=0      
 WHERE IsActive is Null and Center_Id=@Center_Id and Company_U_Id=@Company_U_Id      
END

推荐答案

我可以看到一个可能的错误是临时表不会被删除。最好使用表变量。

One possible error I can see is the temp table is not dropped. It is preferable to use table variable.

你应该使用DROP TABLE语句删除临时表。



&对于临时表你应该使用#tblname



在插入值之前删除表格或立即执行SP。

You should drop the temp table using DROP TABLE statement.

& for temp table you should use #tblname

Drop the table before insert values or immediately execution of SP.

ok thnx to all you you ....我发现我的错误实际上是在我写的临时表的最后一列中，其中一些是错误的...因为它在后端成功执行但是根据程序给出错误而没有响应... .thnx to all

ok thnx to all of you ....i find out my errror actually in the last the column of temp tables that i write , some of them are wrongs ..so due to which it executed successfully on backend but give error and no response according to procedure .... thnx to all

这篇关于在sql server中使用temprorary表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！