Call dynamic function name in SQL

Question

Is it possible to call a function with a dynamic name in SQL?

For example:

SELECT functionid, (SELECT results FROM dbo.Function_*functionid*) AS results
FROM List_of_Functions

This would call a different function for every row in the table List_of_Functions.

Or am I going about this all wrong?

score 3 · Accepted Answer · answered May 11 '12 at 19:48

3

You will need to build (either type it in, or build it dynamically based on your table) a SQL statement like:

SELECT
    functionid
        ,CASE  functionid
            WHEN 1 THEN dbo.Function_1()
            WHEN 2 THEN dbo.Function_2()
            WHEN 3 THEN dbo.Function_3()
         END AS results
    FROM List_of_Functions

Instead of building all those functions, wouldn't it be better to build one function, and pass in a value that the function can use to differentiate the processing? like:

SELECT
    functionid
        ,dbo.Function(functionid) AS results
    FROM List_of_Functions_Parameters

answered May 11 '12 at 19:48

KM.

101,727
34
178
212

Do you know if there will be any difference in performance for method 1 and 2? – William May 11 '12 at 19:57
@William, I have no idea how many functions you have or what they are actually doing. thus, saying anything about performance would be a complete guesses. – KM. May 11 '12 at 20:12

score 2 · Answer 2 · answered May 11 '12 at 19:13

2

Is it possible to call a function with a dynamic name in SQL?

Not in pure SQL.

You can achieve this using dynamic SQL, but not without some risk, in particular that of SQL Injection.

I suggest reading The Curse and Blessings of Dynamic SQL by Erland Sommarskog for a comprehensive treatment of the subject.

answered May 11 '12 at 19:13

Oded

489,969
99
883
1,009

But in the query I posted, the dynamic part of the functions name is coming from a value in SQL, not user input so how could anything be injected? – William May 11 '12 at 19:26
@William - And if something unwanted goes into your `List_of_Functions`? I am not saying it is a risk in _all_ cases, but that generally when using dynamic SQL, you need to keep this risk in mind. – Oded May 11 '12 at 19:28
In this case List_of_Functions is created by me so that isn't an issue. So this isn't possible at all with pure SQL? Do you know of any other ways I could approach this problem? – William May 11 '12 at 19:50
@William - Dynamic SQL is one of the easiest options. – Oded May 11 '12 at 19:51
@Oded In case of function Dynamic SQL is not the only option: https://stackoverflow.com/a/50773766/5070879 – Lukasz Szozda Jun 09 '18 at 11:26

Lukasz Szozda · Answer 3 · 2018-06-09T11:32:51.200

Just want to extend and oppose Oded's answer:

Is it possible to call a function with a dynamic name in SQL?

Not in pure SQL.

You can achieve this using dynamic SQL, but not without some risk, in particular that of SQL Injection.

Yes, it is possible without using dynamic SQL.

Preparing:

CREATE TABLE List_of_Functions(functionid INT);
INSERT INTO List_of_functions(functionid) VALUES(1),(2),(3),(4);

CREATE FUNCTION Function_1()
RETURNS VARCHAR(100)
AS
BEGIN
   RETURN 'Return from Function_1';
END;

CREATE FUNCTION Function_2()
RETURNS VARCHAR(100)
AS
BEGIN
   RETURN 'Return from Function_2';
END;

CREATE FUNCTION Function_3()
RETURNS VARCHAR(100)
AS
BEGIN
   RETURN 'Return from Function_3';
END;

Core function:

CREATE FUNCTION Function_dispatcher(@name SYSNAME)
RETURNS VARCHAR(100)
AS
BEGIN
  DECLARE @r VARCHAR(100);
  IF OBJECT_ID(@name, N'FN') IS NULL  --handling non-existing function
    RETURN NULL;
  EXEC @r = @name; 
  RETURN @r;
END;

And final call:

SELECT *, dbo.Function_dispatcher(s.n) AS result
FROM List_of_Functions lf
OUTER APPLY(SELECT CONCAT('dbo.Function_', lf.functionid)) s(n);

DBFiddle Demo

Output:

┌────────────┬────────────────┬────────────────────────┐
│ functionid │       n        │         result         │
├────────────┼────────────────┼────────────────────────┤
│          1 │ dbo.Function_1 │ Return from Function_1 │
│          2 │ dbo.Function_2 │ Return from Function_2 │
│          3 │ dbo.Function_3 │ Return from Function_3 │
│          4 │ dbo.Function_4 │ null                   │
└────────────┴────────────────┴────────────────────────┘

Using this approach you can avoid of changing main function every time new function will appear(CASE WHEN ...) like in KM.'s answer

Just wanted to add that it is possible to use this approach also for cases when the functions takes an argument. example: `CREATE FUNCTION Function_dispatcher(@name SYSNAME, @someArgument INT) RETURNS VARCHAR(100) AS BEGIN DECLARE @r VARCHAR(100); IF OBJECT_ID(@name, N'FN') IS NULL RETURN NULL; EXEC @r = @name @someArgument; RETURN @r; END;` — Lars Johannesson, Oct 28 '20 at 12:15

Call dynamic function name in SQL

3 Answers3

Linked