Recursive Stored Procedure?

[andegre]

I'm having trouble getting the steps down on how to set up my recursive stored procedure...here's my problem.

I have a list of items with about 6 pieces of data per record...IF...one of the records has a 'RaisedEvent' field that contains a value (not null), then call the another (or the same) stored procedure to get the values for that.

Here's my issue...there will be two different types of input parameters for this issue. The one that calls the first SP is with a date range, if that 'RaisedEvent' value is found in any record, then it needs to call another stored procedure using paramters of just the event's number.

Can I use to separate SP's for this problem and just have each of them write to a temp table? How can I do the check for that specific value in the middle of the first SP.

Thanks everyone...(This is my first try with Recursion in SQL)

 

[jbenson001]

Can you show some sample data, and what you would like the results to be?

Jim

 

[andegre]

Sample Data:

ID, EventRaisedID, Cust_Num, Cust_Name, Cust_ID_Num, Description.

This is for one record, if the EventRaisedID value is not null, then I need to go through and get the next ID that is equal to the EventRaisedID.

ie if they have a kid, the kid's ID is in the EventRaisedID field.

I have to grab the initial values in a date range, but for the EventRaisedID, the date range does not matter, it could be in any timeframe.

Hope this gives you a better understanding of my issue.

Thanks

 

[jbenson001]

I understand the steps, what I would like to see is how you want your final result set formatted. Columns etc...

 

[andegre]

Those are the fields that I am going to output. There are a bunch of joins before this where I get these values.

I hope I'm not breaking rules by posting this, but here is what I have so far...

CREATE Procedure RPTDailySanctions
/* Param List */
(
@FromDate datetime
@ThruDate datetime
}
AS

SET NOCOUNT ON

Insert Into ##temp_Sanctions
Select ce.CustomerEventID,
ceh.RelatedCustomerEventID,
ce.Cust_No,
cs.CustomerName,
Case
When cs.DriversLicense Is Null
Then cs.SSN
Else
cs.DriversLicense
End As [DLN],
src.LookupValue As [SancReasonCode],
src.[Description]
From CustomerEvent ce
Inner Join IARTS20..CSPEED cs On cs.CustomerNumber = ce.Cust_No
Inner Join CustomerSanctionDetail csd On csd.CustomerEventID = ce.CustomerEventID
Inner Join SanctionReasonCode src On src.SanctionReasonCodeID = csd.SanctionReasonCodeID
Left Outer Join CustomerEventHierarchy ceh On ceh.CustomerEventID = ce.CustomerEventID
Where ce.CreatedDate > '2006-06-14'
And ce.CreatedDate < '2006-06-15'
And ce.CreatedBy = 'qabatch'

DECLARE @Event int

--Not sure about the next line...don't know which table to draw from
DECLARE curRecurse CURSOR LOCAL FOR SELECT RelatedCustomerEventID FROM ##temp_Sanctions WHERE RelatedCustomerEventID IS NOT NULL

Open curRecurse
Fetch Next From curRecurse Into @Event
While (@@Fetch_Status = 0)
Begin
if @Event IS NOT NULL
Exec --The recursed stored procedure
Else
Fetch Next From curRecurse Into @Event
End
Close curRecurse
Deallocate curRecurse

If (@@Nestlevel = 1)
Begin
Select * From ##temp_Sanctions
Drop Table ##temp_Sanctions
End


If this breaks rules, please delete...

 

[andegre]

The stuff in the select is how I want it output...

 

[andegre]

Problem Solved...thanks anyway