Help on Stored Proc
-
Hi,
Iam looking for a stored procedure where it can take a list of parent ids(comma separated string) and return me a list of parent and all the children below the parents(level upto 10) as a comma separated string.The table consists of two columns parent and child.Could someone suggest me a better way of doing this?
Thanks
Rajee
-
To be more clear here is the thing which iam looking for
Family Table with two columns
parent int
child int
The table can be like
parent child
1 2
1 3
1 4
2 5
2 6
3 7
7 8
1.When i pass the input as 1 to the stored procedure say GetChildren
it should return me 1,2,3,4,5,6,7,8
2.When i pass the input as 2,7 the output string should be 2,5,6,7,8
3.When i pass the input 4,7 the output string should be 4,7,8
Create table Family
(parent int,
child int)
insert into Family values (1,2)
insert into Family values (1,3)
insert into Family values (1,4)
insert into Family values (2,5)
insert into Family values (2,6)
insert into Family values (3,7)
insert into Family values (7,8)
Thanks
-
I am not clear as to why if you pass in 2,7 you believe you should get a "7" in your output. The same is true of 4,7. There is no "parent" of 4.
But, here is a function you can use for parsing. It uses the pipe [ | ] character instead of comma's. Often comma's are used in text fields, hence it gets complicated using them in the string, so we decided that the front-end application would use pipes instead as that is very unusual character and rarely used.
This may help you get a start. When you have completed your query, you can always CHAR(39) as a seperator to put comma's in your output.
Hope this helps:
CREATE FUNCTION dbo.Parse( @ParseField varchar(2000))
RETURNS varchar(25)
AS
SET NOCOUNT ON
DECLARE @Delimiter char(1),
@LeftDelimter smallint,
@Item varchar(50),
@List varchar(8000)
IF NOT( @ParseField IS NULL OR @ParseField = '0' OR LEN( RTRIM( @ParseField)) < 1)
BEGIN
SELECT @LeftDelimter = 1,
@Delimiter = ';',
@List = @Delimiter + @ParseField + @Delimiter
WHILE CHARINDEX( @Delimiter, @List, @LeftDelimter + 1) > 0
BEGIN
SELECT @Item = SUBSTRING( @List, @LeftDelimter + 1, CHARINDEX( @Delimiter, @List, @LeftDelimter + 1) - ( @LeftDelimter + 1))
IF LTRIM( RTRIM( @Item)) <> ''
BEGIN
SELECT @Item
END
SELECT @LeftDelimter = CHARINDEX( @Delimiter, @List, @LeftDelimter + 1)
END
END
I wasn't born stupid - I had to study.
-
Sorry..if my requirement is not clear...It is something like this
CREATE PROCEDURE pr_GetAllChildrenWithParentList
(@input Varchar(8000),
@Result Varchar(8000) OUTPUT )
.....
When @input=1,3
@Result=1,2,3,4,5,6,7,8
When @input=2,7
@Result=2,5,6,7,8
Its like traversing the tree till leaf node.Hope it is clear now.
@input is simply a list of nodes separated by comma. Need not be parent, child etc.,Say for example it can also be 5,3,4.
Thanks.
-
I cannot follow what you believe should be your output. Here is the original data you gave us.
Original data:
parent child
1 2
1 3
1 4
2 5
2 6
3 7
7 8
In your first input, you pass a string of ParentID's as follows:
When @input=1,3
@Result=1,2,3,4,5,6,7,8
I believe the result should be: 1, 2, 3, 4, 7
parent child
1 2
1 3
1 4
3 7
( can you explain the discrepancy?)
In your second input you pass a string of ParentID's as follows:
When @input=2,7
@Result=2,5,6,7,8
In this instance I get the same result set as you.
I gave you an idea for a method to parse out your @input string, this other question may help in giving you an idea of your returned values: Concatinate row values in a column
Let me know how this works for you.
I wasn't born stupid - I had to study.
-
To get a bit more testing data, I used the following code. While not perfect, it should be close enough to what you have described.
--DROP table Family
DECLARE @Temp table (Parent int, Child int)
DECLARE @curPar int, @curChild int, @curId int, @numChild int, @numLevels int
SET @numLevels = 20
SET @curId = 1
WHILE @numLevels > 0
BEGIN
SET @curPar = @curId
SET @numChild = (rand() * 3) + 1
WHILE @numChild > 0
BEGIN
SET @curId = @curId + 1
INSERT INTO @Temp VALUES (@curPar, @curId)
SET @numChild = @numChild - 1
END
SET @numLevels = @numLevels - 1
END
SELECT * INTO Family FROM @Temp
The following function is needed for parsing your input string. This function assumes a numbers table (first number = 1) is present, as well as SQL 2005. For information on creating a numbers table, take a look at this recent article The Joy of Numbers. The comments on the article include some discussion and altenate ideas for stripping strings, so you may want to take a look there if you want to get more from the stripping function.
CREATE FUNCTION dbo.fn_StripStringtoTable
(@String varchar(8000), @Delim char(1))
RETURNS TABLE
AS
RETURN
(
WITH MyCTE (Num, String)
AS
(
SELECT Num, SUBSTRING(@String, Num,
CASE CHARINDEX(@Delim, @String, Num)
WHEN 0 THEN LEN(@String) - Num + 1
ELSE CHARINDEX(@Delim, @String, Num) - Num
END
) AS String
FROM dbo.Numbers
WHERE Num <= LEN(@String)
AND (SUBSTRING(@String, Num - 1,
1) LIKE @Delim
OR Num = 1)
)
SELECT ROW_NUMBER() OVER (ORDER BY Num) AS Row, Num, String
FROM MyCTE
)
I did not make an actual procedure for this, but the following should be obvious enough on how to get there.
DECLARE @MaxNodeDepth int, @String varchar(8000)
-- You mentioned 10 levels in your post, so I have used it here.
SET @MaxNodeDepth = 10
SET @String = '2,7,13,6'
-- Code above will be handled in your procedure definition
DECLARE @Criteria table (Id int)
INSERT INTO @Criteria SELECT String FROM fn_StripStringtoTable(@String, ',')
DECLARE @Stage table(Id int)
DECLARE @Final varchar(8000)
SET @Final = ''
-- SQL 2005 makes this recursion very simple to create by using Common Table Expression
-- Pulls all parents and children to the level specified above into the @Stage table
;WITH Partial(Id, NodeDepth) AS
(
SELECT Parent, 1 AS NodeDepth FROM Family
WHERE Parent IN (SELECT Id FROM @Criteria)
UNION ALL
SELECT Child, NodeDepth + 1 FROM Family F
INNER JOIN Partial P on F.Parent = P.Id
)
INSERT INTO @Stage
SELECT DISTINCT Id FROM Partial
WHERE NodeDepth <= @MaxNodeDepth
-- In case there was an Id passed in that is not a parent, this step will insert those records
INSERT INTO @Stage
SELECT Id FROM @Criteria C
WHERE NOT EXISTS (SELECT 1 FROM @Stage WHERE Id = C.Id)
SELECT Id FROM @Stage
ORDER BY Id
SELECT @Final = CASE WHEN @Final <> ''
THEN @Final + ','
ELSE @Final
END + RTRIM(CONVERT(char, Id))
FROM @Stage
ORDER BY Id
SELECT @Final
Good luck!
John
-
We have only just gotten 2005, so I have not used CTE's yet.
Thanks for this example! I really want to start to using and understanding this new functionality! (And considering how hard it is to teach an old dog new tricks, this example will really help me!)
I wasn't born stupid - I had to study.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply