Call Dynamic SQL from Function

Call a Stored Procedure or Function from Dynamic SQL - with Nullable Parameter

Just escape the NULL value with an explicit literal NULL, making sure that the quotes are only included when the value is not NULL.

DECLARE @myParameter VARCHAR(10) = 'ABC'

DECLARE @dynamicQuery VARCHAR(MAX)

SET @dynamicQuery = 
   'select * from [qlik].udf_getStatistic(' + ISNULL('''' + @myParameter + '''', 'NULL') + ')'


SELECT @dynamicQuery -- select * from [qlik].udf_getStatistic('ABC')

SET @myParameter = NULL

SET @dynamicQuery = 
   'select * from [qlik].udf_getStatistic(' + ISNULL('''' + @myParameter + '''', 'NULL') + ')'

SELECT @dynamicQuery -- select * from [qlik].udf_getStatistic(NULL)

You might want to escape additional single quotes that might be on your variable, replacing them with double single quotes, so it doesn't break your dynamic build.

Call dynamic SQL from function

You can't use dynamic sql in a udf:

This very simple: you cannot use dynamic SQL from used-defined
functions written in T-SQL. This is because you are not permitted do
anything in a UDF that could change the database state (as the UDF may
be invoked as part of a query). Since you can do anything from dynamic
SQL, including updates, it is obvious why dynamic SQL is not
permitted.

...

In SQL 2005 and later, you could implement your function as a CLR
function. Recall that all data access from the CLR is dynamic SQL.
(You are safe-guarded, so that if you perform an update operation from
your function, you will get caught.) A word of warning though: data
access from scalar UDFs can often give performance problems.

How can I turn this dynamic query into a procedure or function?

You can create scalar user defined function, which returns the sql statement.

CREATE FUNCTION dbo.udf_GenerateSelectQuery() 
Returns nvarchar(max)
AS
BEGIN
declare @t table( tablename SYSNAME) 
declare @sql Nvarchar(max)

set @sql = ''
insert into @t
SELECT t.TABLE_NAME AS table_name FROM INFORMATION_SCHEMA.TABLES AS t

SELECT @sql = @sql + 'Select convert(varchar(5),svc_defs.svc_id) as svcid, data_id, crid, d_custid, d_active From ' + tablename + 
' inner join svc_defs on svc_defs.svc_table = ' + '''' + tablename + '''' + ' union ' from @t 

--remove the trailing 'union'
Select @sql = substring(@sql, 1, len(@sql) - 6)

RETURN @sql
END

you can call it as

declare @sqlstmt NVARCHAR(max) =  dbo.udf_GenerateSelectQuery()
SELECT @sqlstmt

or as

declare @sqlstmt NVARCHAR(max) 
SET @sqlstmt = (SELECT dbo.udf_GenerateSelectQuery())
SELECT @sqlstmt

Execute Stored Procedure or Dynamic SQL in a Function

Since you just need to execute some dynamic SQL, and that Dynamic SQL is simple SELECT statement, you can use the following SQLCLR code to replace your call to sp_excutesql with just:

SET @DestinationValue = dbo.SimpleSelect(@sql);

The C# code for this simple function is:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class TheFunction
{
    [return: SqlFacet(MaxSize = 4000)]
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read,
        SystemDataAccess = SystemDataAccessKind.Read)]
    public static SqlString SimpleSelect(
        [SqlFacet(MaxSize = 4000)] SqlString TheQuery)
    {
        string _Output = null;

        if (TheQuery.Value.Trim() == String.Empty)
        {
            return new SqlString(_Output);
        }

        using (SqlConnection _Connection =
            new SqlConnection("Context Connection = true;"))
        {
            using (SqlCommand _Command = _Connection.CreateCommand())
            {
                _Command.CommandType = CommandType.Text;
                _Command.CommandText = TheQuery.Value;

                _Connection.Open();
                _Output = _Command.ExecuteScalar().ToString();
            }
        }

        return new SqlString(_Output);
    }
}

Because it uses the in-process Context Connection, the Assembly can remain marked as SAFE and the database does not need to be set to TRUSTWORTHY ON. But it also means that you are bound by all of the other restrictions that T-SQL functions have, things like: can't use NEWID(), can't change the state of the database, can't use RAISERROR or SET statements, etc.

The following code is the T-SQL wrapper object that references the C# method shown above. Please note the RETURNS NULL ON NULL INPUT at the end of the 2nd line. This is not something that Visual Studio / SSDT allows you to set, so it must be done manually by running the code below.

CREATE FUNCTION [dbo].[SimpleSelect](@TheQuery [nvarchar](4000))
RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER, RETURNS NULL ON NULL INPUT
AS EXTERNAL NAME [DynamicSqlForFunctions].[TheFunction].[SimpleSelect];

EXAMPLES

The following two examples both return NULL. Please note that the first example shown passes in a NULL, and there is no handling for TheQuery.IsNull in the C# method, yet there is no "Object not set to a reference..." error. That is all thanks to the magic of the RETURNS NULL ON NULL INPUT option specified in the CREATE FUNCTION.
```
SELECT dbo.SimpleSelect(NULL);
-- NULL

SELECT dbo.SimpleSelect('');
-- NULL
```
The following example shows that you can select pretty much any data type and are not required to covert it to NVARCHAR in order to have it work.
```
SELECT dbo.SimpleSelect('SELECT GETDATE();');
-- 10/12/2015 10:25:33 AM
```

The following shows passing in a multi-statement query:

DECLARE @SQL NVARCHAR(4000);
SET @SQL = N'
DECLARE @TempSum INT;
SET @TempSum = 0;
SELECT TOP(80) @TempSum = so.[object_id]
FROM   [master].[sys].[objects] so
SELECT @TempSum;
';

SELECT dbo.SimpleSelect(@SQL);
-- 133575514

The following example shows that we can execute a simple, read-only Stored Procedure via this Function. That is not something that can be done in a T-SQL Function. But, like T-SQL Functions, it cannot run side-effecting statements, which is why there is no SET NOCOUNT ON; in the Stored Procedure.
So first run this:
```
CREATE PROCEDURE #SimpleProcTest
AS
SELECT TOP(5) so.[name], so.[type_desc]
FROM   [master].[sys].[objects] so
ORDER BY so.[name] ASC;
GO
```
Then, if you want to test it, run this:
```
EXEC #SimpleProcTest;
```
Finally, here we do the full test which includes creating a table variable, dumping the results of the Stored Procedure into that table variable.
```
DECLARE @TestSQL NVARCHAR(4000) = N'
DECLARE @Bob TABLE (
[name] sysname,
[type_desc] NVARCHAR(60)
);

INSERT INTO @Bob
   EXEC #SimpleProcTest;

SELECT COUNT(*)
FROM @Bob
WHERE PATINDEX(N''%[0-9]%'', [name]) > 0;
';

SELECT dbo.SimpleSelect(@TestSQL);
-- 2
```

And for the last example, we see again that we are bound by most of the same restrictions that T-SQL Functions have placed on them.

SELECT dbo.SimpleSelect('SELECT NEWID();');
-- Msg 6522, Level 16, State 1, Line 1
-- Invalid use of a side-effecting operator 'SELECT WITHOUT QUERY' within a function.

Call dynamic function name in SQL

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

use function call in dynamic SQL statement

if you are using 12c and you are only using this function for this sql statement you could use a PL/SQL declaration section in the WITH clause of your sql statement

Declare
myvariable number;
Begin

    EXECUTE IMMEDIATE'
    WITH
      FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
      BEGIN
        RETURN p_id * p_id - p_id;
      END;
    SELECT with_function(3)
    FROM   dual
    WHERE  rownum = 1' into myvariable;
    dbms_output.put_line('myvariable: '||myvariable);
    End;

Writing a dynamic SQL function

Have a try with this one:

CREATE PROCEDURE dbo.SEL_PCD
(
    @COBDate DATETIME,
    @FileName VARCHAR(50),
    @PC VARCHAR(50),
    @MyList VARCHAR(max)
) AS

    DECLARE @SQL VARCHAR(max)

    SELECT @SQL = 'SELECT * FROM 
                    (SELECT tab1.TID FROM
                        (SELECT TID FROM dbo.SEL_RT('+@COBDate+','+@FileName+') WHERE BID IN ('+ @MyList +')) tab1
                    JOIN
                        (SELECT TID FROM CT WHERE (Col_Name LIKE %' + @PC + '% OR Bk LIKE %' + @PC + '%) AND FileName = ' + @FileName + ' AND COBDate = @COBDate) tab2
                    ON tab1.TID = tab2.TID) tab3
                JOIN
                    (SELECT TID, Value FROM CR WHERE BID IN (' + @MyList + ') AND COBDate = ' + @COBDate + ' AND FileName = ' + @FileName + 'AND ScenID = 266) tab7
                ON tab3.TID = tab7.TID'

EXEC(@SQL)

Functions

can be used with Select statement
Not returning output parameter but returns Table variables
You can join UDF
Cannot be used to change server configuration
Cannot be used with XML FOR clause
Cannot have transaction within function

Stored Procedure

have to use EXEC or EXECUTE
return output parameter
can create table but won’t return Table Variables
you can not join SP
can be used to change server configuration
can be used with XML FOR Clause
can have transaction within SP

Executing dynamic SQL in a SQLServer 2005 function

It "ordinarily" can't be done as SQL Server treats functions as deterministic, which means that for a given set of inputs, it should always return the same outputs. A stored procedure or dynamic sql can be non-deterministic because it can change external state, such as a table, which is relied on.

Given that in SQL server functions are always deterministic, it would be a bad idea from a future maintenance perspective to attempt to circumvent this as it could cause fairly major confusion for anyone who has to support the code in future.