需要调用表函数

Question

我有一个表函数，它需要2个参数并返回2列。为了简便起见，让调用函数Get_GroupNumber

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

这将返回2列，再次，保持简单说，这是Reason1和Reason2现在

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

，我需要做的是将Reason1返回到Get_GroupNumber，并继续执行，直到它在Reason1中返回NULL，并在Reason2中返回NULL。

所以，第二个电话应该是这样

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

有没有办法做到这一点？我使用SQL Server提前

Answer 1

谢谢如果你不想使用迭代的方法（例如，具有while循环），那么递归CTE可以做的工作。你可以阅读更多有关递归CTE的here，或看看这个例子：

-- 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; 

结果集：

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

我应该指出的是，这里有一个危险：没有什么结构我的CTE本身保证递归最终会结束。因此，您必须确保对于任何可行的初始输入集合，您实施Get_GroupNumber最终将返回两个空值。只要是这样，这种方法应该运作良好。

Answer 2

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 

我知道，我只使用从柱RETURN2的返回值来这里测试