CODEWITHSUNDEEP
sqlsql-serversql-server-2014
NullPointer
NullPointer

Reputation: 2184

Need to call table function recursively

I'm using SQL Server 2014. I have a table function which takes 2 parameters and returns 2 columns. For the sake of simplicity, let's call the function Get_GroupNumber.

Get_GroupNumber('Smith', '123-456-789')

This will return 2 columns. Again keeping it simple let's say it is Reason1 and Reason2.

Reason1 = '111-222-333'
Reason2 = '555'666'777'

I need to pass Reason1 which is returned back into Get_GroupNumber, and keep doing so until it returns a NULL in Reason1 and also NULL in Reason2.

So, the second call would look like this:

Get_GroupNumber('Smith','111-222-333')

Upvotes: 3

Views: 117

Answers (2)

NullPointer
NullPointer

Reputation: 2184

Using While Loop

DECLARE @key varchar(max);
DECLARE @retVal varchar(max);
SET @key = '123-456-789'
While @Key is not null
BEGIN
    set @retval = @key
    SET @key = (SELECT Return2 from [dbo].[Get_GroupNumber]('Smith',@key))
END;

print @retVal

I know I'm only using the return value from column Return2 to test here

Upvotes: 0

Joe Farrell
Joe Farrell

Reputation: 3542

If you don't want to use an iterative approach (e.g., with a while loop), then a recursive CTE can do the job. You can read more about recursive CTEs here, or check out this example:

-- Here's a "dummy" function that has some hard-coded return values for illustrative
-- purposes. It will return ('111-222-333', '555-666-777') when it gets 123-456-789
-- as its second parameter, ('999-999-999', '000-000-000') when it gets 111-222-333
-- as its second parameter, and (null, null) otherwise.
create function Get_GroupNumber
(   
    @Param1 varchar(32),
    @Param2 varchar(32)
)
returns table
as
return 
(
    select
        Reason1 = case @Param2 when '123-456-789' then '111-222-333' when '111-222-333' then '999-999-999' else null end,
        Reason2 = case @Param2 when '123-456-789' then '555-666-777' when '111-222-333' then '000-000-000' else null end
);
go

-- The sample inputs from your question.
declare @Param1 varchar(32) = 'Smith';
declare @Param2 varchar(32) = '123-456-789';

-- And finally, the good stuff:
with GroupNumberCTE as
(
    -- Base case: pass the original parameters, get the results from the function, and
    -- create a new field called "Call #" that will illustrate the order in which the
    -- various calls to Get_GroupNumber were made. (This field is purely informational;
    -- you can remove it if you don't want it.)
    select 
        [Call #] = 1,
        Reason1, 
        Reason2 
    from 
        Get_GroupNumber(@Param1, @Param2)

    union all

    -- Recursive case: if the previous call returned a non-null value in either field,
    -- invoke the function again with the original @Param1 and the Reason1 from the
    -- previous call. 
    select
        [Call #] = [Previous].[Call #] + 1,
        [Next].Reason1,
        [Next].Reason2
    from
        GroupNumberCTE [Previous]
        cross apply Get_GroupNumber(@Param1, [Previous].Reason1) [Next]
    where
        [Previous].Reason1 is not null or
        [Previous].Reason2 is not null
)
select * from GroupNumberCTE;

The result set:

Call #   Reason1       Reason2
----------------------------------
1        111-222-333   555-666-777
2        999-999-999   000-000-000
3        NULL          NULL

I should point out that there's a danger here: there is nothing in the structure of my CTE itself that guarantees that the recursion will eventually end. So you'll have to be sure that for any feasible set of initial inputs, your implementation of Get_GroupNumber will eventually return two nulls. As long as that is so, this approach should work well.

Upvotes: 4

Related Questions