I'm trying to create a procedure that'll return all the children/subchildren for a parent. For example - if we pass 1002 - it should return Company 1,2,3 and 4 - because 2 is the parent of 1 and 3, and 3 is the parent of 4.

Assuming you have SQL 2005 or later you can use a recursive common table expression (CTE) for this.You can search here for plenty of examples (search for hierarchy or parent-child).But here is a quick example of a recursive CTE based on your sample: