Stored Procedure for getting connections

G'day,
I have a bit of a problem where I have a stand alone connection table that I am trying to drill down to get all connections for item x. I have previously done the code in C# but as you can imagine, it's a bit clumsy! So now I'm trying to store it as a stored procedure. Basically I have a table that holds my connections with four fields that holds the relevant data. 'L_CONN', 'L_ID', 'R_CONN' and "R_ID' and another field 'SUSPENDED_REC' which tells me if a connection is still valid. 'L_CONN' holds the name of the table that makes up the left hand side of the connection. 'L_ID' is its unique id number. 'R_CONN' holds the name of the table that makes up the right hand side of the connection. 'R_ID' is its unique id number. Currently I have a working stored procedure that will return the top level of connections (The code snippet), but I want it to be able to loop down the connection 'tree' until it runs out of connections. Finally, if possible, I would then like to be able to retrieve the fields 'OBJECT_ID', 'DESCRIPTION', 'ICON', 'STATUS', 'LOCKED' from each record that it finds.
The data in the table looks like this:
L_CONN            L_ID            R_CONN          R_ID            SUSPENDED_REC
PROJECT           1                 SITES              1                  0
PROJECT           1                 SITES              2                  0
PROJECT           1                 SITES              3                  0
PROJECT           1                 SITES              4                  0
SITES                 1                SITE_SYSTEM  1                 0
SITES                 1                SITE_SYSTEM  2                 0
SITES                 3                SITE_SYSTEM  3                 0
SITES                 3                SITE_SYSTEM  4                 0

Hopefully someone out there has a bit more of an idea about this then I do. The stored procedure will first roll out on a SQL2000 box, but I have been told that it will also have to work on a SQL2005, and eventually SQL2008.
Thanks for the help
Start Free Trial

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:

CREATE PROCEDURE QuickConnConnectionSingle(
@t1 varchar(255),
@inVal varchar(10)
)
AS
BEGIN
 
SET DATEFORMAT DMY
SELECT R_CONN, R_ID from conlink where L_CONN=@t1 and L_ID=@inVal and UP='N' and ((ISNUMERIC(SUSPENDED_REC) = 0) OR (SUSPENDED_REC = 0))
END
GO