Recursive Stored Procedure

Reply

eric_tcp Posted: 10-03-2007 6:11 PM

rated by 0 users

Hi all,

Any one out there familiar with Stored Procedure can  advise me on how to create recursive stored procedure and returns the data?

In my table there are data which have higher level, eg:

Manager > Supervisor > Staff

I like to pass in the manager id, and i able to get the supervisor and staff id

.

Thanks. 

Reply

bronkman replied on 10-04-2007 1:43 AM

rated by 0 users

It is a little unusual to be using recursive stored procedure when you can actually query to get the results in a single stored procedure. So, I assume that you are going to pass only the manager ID, which results in a list of supervisor IDs, and for each supervisor, which in turn results in a list of staff IDs?

Anyways, to call a stored procedure within a stored procedure, try this:

DECLARE @SupID INT
EXEC @SupID = uspGetSupervisorID @ManagerID

But looping in SQL is not advisable and should be avoided. Performance will be better if you just simply use a single statement to get the data you need. If assuming the data is stored in a single table tblUser (where SupID mean superior ID), try this:

SELECT man.UserID AS ManagerID, sup.UserID AS SupervisorID, stf.UserID AS StaffID
FROM tblUser AS man JOIN tblUser AS sup ON man.UserID = sup.SupID
JOIN tblUser AS stf ON sup.UserID = stf.SupID
WHERE man.UserID = @ManagerID

Reply

icelava replied on 10-26-2007 11:23 AM

rated by 0 users

You may wanna check out Common Table Expressions (CTE) and see if it helps your purposes.

http://www.4guysfromrolla.com/webtech/071906-1.shtml