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.