Click here to Skip to main content
15,921,660 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
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
Posted
Updated 30-Jun-13 23:13pm
v2
Comments
bbirajdar 1-Jul-13 5:05am    
It gives error because there is problem with the code.
GDdixit 1-Jul-13 5:22am    
whats the problem ...give some hint ..
GDdixit 1-Jul-13 5:22am    
rply.........
_Amy 1-Jul-13 6:20am    
What error it is giving?

One possible error I can see is the temp table is not dropped. It is preferable to use table variable.
 
Share this answer
 
v2
Comments
GDdixit 1-Jul-13 5:41am    
for which table you say this ...for #MYTEMPTABLE ...
? pls explain ..
ArunRajendra 1-Jul-13 5:52am    
Yes for temp table #MYTEMPTABLE
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.
 
Share this answer
 
Comments
GDdixit 1-Jul-13 6:17am    
how can i drop the temp table befire inserting data of that table .....?
KuttiSankar 1-Jul-13 6:51am    
you can drop it after your variable declaration
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
 
Share this answer
 

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