I'm assuming you're using SQL Server because this question is tagged with tsql (or usually t-sql) and that sp_executeSQL is a SQL Server stored procedure. Given that assumption, I don't think you really need to resort to using sp_executeSQL ... SQL Server 2000 added the ability to define local variables as table types, which makes it a bit easier to make something like this thread-safe.
Here's a script that creates a database with the kind of table you described, adds some data to that table, creates a stored procedure to get the list of ids like you want and returns it as a comma-delimited list in the same varchar input parameter. (Obviously this requires the permission to create and delete databases.) The stored procedure parameter @degrees lets you control the number of times you want to search for additional related users with a value of 0 causing it to search until no more are found. The last parameter, @relationid, if specified, will limit the search to a specific type of relation, so if you only wanted to see ancestors, you could specify @degrees = 0, @relationid = x where x is the id for a parent relationship and it would return parent of parents until it's found every user in that lineage.
create database testme
go
use testme
go
/* this suppresses output of statement results
which can sometimes interfere with other code */
set nocount on
/* this just creates some dummy data to test with - modify it however you need */
create table UserRElations (id int identity primary key, user1id int, user2id int, relationid int)
insert into UserRelations (user1id, user2id, relationid) values (1,2, 1)
insert into UserRelations (user1id, user2id, relationid) values (1,3, 3)
insert into UserRelations (user1id, user2id, relationid) values (2,3, 8)
insert into UserRelations (user1id, user2id, relationid) values (3,5, 2)
insert into UserRelations (user1id, user2id, relationid) values (3,7, 17)
insert into UserRelations (user1id, user2id, relationid) values (4,7, 6)
insert into UserRelations (user1id, user2id, relationid) values (4,8, 1)
insert into UserRelations (user1id, user2id, relationid) values (5,10, 3)
insert into UserRelations (user1id, user2id, relationid) values (5,13, 1)
go
create procedure pGetFriendsOfFriends
@list nvarchar(1000) output, -- comma delimited list of int values from the user1id column of UserRelations
@degrees tinyint = 0, -- the number of times to search for more relations - 0 means no limit
@relationid int = null -- set this only if you want a specific type of relation
as
/* see above comment about nocount */
set nocount on
/* create a local variable that is a table
with one int column named id as the primary key */
declare @utbl table (id int primary key)
/* additional temp variables for later use */
declare @tmp nvarchar(5) -- if you have user1ids of more than 5 digits you might need this larger
declare @next int, @i int
set @list = @list + ',' -- make sure there's a comma after every id in the list
/* move the first item in the list to the table until the list is empty */
while (len(@list) > 0) begin
set @next = charindex(',', @list) -- find the first comma in the list
if (@next > 0) begin
/* we found a comma, insert an id into the temp table utbl */
set @tmp = ltrim(rtrim(left(@list, @next-1))) -- get a string up to the comma
if (isnumeric(@tmp) = 1) begin -- make sure the string is numeric
set @i = cast(@tmp as int) -- convert the string to an int
if (not exists (select id from @utbl where id = @i)) -- make sure we dont insert the same id twice
insert into @utbl values (@i) -- self explanatory
end
/* remove the id we just inserted into the table from the string */
set @list = substring(@list,@next+1,len(@list))
end else set @list = '' -- there weren't any more commas, we're done
end
/* now we're going to loop over the search for relations */
set @i = 1 -- set our temp variable to 1 to start the loop
while (@i <= @degrees -- loop from 1 to a specified number of @degrees of separation
or /* the keyword "exists" below will stop the select statement and return true as soon as a matching row is found */
(@degrees = 0 and exists
( -- !!NOTE!! this select statement must have the same where clause as below - otherwise you risk an infinite loop
select distinct urx.user2id
from UserRelations urx
where urx.user1id in (select id from @utbl)
and urx.user2id not in (select id from @utbl)
and (@relationid is null or urx.relationid = @relationid)
)
))
begin
/* add more relations for the current pass */
insert into @utbl
select distinct urx.user2id -- distinct ensures all the returned user2id values are unique
from UserRelations urx
where urx.user1id in (select id from @utbl) -- find relations for users already in the @utbl temp table
and urx.user2id not in (select id from @utbl) -- ignore any that are already in the @utbl temp table
and (@relationid is null or urx.relationid = @relationid) -- allow the caller to declare a specific relation type - null will return all types
set @i = @i + 1 -- !!NOTE!! if @degrees is not 0, the counter must be incremented (or you'll get an infinite loop)
end
set @list = '' -- we'll return the list parameter to the caller, but start with an empty string
/* create a cursor to loop over the ids we found */
declare cFoF cursor local fast_forward for
select id from @utbl
open cFoF -- open the cursor
fetch next from cFoF into @i -- get the first item from the cursor
while (@@FETCH_STATUS = 0) begin -- while there are more records in the cursor
set @list = ',' + cast(@i as nvarchar) + @list -- add the current item to the list
fetch next from cFoF into @i -- get the next item from the cursor
end
set @list = substring(@list, 2, len(@list)) -- remove the leading comma from the list
return -- make sure the @list parameter goes back to the caller
go
declare @list nvarchar(1000) = '1,2'
exec pGetFriendsOfFriends @list out, 2
print ''
print '@list=1,2, @depth=2 => @list=' + @list
set @list = '2'
exec pGetFriendsOfFriends @list out, 1
print ''
print '@list=2, @depth=1 => @list=' + @list
set @list = '1'
exec pGetFriendsOfFriends @list out, 0
print ''
print '@list=1, @depth=0 => @list=' + @list
set @list = '1'
exec pGetFriendsOfFriends @list out, 0, 1
print ''
print '@list=1, @depth=0, @relationid=1 => @list=' + @list
go
use master
go
drop database testme
go
When I execute this script, the output I get looks like:
@list=1,2, @depth=2 => @list=7,5,3,2,1
@list=2, @depth=1 => @list=3,2
@list=1, @depth=0 => @list=13,10,7,5,3,2,1
@list=1, @depth=0, @relationid=1 => @list=2,1
In most cases I personally find that taking the input as a string list of integers or returning it that way isn't actually necessary. If you turn that local table variable into a SQL server type (SQL Server 2008 or later), then you can pass a table of that type in as a parameter to the stored procedure, or return one back out, eliminating the need for all that extra string parsing and converting between int and varchar, and your procedure will undoubtedly execute faster that way.
