Nov 092011
 

Functions are the most restrictive code blocks you can define in SQL Server. They perform just like stored procedures in which it can return set and output parameters. But you cannot create or modify objects and the data within the object in a function except the objects are table variables and table value type variables. Actually any transaction depended objects are not allowed to run in a function, such as SEND and RECEIVE. You can run exec proc within a function but only some extended procedures are allowed. One is not allowed but most developers(including me) desire is to run sp_executesql in a function. You can get around it by creating CLR functions.

First of all, you need to consider using context connection string or none context connection string. If you use context connection string linking back SQL Server, you can run a dynamic SQL but you can only perform read operation. If you try to update a table, you will get error:

Invalid use of a side-effecting operator 'UPDATE' within a function.

If you want to use function to run a dynamic SQL with data modification, you will have to use a loop back connection. You can use context connection to get SQL Server Instance Name and Database Name, and use them to compose a loop back connectioin string. See code from line 10 to 10 below.
One more thing you need to remember is to disable use of Distributed Transaction Coordinator (DTC) by setting ENLIST property FALSE in the loop back connection string. If not, when your function running under a transaction, existance of MSDTC will be checked. If you don’t have, you will receive

MSDTC on server 'server' is unavailable.

If you do have, you will receive

Transaction context in use by another session.

This feature is by design. Nothing wrong with your DTC.

    [Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read)]
    public static SqlChars RunSQLStr(SqlChars SQL)
    {
        if (SQL.IsNull)
            return SqlChars.Null;
        string instanceName, databaseName;
        object returnValue=null;
        using (SqlConnection connection = new SqlConnection())
        {
            SqlCommand command = connection.CreateCommand();
            connection.ConnectionString = "context connection=true";
            connection.Open();
            command.CommandText = "select @@serverName, db_name();";
            SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
            reader.Read();
            instanceName = reader[0].ToString();
            databaseName = reader[1].ToString();
            reader.Close();
            connection.ConnectionString = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI;", instanceName, databaseName); //Enlist=false;
            connection.Open();
            command.CommandText = SQL.ToSqlString().ToString();
            returnValue = command.ExecuteScalar();
            connection.Close();
        }
        return returnValue is Nullable ? SqlChars.Null : new SqlChars(new SqlString(returnValue.ToString()));
    }

The CLR above will return a string value. You can create your owns for different returning data types or even set(s).

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="">

(required)

(required)

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.