for loop in sp RSS

• • Reply
  

  Anusha Sridh...

  Member

  5 Points

  43 Posts

  for loop in sp

  Dec 21, 2012 02:23 AM|LINK

  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.

• • Reply
  

  shanmugamm

  Participant

  1612 Points

  317 Posts

  Re: for loop in sp

  Dec 21, 2012 04:44 AM|LINK

  It's not a good idea to keep the calculation inside the SQL query. it should be part of your Business Logic Layer, Don't mix up too many things in Store Procedure

  My Blog
  http://shanmugam-netguru.blogspot.com
  Follow me in Linkedin
• • Reply
  

  eralper

  Contributor

  6048 Points

  971 Posts

  Re: for loop in sp

  Dec 21, 2012 05:32 AM|LINK

  Hi Anisha,

  Why you need a loop I could not understand?

  Your procedures work set based.

  If it works with item parameter, you can build a sql cursor and pass record id as an input parameter to inner procedure. But in your case I guess no need for a loop or sql cursor.

  T-SQL programming SQL Server Tutorials
  SQL Server 2012
  
  • Marked as answer by Catherine Shan - MSFT on Dec 27, 2012 04:29 AM
• • Reply
  

  nikunjnandan...

  Participant

  882 Points

  223 Posts

  Re: for loop in sp

  Dec 23, 2012 06:33 AM|LINK

  Hiii,,

  You can use while loop or cursor in sql server as per you logic.

  Here below is link for cursor.

  http://msdn.microsoft.com/en-us/library/ms180169.aspx

  http://blog.sqlauthority.com/2007/01/01/sql-server-simple-example-of-cursor/

  Regards,
  Nikunj Nandaniya
  My Blog