I have a MS SQL 2008 table called PARENTCHILD with the following fields of interest:
ID (Auto Number)
PID (NVARCHAR)
CPID (NVARCHAR)
DATEOFSPLIT (DATE)
This table is a parent Child relationship for records that are combined and/or split. I need a query using any means that will recursively look up using the PID and CPID to find all of the "linked" records.
ID PID CPID DATEOFSPLIT
13 R72 00104 0005 MCD-PLACEHOLDER-1 2012-04-25
14 R72 00104 0006 MCD-PLACEHOLDER-1 2012-04-25
15 R72 00104 0007 MCD-PLACEHOLDER-1 2012-04-25
16 MCD-PLACEHOLDER-1 NEWPID1 2012-04-25
17 MCD-PLACEHOLDER-1 NEWPID2 2012-04-25
18 NEWPID2 NEWPID1RR1 2012-05-07
19 NEWPID2 NEWPID1RR2 2012-05-07
20 NEWPID1RR1 NEWPID1XX1 2012-05-07
21 R72 00104 0010 NEWPID1XX1 2012-05-07
So in the data example I enter a search variable NEWPID1XX1 This would find record 21 and 20 and then it would look at the PID of each (NEWPID1RR1 + R72 00104 0010) and then look for both of those PIDs in the CPID fields, which would find REcord #18, then it would look at the PID of that field (NEWPID2) and then continue to search that PID in teh CPID field and find record #17 (MCD-PLACEHOLDER-1) which would find in teh CPID field #13, #14, and #15. Since none of those PIDs are found in the CPID the recordset would end.
I need the record set to be ordered by an entered variable of the stored proceedure and display all the fields in the table. I do not have any sample data, except for the example above. I also need an example of how to call this in VB script (Classic ASP). If you can make a function in Classic ASP that can do this, I can also work with that.