Getting an Error When Executing a Dynamic SQL Within a Function (SQL Server)

Getting an error when executing a dynamic sql within a function (SQL Server)?

You cannot use dynamic SQL from a function, neither can you call
stored procedures.

Create proc GetPrePopValue(@paramterValue nvarchar(100))
as
begin
declare @value nvarchar(500),
        @SQLString nvarchar(4000)

Set @SQLString = 'Select @value = Grant_Nr From Grant_Master where grant_id = @paramterValue'

exec sp_executesql @SQLString, N'@paramterValue nvarchar(100)', 
       @paramterValue, 
       @value = @value output

return @value   
end 

BEGIN TRY and EXECUTE dynamic simple query error

This is a bug. I have reported it here

It is not clear why you need this query, the following explains the issue and how to avoid it if there is some reasonable reason for needing this query.

The following query returns two object_ids -103085222 and -593

SELECT object_id ,name
FROM sys.system_columns
where system_type_id in (35,99,167,175,231,239) and collation_name IS NULL

These relate to the objects below

+------------+----------------------------------+
|     id     |               name               |
+------------+----------------------------------+
| -103085222 | pdw_nodes_pdw_physical_databases |
|       -593 | pdw_table_mappings               |
+------------+----------------------------------+

For some reason both of these have a string column (called physical_name) that SQL Server can not resolve the collation for. As these objects are only relevant to Parallel Data Warehouse and don't even properly exist in other versions of the product this is not usually a problem.

sys.all_columns references the sys.system_columns view which uses the following expression for collation_name : convert(sysname, ColumnPropertyEx(object_id, name, 'collation'))

A simpler case that reproduces the issue is

BEGIN TRY
    SELECT columnpropertyex(-593, 'physical_name', 'collation')
END TRY
BEGIN CATCH
END CATCH 

Or

SET XACT_ABORT ON;
SELECT columnpropertyex(-593, 'physical_name', 'collation')

Both return

Msg 0, Level 11, State 0, Line 33

A severe error occurred on the
current command. The results, if any, should be discarded.

When run outside the TRY block this returns NULL (when XACT_ABORT is off). Behind the scenes it throws an exception that is handled internally and the end user is unaware. The call stack when the exception is thrown shows that GetColumnCollate function ends up using the Algebrizer to try and resolve the column and it eventually fails in CAlgTableMetadata::RaiseBadTableException (possibly as a related object definition is missing somewhere).

When run in the TRY ... CATCH context or with XACT_ABORT ON something goes wrong with the bit that is supposed to silently ignore the error and return NULL. Inserting a duplicate key into an index with ignore_dup_key ON also raises an internal error that is ignored but does not have the same problem.

So one way of resolving the issue is to wrap the reference to collation_name in a CASE expression so it is never evaluated for those two problematic object_id when run inside the TRY block.

  BEGIN TRY 
    DECLARE  @vSqlCmd NVARCHAR(MAX) = N'
    SELECT TOP 1 V = ca.safe_collation_name, C = COUNT(1) OVER (PARTITION BY ca.safe_collation_name)
    FROM sys.all_columns d
    CROSS APPLY (SELECT CASE WHEN object_id NOT IN (-593,-103085222) THEN collation_name END) ca(safe_collation_name);
    ';
    SELECT @vSqlCmd;

    EXECUTE (@vSqlCmd);
END TRY
BEGIN CATCH
    PRINT 'CATCH!!!';

    THROW;
END CATCH

This does not protect you against different cases of problematic metadata being added to the product in the future. Again I question the need for this query however. The query you have provided is so odd that it is difficult to advise you what you should replace it with.

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.

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

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.
   

Dynamic query in stored procedure doesn't return an error but fails to insert record

One problem I can see (Thanks to @GuidoG) is that if @param1 or @param2 are NULL your @QRY string will be NULL.

You could replace these lines...

        SET @QRY = @QRY + ' DECLARE @param1 nvarchar(MAX) = ' + CHAR(39) + ISNULL(@param1, 'NULL') + CHAR(39) + ';'
        SET @QRY = @QRY + ' DECLARE @param2 nvarchar(MAX) = ' + CHAR(39) + ISNULL(@param2, 'NULL') + CHAR(39) + ';'

Personally, I'd skip the parameter parts anyway. You're not escaping them, so are open to SQL Injection attacks and/or unexpected failures.

sp_executesql allows you to avoid those problems...

        SET @QRY = @QRY + ' INSERT INTO tableName (column1, column2' 
        IF @param3 is not null
            SET @QRY = @QRY + ', column3'
        IF @param4 is not null
            SET @QRY = @QRY + ', column4'
        IF @param5 is not null
            SET @QRY = @QRY + ', column5'
        SET @QRY = @QRY + ') VALUES '
        SET @QRY = @QRY + ' (@param1, @param2 '
        IF @param3 is not null
            SET @QRY = @QRY + ', @param3'
        IF @param4 is not null
            SET @QRY = @QRY + ', @param4'
        IF @param5 is not null
            SET @QRY = @QRY + ', @param5'
        SET @QRY = @QRY + ')'

        EXEC sp_executesql
          @QRY,  
          N'@param1 NVARCHAR(MAX),
            @param2 NVARCHAR(MAX),
            @param3 NVARCHAR(MAX),
            @param4 NVARCHAR(MAX),
            @param5 NVARCHAR(MAX)',
          @param1,
          @param2,
          @param3,
          @param4,
          @param5

(Even though you pass in all 5 parameters, only the ones you are interested in get used in the INSERT, and passing them as parameters prevents SQL Injection attacks, or the need to escape special characters, etc, etc. Oh, and you're never at risk of concatenating NULL.)

EDIT:

I also looked around to see if there was a better way to pick up column defaults. Although I couldn't find anything "better", there is a "different" approach...

INSERT INTO
    tableName(
      column1,
      column2,
      column3,
      column4,
      column5
    )
SELECT
    @param1,
    @param2,
    ISNULL(@param3, MAX(CASE WHEN COLUMN_NAME = 'column3' THEN COLUMN_DEFAULT END)),
    ISNULL(@param4, MAX(CASE WHEN COLUMN_NAME = 'column4' THEN COLUMN_DEFAULT END)),
    ISNULL(@param5, MAX(CASE WHEN COLUMN_NAME = 'column5' THEN COLUMN_DEFAULT END))
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
        TABLE_SCHEMA = 'dbo'        -- or whatever it really is in your case 
    AND TABLE_NAME   = 'tableName'
;

That doesn't need Dynamic SQL at all. But I'm not convinced that it's any better than Dynamic SQL.

EDIT2:

OH!!!

Your code shows a ROLLBACK in the error handling, which implies there's a BEGIN TRANSACTION in the code that you're not showing us?

Do you actually have a COMMIT TRANSACTION anywhere???

Dynamic sql is giving syntax error.

Update your query to the following

(@pvchMake = ''0'' OR m1.vchMake = @pvchMake) AND
(@pvchMachineId = ''0'' OR m1.vchMachineId = @pvchMachineId)

than later when you go to execute just pass it in as parameters to sp_executesql function.

EXEC sp_executesql @sql
         ,N'@pvchMachineId VARCHAR(100), @pvchMake VARCHAR(100)'
         ,@pvchMachineId,@pvchMake

or this which is cleaner

Declare @ParametersDefinition NVARCHAR(max) = N'@pvchMachineId VARCHAR(100), @pvchMake VARCHAR(100)'
EXEC sp_executesql @sql, @ParametersDefinition, @pvchMachineId,@pvchMake

In the end you do not want to concatenate your dynamic SQL statement, it opens it up for SQL Injections. Even though it is a valid option it should be avoided at all cost.

---

Related Topics