This was a project that was already completed on Vworker, however I approved the code and found a small problem afterwards. I need this problem fixed AS fast as possible. I will include the current source code, original description and the current problem.
## Deliverables
**ORIGINAL REQUEST**
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 procedure 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.
**PROBLEM THAT I NEED YOU TO FIX**
When I response write the SQL I find that it is repeating the same query until it gives the error. It is like it is stuck in an infinite loop. I don't know what the problem is however it looks like it is related to records that have the same CPID. the problem can be seen if you look at Record ID: 80 The CPID is Me2000 the PID is MCD-PLACEHOLDER-1 however I changed it from the original data MCD-PLACEHOLDER-3 and then ran the query for Me2000 if MCD-PLACEHOLDER-3 is the PID then it fails, if MCD-PLACEHOLDER-1 (which is wrong, only for testing) then it works fine. This same error for Record ID 79, 80, 81 Also 33,34,35 Also 68 and 69.. Basically ALL records that have any sharing CPID
** See Attached Document Code