SQL Server : recursive update statement

Question

I'm somewhat new to SQL and I am trying to figure out the best way of doing this without hardcoding update statements in SQL Server 2012.

Basically I have a hierarchical table of companies (think of a supply chain) with columns (CompanyID, ParentID, ParentPriceAdj, CompanyPriceAdj). Each company gets assigned a price adjustment by their parent that modifies a list price in the PartMaster table and final price gets calculated by cascading the adjustments from parent to child.

If a parents price adjustment gets updated, I want that to reflect on all of his child companies and so forth

aka:

When updating a CompanyPriceAdj for a given updatedcompanyID, I want to recursively find the child CompanyID's (ParentId = updatedCompanyID) and update their ParentPriceAdj to ParentCompany's (parentPriceAdj * (1 + CompanyPriceAdj))

CompanyId     ParentID     ParentPriceAdj    CompanyPriceAdj
  5               6              0.96               .10
  6               8              1                  .20
  7               6              0.96               .15
  8              11              1                   0
 10               6              0.96                0
 11              12              1                   0

I was thinking of using a stored procedure that updates then repeatedly calls itself for every child that was just updated and then subsequently updates his children.... until the company has no children

I've tried looking around couldn't find any examples like this

This is what I have right now

ALTER PROCEDURE [dbo].[UpdatePricing] 
@updatedCompanyID int, @PriceAdj decimal
AS
BEGIN
SET NOCOUNT ON;

    WHILE (Select CompanyID From CompanyInfo Where ParentID = @updatedCompanyID) IS NOT NULL
       UPDATE CompanyInfo 
       SET  ParentPriceAdj = @PriceAdj * (1+CompanyPriceAdj), 
            @updatedCompanyId = CompanyID, 
            @PriceAdj = CompanyPriceAdj         
       WHERE ParentID = @updatedCompanyID

       --- some method to call itself again for each (@updatedCompanyID, @PriceAdj)
END

Answer 1

Recursive CTE can be used to walk hierarchy, something like:

ALTER PROCEDURE [dbo].[UpdatePricing]
(
    @companyID int,
    @PriceAdj decimal
)
as
begin
    set nocount on

    update CompanyInfo
    set CompanyPriceAdj = @PriceAdj
    where CompanyID = @companyID

    ;with Hierarchy(CompanyID, ParentID, InPriceAdj, OutPriceAdj)
    as (
        select D.CompanyID, D.ParentID, cast(D.ParentPriceAdj as float),
            cast(D.ParentPriceAdj as float) * cast(1 + D.CompanyPriceAdj as float)
        from CompanyInfo D
        where CompanyID = @companyID
        union all
        select D.CompanyID, D.ParentID,
            H.OutPriceAdj, H.OutPriceAdj * (1 + D.CompanyPriceAdj)
        from Hierarchy H
            join CompanyInfo D on D.ParentID = H.CompanyID
    )
    update D
    set D.ParentPriceAdj = H.InPriceAdj
    from CompanyInfo D
        join Hierarchy H on H.CompanyID = D.CompanyID
    where
        D.CompanyID != @companyID

end

Answer 2

You can use WITH expression in t-sql to get all parent records for given child record. And can update each record in record set accordingly with your logic.

Here are links for WITH expression --

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

http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx