Execute Stored Procedure or Dynamic SQL in a Function

Question

I am using SQL Server 2012 and am trying to convert data in a temp table to another temp table, converting values as I copy the info from one table to another. Here is an example of the input table:

#IMPORT_DATA
SSN             Plan                          StartDate
123-45-6789     Basic Life Insurance          1/1/2015
123-45-6789     Vision                        1/1/2015
123-45-6789     Dental                        1/1/2015

I need to convert this data to something like this:

#STAGE_DATA
SSN             Plan                          StartDate
123456789       BLI                           20150101
123456789       VIS                           20150101
123456789       DTL                           20150101

I have a translation table that defines a SQL script that will convert the data but am having an issue with executing dynamic SQL from within a function.

I am using a table-valued function to load #STAGE_DATA, passing in the #IMPORT_DATA as XML. The table function is:

create function dcBPI.TranslateBenefitsStagingTable_FN
(
      @XmlData xml
    , @TranslationType varchar(50)
    , @Company varchar(3) = null
)
returns @ReturnTable table (
      [RowID] [int]
    , [SSN] [varchar](9) NULL
    , [Plan] [varchar](3) NULL
    , [StartDate] [varchar](8) NULL
)
as

begin

    insert into @ReturnTable ([RowID], [EmpSSN]) 
    select [Table].[Column].value('ID[1]', '[int]') as 'RowID'   -- no translation for RowID
            , dcBPI.TranslateSourceValue_FN('Benefit', 'SSN', [Table].[Column].value('SSN[1]', '[varchar](11)'), @Company) as 'SSN'
            , dcBPI.TranslateSourceValue_FN('Benefit', 'Plan', [Table].[Column].value('Plan[1]', ' [varchar](3)'), @Company) as 'Plan'
            , dcBPI.TranslateSourceValue_FN('Benefit', 'StartDate', [Table].[Column].value('StartDate[1]', ' [varchar](10)'), @Company) as 'StartDate'

    from @XmlData.nodes('//row') as [Table]([Column])

    return 
end
go

The translation function is:

create function dcBPI.TranslateSourceValue_FN
(
      @TranslationType varchar(50)
    , @DestinationHeader varchar(255)
    , @SourceValue varchar(255)
    , @Company varchar(3) = null
)
returns varchar(255)
as
begin
    declare   @DestinationValue varchar(255) = 'Missing'
            , @sql nvarchar(max) = ''

    if len(rtrim(@Company)) = 0 set @Company = null

    select  @sql = 
            select
                    distinct td.DestinationValue
            from    dcBPI.TranslationData td    
            where   (
                        td.Company = @Company 
                        or td.Company = 'All'
                    )
                    and td.TranslationType = @TranslationType
                    and td.DestinationHeader = @DestinationHeader
                    and td.SourceValue = @SourceValue

    )

    exec sp_executesql @sql, N'@OutputValue nvarchar(max) OUTPUT', @OutputValue = @DestinationValue OUTPUT

    return @DestinationValue

end
go

When I execute the table-valued function (which in turn executes TranslateSourceValue_FN), I get an error message of:

Only functions and some extended stored procedures can be executed from within a function.

I know from other articles that I cannot call Dynamic SQL from a function, so is there another way to accomplish what I am trying to do?

EDIT (added @sql examples)
The SQL that is to be used are simple select statements like:

• select replace('123-45-6789' '-', '')
• select PlanCode from CodeTable where Plan = @Plan'

Answer 1

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

1. 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
   

2. 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
   

3. 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
   

4. 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
   

5. 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.