如何使用“for循环”在sp [英] how to use the "for loop" in sp

问题描述

朋友们，



我正在做一个有计算的项目。在我的前端，我必须在网格中显示结果。所以我想我将完成SQL中的步骤并将其与网格绑定。

我有两个SP'是计算，我必须使用for循环将它们合并在一个SP中。

我有的SP是：

for Onsite：

Hi friends,

I am doing a project which has calculations. In my front end, I have to display the result in a grid. So I thought I ll complete the steps in SQL and bind it with the grid.
I have two SP''s which are calculations and I have to merge both of them in a single SP using for loop.
The SP''s I have are:
for Onsite:

Alter procedure usp_PyramidCalculationOnsite
as
begin
select      
            A.ServiceID,Service,--,FTECnt,C.PT,D.Offshore,
            (FTECnt*C.PT/100*D.Onsite/100) as PT,
            Round((FTECnt*C.PAT/100*D.Onsite/100),1) as PAT,
            Round((FTECnt*C.P/100*D.Onsite/100),1) as P,
            Round((FTECnt*C.PA/100*D.Onsite/100),1) as PA,
            Round((FTECnt*C.A/100*D.Onsite/100),1) as A,
            Round((FTECnt*C.SA/100*D.Onsite/100),1) as SA,
            Round((FTECnt*C.M/100*D.Onsite/100),1) as M,
            Round((FTECnt*[SM+]/100*D.Onsite/100),1) as SM            
from 
            (select 
                        A.ServiceID,B.Service,A.IsScope,
                        10 as FTECnt 
            from  
                        tblEstimateServices A,
                        tblServices B
                        
            where 
                        IsScope > 0 and 
                        EstimateID=827 and 
                        VersionNo=1 and 
                        A.ServiceID = B.ServiceID)A,tblDesignationReference C,tblOnsiteOffshoreRatio D
where 
            A.ServiceID = C.ServiceID and 
            Location='Onsite' and 
            ReferenceModel='Aggressive' and 
            A.ServiceID = D.ServiceID and           
            C.ServiceID = D.ServiceID
 End

和离岸版：

and for offshore its :

Alter procedure usp_PyramidCalculationOffshore
as
begin
select      
            A.ServiceID,Service,--,FTECnt,C.PT,D.Offshore,
            (FTECnt*C.PT/100*D.Offshore/100) as PT,
            Round((FTECnt*C.PAT/100*D.Offshore/100),1) as PAT,
            Round((FTECnt*C.P/100*D.Offshore/100),1) as P,
            Round((FTECnt*C.PA/100*D.Offshore/100),1) as PA,
            Round((FTECnt*C.A/100*D.Offshore/100),1) as A,
            Round((FTECnt*C.SA/100*D.Offshore/100),1) as SA,
            Round((FTECnt*C.M/100*D.Offshore/100),1) as M,
            Round((FTECnt*[SM+]/100*D.Offshore/100),1) as SM            
from 
            (select 
                        A.ServiceID,B.Service,A.IsScope,
                        10 as FTECnt 
            from  
                        tblEstimateServices A,
                        tblServices B
                        
            where 
                        IsScope > 0 and 
                        EstimateID=827 and 
                        VersionNo=1 and 
                        A.ServiceID = B.ServiceID)A,tblDesignationReference C,tblOnsiteOffshoreRatio D
where 
            A.ServiceID = C.ServiceID and 
            Location='Offshore' and 
            ReferenceModel='Aggressive' and 
            A.ServiceID = D.ServiceID and 
            C.ServiceID = D.ServiceID
end

在我的前端，我必须向他们展示l ike this，

In my front end, I have to show them like this,

ServiceID  FTE            Offshore                       Onsite

              P  PAT  PA  PT  A  SA  M  SM        P   PAT  PA  PT  A  SA  M  SM

我必须在此网格下显示结果。



请帮助我进一步了解for循环查询。



提前致谢。

I have to show the result under this grid.

Please help me on how to proceed further with the for loop query.

Thanks in advance.

推荐答案

嗨Anusha，



根据您的要求，我不认为您需要使用循环。

我们也应尽量减少使用循环。

Hi Anusha,

As per your requirement, i don''t think that you need to use loop.
Also we should try to minimize use of loop.

CREATE PROCEDURE PROC_PYRAMIDCALCULATION
As 
 begin

	SELECT      
	A.SERVICEID,SERVICE,--,FTECNT,C.PT,D.OFFSHORE,
	CASE WHEN LOCATION ='OFFSHORE' THEN   (FTECNT*C.PT/100*D.OFFSHORE/100)
	WHEN LOCATION = 'ONSITE' THEN (FTECNT*C.PT/100*D.ONSITE/100)  END  AS PT,
	CASE WHEN LOCATION = 'OFFSHORE' THEN ROUND((FTECNT*C.PAT/100*D.OFFSHORE/100),1)
	WHEN LOCATION ='ONSITE' THEN  ROUND((FTECNT*C.PAT/100*D.ONSITE/100),1) END  AS PAT,
	CASE WHEN LOCATION = 'OFFSHORE' THEN ROUND((FTECNT*C.P/100*D.OFFSHORE/100),1)	
	WHEN LOCATION = 'ONSITE' THEN  ROUND((FTECNT*C.P/100*D.ONSITE/100),1)  END P,
	CASE WHEN LOCATION = 'OFFSHORE' THEN  ROUND((FTECNT*C.PA/100*D.OFFSHORE/100),1)	
	WHEN LOCATION = 'ONSITE' THEN   ROUND((FTECNT*C.PA/100*D.ONSITE/100),1) END PA,
	CASE WHEN LOCATION = 'OFFSHORE' THEN ROUND((FTECNT*C.A/100*D.OFFSHORE/100),1)   
	WHEN LOCATION = 'ONSITE' THEN  ROUND((FTECNT*C.A/100*D.ONSITE/100),1)  END A,
	CASE WHEN LOCATION = 'OFFSHORE' THEN ROUND((FTECNT*C.SA/100*D.OFFSHORE/100),1)	
	WHEN LOCATION = 'ONSITE' THEN  ROUND((FTECNT*C.SA/100*D.ONSITE/100),1)  END SA,
	CASE WHEN LOCATION = 'OFFSHORE' THEN  ROUND((FTECNT*C.M/100*D.OFFSHORE/100),1)	
	WHEN LOCATION = 'ONSITE' THEN ROUND((FTECNT*C.M/100*D.ONSITE/100),1) END M,
	CASE WHEN LOCATION = 'OFFSHORE' THEN  ROUND((FTECNT*[SM+]/100*D.OFFSHORE/100),1)	
	WHEN LOCATION = 'ONSITE' THEN ROUND((FTECNT*[SM+]/100*D.ONSITE/100),1)  END SM

	FROM 
	(SELECT 
	A.SERVICEID,B.SERVICE,A.ISSCOPE,
	10 AS FTECNT 
	FROM  
	TBLESTIMATESERVICES A
	JOIN  TBLSERVICES B ON A.SERVICEID = B.SERVICEID

	WHERE 
	ISSCOPE > 0 AND 
	ESTIMATEID=827 AND 
	VERSIONNO=1 
	)A,

	JOIN TBLDESIGNATIONREFERENCE C ON A.SERVICEID = C.SERVICEID
	JOIN TBLONSITEOFFSHORERATIO D ON   A.SERVICEID = D.SERVICEID
	WHERE 

	LOCATION IN ('OFFSHORE','ONSITE') AND 
	REFERENCEMODEL='AGGRESSIVE'

end

请为我的解决方案评分

Please rate my Solution

这篇关于如何使用“for循环”在sp的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！