Execute table valued function from row values

Question

Given a table as below where fn contains the name of an existing table valued functions and param contains the param to be passed to the function

fn       | param
----------------
'fn_one' | 1001
'fn_two' | 1001
'fn_one' | 1002
'fn_two' | 1002

Is there a way to get a resulting table like this by using set-based operations? The resulting table would contain 0-* lines for each line from the first table.

param | resultval
---------------------------
1001  | 'fn_one_result_a'
1001  | 'fn_one_result_b'
1001  | 'fn_two_result_one'
1002  | 'fn_two_result_one'

I thought I could do something like (pseudo)

select t1.param, t2.resultval
from table1 t1
cross join exec sp_executesql('select * from '+t1.fn+'('+t1.param+')') t2

but that gives a syntax error at exec sp_executesql.

Currently we're using cursors to loop through the first table and insert into a second table with exec sp_executesql. While this does the job correctly, it is also the heaviest part of a frequently used stored procedure and I'm trying to optimize it. Changes to the data model would probably imply changes to most of the core of the application and that would cost more then just throwing hardware at sql server.

Answer 1

I believe that this should do what you need, using dynamic SQL to generate a single statement that can give you your results and then using that with EXEC to put them into your table. The FOR XML trick is a common one for concatenating VARCHAR values together from multiple rows. It has to be written with the AS [text()] for it to work.

--=========================================================
-- Set up
--=========================================================
CREATE TABLE dbo.TestTableFunctions (function_name VARCHAR(50) NOT NULL, parameter VARCHAR(20) NOT NULL)
INSERT INTO dbo.TestTableFunctions (function_name, parameter)
VALUES ('fn_one', '1001'), ('fn_two', '1001'), ('fn_one', '1002'), ('fn_two', '1002')

CREATE TABLE dbo.TestTableFunctionsResults (function_name VARCHAR(50) NOT NULL, parameter VARCHAR(20) NOT NULL, result VARCHAR(200) NOT NULL)
GO
CREATE FUNCTION dbo.fn_one
(
    @parameter VARCHAR(20)
)
RETURNS TABLE
AS
RETURN
    SELECT 'fn_one_' + @parameter AS result
GO
CREATE FUNCTION dbo.fn_two
(
    @parameter VARCHAR(20)
)
RETURNS TABLE
AS
RETURN
    SELECT 'fn_two_' + @parameter AS result
GO

--=========================================================
-- The important stuff
--=========================================================

DECLARE @sql VARCHAR(MAX)

SELECT @sql = 
(
    SELECT 'SELECT ''' + T1.function_name + ''', ''' + T1.parameter + ''', F.result FROM ' + T1.function_name + '(' + T1.parameter + ') F UNION ALL ' AS [text()]
    FROM
        TestTableFunctions T1
    FOR XML PATH ('')
)

SELECT @sql = SUBSTRING(@sql, 1, LEN(@sql) - 10)

INSERT INTO dbo.TestTableFunctionsResults
EXEC(@sql)

SELECT * FROM dbo.TestTableFunctionsResults

--=========================================================
-- Clean up
--=========================================================
DROP TABLE dbo.TestTableFunctions
DROP TABLE dbo.TestTableFunctionsResults
DROP FUNCTION dbo.fn_one
DROP FUNCTION dbo.fn_two
GO

The first SELECT statement (ignoring the setup) builds a string which has the syntax to run all of the functions in your table, returning the results all UNIONed together. That makes it possible to run the string with EXEC, which means that you can then INSERT those results into your table.

A couple of quick notes though... First, the functions must all return identical result set structures - the same number of columns with the same data types (technically, they might be able to be different data types if SQL Server can always do implicit conversions on them, but it's really not worth the risk). Second, if someone were able to update your functions table they could use SQL injection to wreak havoc on your system. You'll need that to be tightly controlled and I wouldn't let users just enter in function names, etc.

Answer 2

You cannot access objects by referencing their names in a SQL statement. One method would be to use a case statement:

select t1.*,
       (case when fn = 'fn_one' then dbo.fn_one(t1.param)
             when fn = 'fn_two' then dbo.fn_two(t1.param)
        end) as resultval
from table1 t1 ;

Interestingly, you could encapsulate the case as another function, and then do:

select t1.*, dbo.fn_generic(t1.fn, t1.param) as resultval
from table1 t1 ;

However, in SQL Server, you cannot use dynamic SQL in a user-defined function (defined in T-SQL), so you would still need to use case or similar logic.

Either of these methods is likely to be much faster than a cursor, because they do not require issuing multiple queries.