I have a function which traverses a graph.  I first query to get the nodeID which I pass into the function.  The problem is that sometimes the query returns multiple ids and only the last id  gets passed into the function.  Is there a way to get the results from the function for all the ids.

Here is the function and the code

CREATE TABLE [dbo].[Files](
	[nodeId] [bigint] NOT NULL,
	[nodeName] [varchar](255) NOT NULL,
	[nodeType] [varchar](7) NOT NULL)

CREATE TABLE [dbo].[Rel](
	[fromNodeId] [bigint] NOT NULL,
	[toNodeId] [bigint] NOT NULL)

insert into Files values (1,'file1','ABC');
insert into Files values (2,'file2','DEF');
insert into Files values (3,'file3','GHI');
insert into Files values (4,'file4','JKL');
insert into Files values (5,'file5','ABC');
insert into Files values (6,'file6','DEF');
insert into Files values (7,'file7','GHI');
insert into Files values (8,'file8','JKL');

insert into Rel values (1,2)
insert into Rel values (2,3)
insert into Rel values (3,4)
insert into Rel values (5,6)
insert into Rel values (6,7)
insert into Rel values (7,8)

CREATE FUNCTION [dbo].[fnUtil_GetIncoming]
(
    @nodeId AS BIGINT
) RETURNS TABLE
AS
RETURN
WITH cte AS
(
    --  Directly incoming
    SELECT toNodeId, fromNodeId, 1 AS level
    FROM   dbo.Rel
    WHERE  toNodeId = @nodeId

    UNION ALL

    -- Incoming to those in the previous level
    SELECT E.toNodeId, E.fromNodeId, C.level + 1 AS level
    FROM   cte C
    JOIN   dbo.Rel AS E ON C.fromNodeId = E.toNodeId
)
SELECT toNodeId, fromNodeId, level
FROM cte;

I want to run the following two sets queries.  The first part of the first query returns one nodeId and returns the correct table values.  The first part of the second query returns two nodeIds but only the second id is processed by the function. 

-- first set of queries

DECLARE @node BIGINT
SELECT @node = NodeID FROM Files WHERE  nodeName = 'File3'

SELECT  distinct FromNodeID,substring(n2.nodeName,1,80) AS FromNodeName,
        ToNodeID, substring(n1.nodeName,1,80) AS ToNodeName,level
FROM dbo.fnUtil_GetIncoming(@node) f
JOIN Files n1 ON n1.NodeID = f.toNodeID
JOIN Files n2 ON n2.NodeID = f.fromNodeId
order by level desc

Returns

FromNodeID	FromNodeName	ToNodeID	ToNodeName	level
1	file1	2	file2	2
2	file2	3	file3	1

-- second set of queries

DECLARE @node BIGINT
SELECT @node = NodeID FROM Files WHERE nodeType = 'GHI' --  nodeName = 'File3'

SELECT  distinct FromNodeID,substring(n2.nodeName,1,80) AS FromNodeName,
        ToNodeID, substring(n1.nodeName,1,80) AS ToNodeName,level
FROM dbo.fnUtil_GetIncoming(@node) f
JOIN Files n1 ON n1.NodeID = f.toNodeID
JOIN Files n2 ON n2.NodeID = f.fromNodeId
order by level desc

Returns

FromNodeID	FromNodeName	ToNodeID	ToNodeName	level
5	file5	6	file6	2
6	file6	7	file7	1

When I want it to return

FromNodeID	FromNodeName	ToNodeID	ToNodeName	level
1	file1	2	file2	2
2	file2	3	file3	1
5	file5	6	file6	2
6	file6	7	file7	1

(I don't care about the level being correct)

Any help would be appreciated.

Without  big changes and provided data

DECLARE @node BIGINT
DECLARE @node1 BIGINT
SELECT @node = NodeID FROM Files WHERE nodeType = 'GHI' --  nodeName = 'File3'
SELECT @node1 = NodeID FROM Files WHERE  nodeName = 'File3'

SELECT  distinct FromNodeID,substring(n2.nodeName,1,80) AS FromNodeName,
        ToNodeID, substring(n1.nodeName,1,80) AS ToNodeName,level
FROM dbo.fnUtil_GetIncoming(@node ) f
JOIN Files n1 ON n1.NodeID = f.toNodeID
JOIN Files n2 ON n2.NodeID = f.fromNodeId
UNION ALL
SELECT  distinct FromNodeID,substring(n2.nodeName,1,80) AS FromNodeName,
        ToNodeID, substring(n1.nodeName,1,80) AS ToNodeName,level
FROM dbo.fnUtil_GetIncoming(@node1 ) f
JOIN Files n1 ON n1.NodeID = f.toNodeID
JOIN Files n2 ON n2.NodeID = f.fromNodeId
order by level desc

My example had two sets.  My real data has about 1000.  I'm looking for a generic solution.

You're only getting one result because you predefined a parameter, and set it to a single value...

DECLARE @node BIGINT
SELECT @node = NodeID FROM Files WHERE nodeType = 'GHI' --  nodeName = 'File3'

SELECT  distinct FromNodeID,substring(n2.nodeName,1,80) AS FromNodeName,
        ToNodeID, substring(n1.nodeName,1,80) AS ToNodeName,level
FROM dbo.fnUtil_GetIncoming(@node) f
JOIN Files n1 ON n1.NodeID = f.toNodeID
JOIN Files n2 ON n2.NodeID = f.fromNodeId
order by level desc

You said here get the values from files, and put it into the variable node. Do this for every row.

Since the variable can only hold one value, it's repeatedly overwritten until the query finishes, leaving you with the last value.

Try this, you may need to tweak it:

SELECT  distinct FromNodeID,substring(n2.nodeName,1,80) AS FromNodeName,
        ToNodeID, substring(n1.nodeName,1,80) AS ToNodeName,level
FROM files f1
  CROSS APPLY dbo.fnUtil_GetIncoming(f2.nodeID) f
  JOIN Files n1 ON n1.NodeID = f.toNodeID
  JOIN Files n2 ON n2.NodeID = f.fromNodeId
WHERE f1.nodeType = 'GHI'
order by level desc

This worked great.  Thanks so much.

>> have a function which traverses a graph. <<

A traversal is a set of nodes, a function returns a single value. So this is wrong, you might have a procedure that returns a traversal. 

What math do you do with the file_name? The use of numeric data types for identifiers is a common design error. It is the SQL version of pointer math in C, etc.

Your non-tables have no keys! We need a PK! You have no DRI between the tables, that is what the R in RDBMS means. You have a table of files, but no file_name? Then your fake pointer is redundant and unverifiable. 

Larger integers, IDENTITY or GUIDs are used to mimic points by SQL noobs. What is the real identifier of the entity? 

>> I first query to get the file_name which I pass into the function. The problem is that sometimes the query returns multiple ids and only the last id gets passed into the function. Is there a way to get the results from the function for all the ids.

Here is the function and the code

CREATE TABLE Files
(file_name VARCHAR(255) NOT NULL PRIMARY KEY, 
 file_type CHAR(3) NOT NULL
 CHECK(file_type IN ('ABC', 'DEF', 'GHI', 'JKL'))
);

See the key for the table? See how the types are modeled with constraints? 

INSERT INTO Files 
VALUES 
('file1', 'ABC'), 
('file2', 'DEF'), 
('file3', 'GHI'), 
('file4', 'JKL'), 
('file5', 'ABC'), 
('file6', 'DEF'), 
('file7', 'GHI'), 
('file8', 'JKL'); 

What kind of entity or relationship is a Rel? I am using the roles head and tail from list processing on the column names. Why did you use multiple insertions statements? We have had row constructors since SQL-92. 


CREATE TABLE Rel
(hd_file_name VARCHAR(255) NOT NULL,
 REFERENCES Files(file_name)
 ON DELETE CASCADE 
 ON UPDATE CASCADE,
 tl_file_name VARCHAR(255) NOT NULL
 REFERENCES Files(file_name)
 ON DELETE CASCADE 
 ON UPDATE CASCADE,
 CHECK (hd_file_name <> tl_file_name)
PRIMARY KEY (hd_file_name, tl_file_name) 
);

-- fake pointers to model tree. 
INSERT INTO Rel 
VALUES (1, 2), (2, 3), (3, 4), (5, 6), (6, 7), (7, 8);

If the files are in hierarchy, then this is the worst way to model it. Google Nested sets. If a file can contain itself, then we have more work.