Recursive Stored Procedure - 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)

Recursive Stored Procedure

Author

Topic

ashokvardhan
Starting Member

4 Posts

Posted - 2007-05-23 : 04:18:49

Hi, I have a table like this and i need the output in the below mentioned format. This info is WRT to your old recursive procedure.

CREATE PROCEDURE ReportList
@iCode int
AS
SET NOCOUNT ON
DECLARE @iReferID int
IF NOT EXISTS( SELECT 1 FROM sysobjects WHERE NAME = 'ReferralOutPut' )
CREATE TABLE ReferralOutput( Code int, ReferID int )
SELECT @iReferID = ReportingEmpCode FROM ReportMapper where EmployeeCode = @iCode
IF @iReferID IS NOT NULL
BEGIN
INSERT INTO ReferralOutput SELECT @iCode, @iReferID
execute ReportList @iReferID
END
SELECT ReferID FROM ReferralOutput
-- DROP TABLE ReferralOutput
GO

refererid code
201 204
203 206
204 207
205 208
207 209
206 210
210 214
210 213
207 300

refererid code
201 204
204 207
207 209
207 300

But i am getting only the below output

refererid code
201 204
204 207
207 209

Regards
Ashok

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-05-23 : 07:22:05

Hi Ashok,
can u post sample data, desired output for ReportMapper table.
Why do u need recursive loop?

ashokvardhan
Starting Member

4 Posts

Posted - 2007-05-25 : 05:14:45

Hi Peter,

I have alreay mentioned sample data in my earlier mail. Any way here is the sample data in the reportmapper table which has two columns refererid & code
refererid code
201 204
203 206
204 207
205 208
207 209
206 210
210 214
210 213
207 300

Expected Output

refererid code
201 204
204 207
207 209
207 300

But i am getting only the below output

Incomplete output
refererid code
201 204
204 207
207 209

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-25 : 05:21:36

ur recursive function will work fine if ur referid column is unique..but it has duplicate values so it find the first return row (here 207 is having two entries so the first 207 entry will be taken for that 209 is code and it will seach for 209 referid not 207 again....

u have to change the logic.

ashokvardhan
Starting Member

4 Posts

Posted - 2007-05-30 : 04:37:52

Hi,

Thanx for the reply, I am implementing this in terms of organizational hierarchy. I am treating referid as boss and code as subordinate. So, the criteria is two people will be reporting to a person i.e., nothing but 207 (refer id).

Regards
Ashok

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources