Help on Stored Proc. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help on Stored Proc.

Author

Topic

Rajee
Starting Member

12 Posts

Posted - 2006-08-21 : 08:21:40

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-21 : 08:25:03

How does you table definition's look like?

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-21 : 11:04:57

1 http://www.datamodel.org/NormalizationRules.html
2 http://www.nigelrivett.net/RetrieveTreeHierarchy.html

Madhivanan

Failing to plan is Planning to fail

Rajee
Starting Member

12 Posts

Posted - 2006-08-22 : 01:11:51

As i mentioned in my first message, the table consists of two columns parent and child (both are integers)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-22 : 01:29:33

quote:
Originally posted by Rajee

As i mentioned in my first message, the table consists of two columns parent and child (both are integers)

Please post your table DDL, some sample data and the result that you want

Rajee
Starting Member

12 Posts

Posted - 2006-08-22 : 02:37:29

Ok.To be more clear here is the thing which iam looking for

Table1 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)

Hope this helps

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-22 : 03:18:15

This might be what you want. Even better maybe, since it is wrapped in a function.

CREATE FUNCTION dbo.fnGetChildren
(
	@Parent INT,
	@Child INT
)
RETURNS VARCHAR(8000)
AS

BEGIN
	DECLARE	@Generation INT

	SELECT	@Generation = 0

	DECLARE	@Result TABLE
		(
			Generation INT,
			Child INT
		)

	INSERT	@Result
	SELECT	-1,
		@Parent

	INSERT	@Result
	SELECT	0,
		Child
	FROM	Family
	WHERE	Parent = @Parent

	INSERT	@Result
	SELECT	0,
		@child

	WHILE @@ROWCOUNT > 0
		BEGIN
			SELECT	@Generation = @Generation + 1

			INSERT	@Result
			SELECT	@Generation,
				f1.Child
			FROM	Family f1
			WHERE	f1.Parent IN (SELECT r1.Child FROM @Result r1 WHERE r1.Generation = @Generation - 1)
				AND f1.Child NOT IN (SELECT r2.Child FROM @Result r2 WHERE r2.Generation > 0)	-- To avoid redundance
		END

	DECLARE	@Concat VARCHAR(8000)

	SELECT	@Concat = LEFT(ISNULL(@Concat + ',', '') + CONVERT(varchar, z.Child), 8000)
	FROM	(
			SELECT		TOP 100 PERCENT Child
			FROM		@Result
			ORDER BY	Generation,
					Child
		) z

	RETURN	@Concat
END

And just for calification, call with

select * from fnGetChildren(1, null)
select * from fnGetChildren(2, 7)
select * from fnGetChildren(4, 7)

Peter Larsson
Helsingborg, Sweden

Rajee
Starting Member

12 Posts

Posted - 2006-08-22 : 07:43:19

Thanks Peter. Even though it is not exactly what i was looking for, it helped me to sort out a way of doing this. Acually i was looking for passing comma separated input string as one argument (and not specifically parent or children) and my output need to include the input also.For example when i say my input is 4,7 it doesn't mean that 4 is the parent and 7 is the child. My input can also be 4,7,3 etc.,
Anyway thanks for your effort.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-22 : 08:57:07

What is the input then?
How do you expect us to help you if we do not get full information?

Peter Larsson
Helsingborg, Sweden

Rajee
Starting Member

12 Posts

Posted - 2006-08-22 : 23:39:55

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.
Thanks.

Rajee
Starting Member

12 Posts

Posted - 2006-08-22 : 23:42:19

To add to that

@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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-23 : 00:52:37

Use a split function and insert the result into the result table.

Peter Larsson
Helsingborg, Sweden

Rajee
Starting Member

12 Posts

Posted - 2006-08-23 : 01:31:25

Done that already.Thanks

Rajee
Starting Member

12 Posts

Posted - 2006-08-23 : 02:22:06

Hi,
Can u help me with this?
CREATE PROCEDURE [dbo].[pr_FetchDetails]
@Ids Varchar(8000) AS
BEGIN
Select * from Family where ID in @Ids
END

say @Ids=(1,2,3)

Iam getting an incorrect syntax error near the in keyword.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-23 : 02:25:52

well you need to use the split function.



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CrackInRows]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[CrackInRows]
GO

CREATE FUNCTION [dbo].[CrackInRows] (@delim varchar(1), @Phrase2Crack as varchar(8000))
RETURNS @CrackRow table 
(
	INROWS varchar(1000)
)
as
BEGIN 
insert @CrackRow
Select NullIf(SubString(@Delim + @Phrase2Crack + @Delim , IDNos , 
CharIndex(@Delim , @Delim + @Phrase2Crack + @Delim , IDNos) - IDNos) , '') AS INROW
FROM IDNos
WHERE IDNos <= Len(@Delim + @Phrase2Crack + @Delim) AND 
SubString(@Delim + @Phrase2Crack + @Delim , IDNos - 1, 1) = @Delim 
AND CharIndex(@Delim , @Delim + @Phrase2Crack + @Delim , IDNos) - IDNos > 0
return
END
GO

CREATE PROCEDURE [dbo].[pr_FetchDetails]
@Ids Varchar(8000) AS
BEGIN
Select * from Family f Inner Join Dbo.CrackInRows(',',@Ids) as Cr On 
Cr.InRows = F.ID
END

Edit: OR Second way to do this, is by using Dynamic SQL


CREATE PROCEDURE [dbo].[pr_FetchDetails]
@Ids Varchar(8000) AS
BEGIN
Declare @QryString Varchar(2000)
Set @QryString = 'Select * from Family Where ID In ( ' + @IDs + ')'
Exec(@QryString)
END

Chirag

Rajee
Starting Member

12 Posts

Posted - 2006-08-23 : 02:39:02

Thanks chirag

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources