updating the parent and all its records child in sql

Question

Hi,

I am using SQL Query and below are the tables.

Organization

OrgID    Name        RAOGID   RAOID   SubGroupID   StatusID
1       Org RAOG1   1   NULL       NULL                2
2       Org RAO1         NULL    1     NULL      3
3       Org Sub Group1   NULL       NULL      1        1
4       Org RAOG2   2   NULL       NULL                2
5       Org RAO2         NULL    2     NULL      3
6       Org Sub Group2   NULL       NULL      2        2

RAOG

RAOGID  AccredID
1              2  
2              2  
3              2

RAO

RAOID   RAOGID
1         1
2         3
3         3

Sub Group

SubGroupID  RAOID
1            1
2            2
3            2

I have four tables as shown above, here is details of them, 1) Every RAOG, RAO and SUBGROUP are a organisation 2) RAOG can have many RAOs 3) RAO can have many SubGroups

I want to update my organisation table statusid, before updating I want to check if it is the RAOG type of oraganisation then it will update all the RAOs and SUBGROUPs under it and same with RAO it will update all the SUBGROUP under him it will not update it parent RAOG.And at last if it SUBGROUP type of organisation it will only update the subgroup not its parent records.

Thanks, Best Regards, Manoj

Answer 1

This is best acheived by wrapping the checks and calls you want to make into a stored procedure that you can then execute within a transaction.

CREATE PROCEDURE [dbo].[MySprocOfDoom]

@param1    paramType,
    --...
AS
BEGIN
SET NOCOUNT ON;
    --SELECT statements here
END