how to return integers with "," delimiter

[KatherineF]

I have this function. The result of this fucntion I should insert into IN Statement ( Example: PartnerID IN (dbo.Function_Name(1)) ) Now IN statement looks like this IN ('1,2'), but I need IN (1,2)

ALTER FUNCTION dbo.Function_Name (@PartnerID INT)
RETURNS VARCHAR(8000) AS
BEGIN

DECLARE
@ParentID AS INT,
@ChildID AS INT,
@ParentName AS VARCHAR(50),
@LEVEL AS VARCHAR(8000),
@RootPartnerID INT


SET @ChildID = @PartnerID
SET @Level = ''

SET @RootPartnerID = 1111

WHILE @ChildID <> @RootPartnerID
BEGIN
SELECT @ParentID = PartnerID_Parent, @ParentName = PartnerName_Parent FROM dbo.Partner_Relationship_Info_V WHERE PartnerID = @ChildID

SET @LEVEL = CAST(@ParentID AS VARCHAR(50)) + ',' + @LEVEL

SET @ChildID = @ParentID
END

IF RIGHT(@LEVEL,1)= ',' SET @LEVEL=LEFT(@LEVEL,LEN(@LEVEL)-1)


EndOfFunction:
RETURN (@LEVEL)
END

Thank you!

 

[donutman]

Have your function return a table of PartnerID_Parent. Then instead of writing a PartnerID IN do an inner join with the table that the function returns.
-Karl

 

[KatherineF]

no, it doesn't. This is the solution. Thank you!

 

[donutman]

Actually, I think you'll find that either solution is a performance nightmare on anything but small dbs, but good luck.
-Karl