Stored Procedure Recursive Funtion

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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure Recursive Funtion

Author

Topic

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-06-17 : 11:03:37

Hello Friends,
I have a table with two columns.
1. DesignationId auto increment int
2. BelongsTo int
Table Data is as follows
DesignationId---BelongsTo
1----------------NULL
2----------------1
3----------------1
4----------------2
5----------------2
6----------------3
7----------------3
8----------------3
My requirement is to develop a stored procedure thant can accept DesignationId (say 1) then the output should be belongsTo including child belongsTo as 2,3,4,5,6,7,8.
Input : 1
Output : 2,3,4,5,6,7,8
Input: 2
Output: 4,5
Input: 3
Output: 6,7,8

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-17 : 11:07:28

[code]
CREATE PROC GetHierarchy
@DesgnID int
AS

;With CTE (DesnID,Parent) AS
(SELECT DesignationId,BelongsTo
FROM YourTable
WHERE DesignationId=@DesgnID
UNION ALL
SELECT t.DesignationId,t.BelongsTo
FROM YourTable t
INNER JOIN CTE c
ON c.DesnID=t.BelongsTo
)
SELECT * FROM CTE
GO
[/code]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-17 : 12:07:13

Just watch out for circular reference!

add the following lines.....

9---------------10
10---------------9

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-17 : 12:20:27

Can catch a circular reference like this:


DECLARE @employee TABLE (
	[ID] INT
	, [firstName] VARCHAR(255)
	, [surname] VARCHAR(255)
	, [managerID] INT
	)

INSERT @employee ([Id], [firstname], [surname], [managerID])
      SELECT 1, 'Fred', 'Flintstone', 2
UNION SELECT 2, 'Wilma', 'Flintstone', 3
UNION SELECT 3, 'Barney', 'Rubble', 4
UNION SELECT 4, 'Pebbles', 'Flintstone', 5
UNION SELECT 5, 'Bam Bam', 'Rubble', 4
UNION SELECT 6, 'Dino', 'Dino', 6

; WITH manager_tree AS (
SELECT
	e.[Id] AS [employeeID]
	, e.[Id] AS [stepID]
	, e.[managerId] AS [managerId]
	, 0 AS [level]
	, CAST(e.[firstName] + ' ' + e.[surname] AS VARCHAR(MAX)) AS [management Path]
	, CAST(e.[ID] AS VARCHAR(MAX)) AS [id Path]
FROM
	@employee e

UNION ALL SELECT
	mt.[employeeId]
	, e2.[Id] AS [stepId]
	, e2.[managerID] AS [managerId]
	, mt.[level] + 1 AS [level]
	, CAST(mt.[management Path] + ' - ' + e2.[firstName] + ' ' + e2.[surname] AS VARCHAR(MAX))
	, CAST(mt.[id Path] + ',' + CAST(e2.[ID] AS VARCHAR(MAX)) AS VARCHAR(MAX)) AS [id Path]
FROM
	manager_tree mt
	JOIN @employee e2 ON e2.[Id] = mt.[managerId]
WHERE
	e2.[Id] <> mt.[stepID]
	AND [id Path] NOT LIKE '%' + CAST(e2.[ID] AS VARCHAR(MAX)) + '%'
	)
SELECT
	mt.[employee]
	, mt.[Mangement Hierarchy]
FROM
	(
	SELECT
		e.[firstName] + ' ' + e.[surname] AS [Employee]
		, mt.[management Path] AS [Mangement Hierarchy]
		, ROW_NUMBER() OVER(PARTITION BY mt.[employeeID] ORDER BY [level] DESC) AS [pos]
	FROM
		manager_tree mt
		JOIN @employee e ON e.[ID] = mt.[employeeID]
	)
	mt
WHERE
	mt.[pos] = 1

Here Bam Bam and Pebbles are both the manager of each other (crazy) and Dino is his own manager.

If you have any data like this then watch out!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-06-18 : 01:46:35

Thanks Visakh and Charlie.

I want to be a master in T-SQL. What books should i go through?

Thanks again.

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-06-18 : 03:34:53

Is there a way to copy CTE to a table variable?

I tried it as follows:

Declare @tempTable table
(desnId int)
insert @tempTable values(SELECT DesnId FROM CTE)

Its not working.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-18 : 03:48:17

Declare @tempTable table
(desnId int)
insert @tempTable(DesnId)
SELECT DesnId FROM CTE

Madhivanan

Failing to plan is Planning to fail

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-06-18 : 04:13:33

Hello Madhivanan,
I am getting an error "Invalid Object name CTE"
My code is as follows:

Declare @tempTable table
(desnId int)

;With CTE (DesnID,Parent) AS
(SELECT Designation_Id,BelongsTo
FROM Designation_Master
WHERE Designation_Id=1
UNION ALL
SELECT t.Designation_Id,t.BelongsTo
FROM Designation_master t
INNER JOIN CTE c
ON c.DesnID=t.BelongsTo)

--SELECT DesnId FROM CTE
--select distinct fkAccessLevel from Designation_Master where Designation_id in (SELECT DesnId FROM CTE)
--select distinct fkdepartment_code from Designation_Master where Designation_id in (SELECT DesnId FROM CTE)

insert @tempTable(DesnId)
SELECT DesnId FROM CTE

SELECT * FROM @tempTable

GO

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-18 : 04:24:09

Does this work? It does on my 2005 box....


DECLARE @foo TABLE (
	[designation_ID] INT
	, [belongsTo] INT
	)

INSERT @foo ([designation_Id], [belongsTo])
      SELECT 1, NULL
UNION SELECT 2, 1
UNION SELECT 3, 1
UNION SELECT 4, 3

-- From Forum
Declare @tempTable table
(desnId int)

;WITH CTE (DesnID,Parent) AS
(SELECT Designation_Id,BelongsTo
FROM @foo
WHERE Designation_Id=1
UNION ALL
SELECT t.Designation_Id,t.BelongsTo
FROM @foo t
INNER JOIN CTE c
ON c.DesnID=t.BelongsTo)

--SELECT DesnId FROM CTE
--select distinct fkAccessLevel from Designation_Master where Designation_id in (SELECT DesnId FROM CTE) 
--select distinct fkdepartment_code from Designation_Master where Designation_id in (SELECT DesnId FROM CTE)
insert @tempTable(DesnId)
SELECT DesnId FROM CTE

SELECT * FROM @tempTable

GO

are you sure that the database you are running on is on a 2005 or up server and is in compat level 90 or greater?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-06-18 : 05:14:58

quote:
are you sure that the database you are running on is on a 2005 or up server and is in compat level 90 or greater?

Its working fine. I don't know what was the problem before, i am using Sql server 2005.

Thanks for your help.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-18 : 05:16:36

you're welcome.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-06-18 : 05:25:48

Hello Charlie,
If i UnComment the statement: SELECT DesnId FROM CTE ,
then i am getting the error: "Invalid object name 'CTE'"
What may be the reason?

DECLARE @foo TABLE (
[designation_ID] INT
, [belongsTo] INT
)

INSERT @foo ([designation_Id], [belongsTo])
SELECT 1, NULL
UNION SELECT 2, 1
UNION SELECT 3, 1
UNION SELECT 4, 3

-- From Forum
Declare @tempTable table
(desnId int)

;WITH CTE (DesnID,Parent) AS
(SELECT Designation_Id,BelongsTo
FROM @foo
WHERE Designation_Id=1
UNION ALL
SELECT t.Designation_Id,t.BelongsTo
FROM @foo t
INNER JOIN CTE c
ON c.DesnID=t.BelongsTo)

SELECT DesnId FROM CTE -- Statement is Uncommented
--select distinct fkAccessLevel from Designation_Master where Designation_id in (SELECT DesnId FROM CTE)
--select distinct fkdepartment_code from Designation_Master where Designation_id in (SELECT DesnId FROM CTE)
insert @tempTable(DesnId)
SELECT DesnId FROM CTE

SELECT * FROM @tempTable

GO

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-18 : 05:29:47

The common table expression only exists for the scope of one SELECT statement. Its being used by


SELECT DesnId FROM CTE -- Statement is Uncommented

and so doesn't exist when


insert @tempTable(DesnId)
SELECT DesnId FROM CTE

Is run.

Does that make sense? I'm not sure I explained it well.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

amodi
Yak Posting Veteran

83 Posts

Posted - 2009-06-18 : 05:38:27

quote:
Originally posted by Transact Charlie

The common table expression only exists for the scope of one SELECT statement. Its being used by

SELECT DesnId FROM CTE -- Statement is Uncommented

and so doesn't exist when

insert @tempTable(DesnId)
SELECT DesnId FROM CTE

Is run.

Does that make sense? I'm not sure I explained it well.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Absolutely, it makes sense. Your way of explanation simple and to the point.
Thanks.
Have a nice day

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources