how to use the "for loop" in sp

Posted by Anushansri under Sql Server on 12/20/2012 | Points: 10 | Views : 908 | Status : [Member] | Replies : 2
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 calculationa and i have to merge both of them in a single sp using for loop. The sp's i have pasted below.!

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

==========================================

In my front end, i have to show then like this,

ServiceID FTE Offshore Onsite

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

I have to show the result under this grid..

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

Thanks in advance.
.




Responses

Posted by: Pandians on: 12/21/2012 [Member] [MVP] Silver | Points: 25

Up
0
Down
Clarify the following items!

1. Do you need Look here ?

2. If the both SP returns single record then, First, Insert the result of each SP into a Temp table and then JOIN the both tables and produce the result as you want.

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Anushansri, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Anushansri on: 12/21/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,
Thanks for the reply,
I am not sure weather to use the loop.
both the sp's gives different results which i want to merge and show it in a grid.
Can you please help me on how to query it?

Thanks much.!
Anu


Anushansri, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response