Use SQL CLR Function to Overcome Limitation of SQL User-Defined Function

• Download SqlCLRFunction.zip - 381.6 KB

Introduction

Pulling data from a column with multiple rows and transforming it into a CSV string is a common task that a developer does frequently. There are many creative SQL statements available for the task. My favorite is the one similar to the following that generates a CSV string for department names:

Listing 1:

SELECT STUFF((SELECT  ', ' + Name FROM Department 
	WHERE GroupName='Executive General and Administration' 
	FOR XML PATH('')), 1, 2, '')

The FOR XML PATH(‘’)  is utilized to transform multiple rows of names into a single row  consisting of  repeated  ‘, ‘ + Name, and then the STUFF() function removes the extra leading comma and space ‘, ‘ to obtain a regular CSV string.  To better take advantage of the above SQL query, a SQL user-defined function comes handy.

Listing 2:

CREATE FUNCTION [dbo].[udfs_SelectCSV]
(
	@GroupName NVARCHAR(50) 
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	RETURN (
		SELECT STUFF((SELECT  ', ' + Name FROM CLRDemo.dbo.Department 
		WHERE GroupName=@GroupName FOR XML PATH('')), 1, 2, '')
	)
END

It is convenient to use the function in a complex SQL query to include a CSV column. This works well until we want a CSV from a different column and tables, for example, a CSV of book names in a category. In this case, another user-defined function has to be created with relevant SQL query. It would be nice if an ad-hoc SQL query can be passed into the SQL function and executed, so that a CSV may be obtained from any column (or tables) as desired. However, execution of an ad-hoc query is not allowed in a SQL function by design.

Fortunately, a SQL CLR function can help overcome the limitation. 

SQL CLR Function

There are plenty of references available on creating SQL CLR functions using Visual Studio. Steps and screen shots are presented here using SQL Server 2012 and Visual Studio 2015.

First of all, create a database and name it CLRDemo on your SQL server in SSMS, and then run the SQL scripts in Listing 3 to create and populate a data table: Department that is borrowed from AdventureWorks. A user-defined function udfs_SelectCSV is also created by the scripts.

Listing 3:

USE [CLRDemo]
GO
/****** Object:  Table [dbo].[Department]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department](
	[DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[GroupName] [nvarchar](50) NOT NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Department] ON 

INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (1, N'Engineering', N'Research and Development')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (2, N'Tool Design', N'Research and Development')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (3, N'Sales', N'Sales and Marketing')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (4, N'Marketing', N'Sales and Marketing')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (5, N'Purchasing', N'Inventory Management')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (6, N'Research and Development', N'Research and Development')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (7, N'Production', N'Manufacturing')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (8, N'Production Control', N'Manufacturing')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (9, N'Human Resources', N'Executive General and Administration')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (10, N'Finance', N'Executive General and Administration')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (11, N'Information Services', N'Executive General and Administration')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (12, N'Document Control', N'Quality Assurance')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (13, N'Quality Assurance', N'Quality Assurance')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (14, N'Facilities and Maintenance', N'Executive General and Administration')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (15, N'Shipping and Receiving', N'Inventory Management')
INSERT [dbo].[Department] ([DepartmentID], [Name], [GroupName]) VALUES (16, N'Executive', N'Executive General and Administration')
SET IDENTITY_INSERT [dbo].[Department] OFF

GO

CREATE FUNCTION [dbo].[udfs_SelectCSV]
(
	@GroupName NVARCHAR(50) 
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	RETURN (
		SELECT STUFF((SELECT  ', ' + Name FROM CLRDemo.dbo.Department 
		WHERE GroupName=@GroupName FOR XML PATH('')), 1, 2, '')
	)
END

 

Secondly, make sure CLR is enabled on your SQL server. 

Listing 4:

--See if CLR is enabled
SELECT * FROM sys.configurations WHERE name = 'clr enabled'
Go

--Enable CLR on SQL Server if not enabled
sp_configure 'show advanced options', 1 
RECONFIGURE 
GO 
sp_configure 'clr enabled', 1 
RECONFIGURE 
GO 
sp_configure 'show advanced options', 0 
RECONFIGURE 
GO 

 

Now it is ready to create a SQL CLR function in Visual Studio 2015. 

 

1. Create a Sql Server Database Project in Visual Studio 2015 and name it SqlCLRFunction.

 

2. Right click on the project just created and select Properties.

 

3. In the Properties window, select a correct SQL server version and .NET version. In this demo, they are SQL Server 2012 and .NET Framework 4.5.2 respectively. 

 

4. Add a SQL CLR C# User Defined Function code file into the project, name it CLRFunction.cs.

 

5. A default function is created by Visual Studio with required directives.

 

6. Replace the default function with code in listing 5. The public static method udfs_CLR_GetCSV_AdhocSQL is the CLR function to be deployed to the SQL database, CLRDemo. The code will be reviewed later.

Listing 5:

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

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static SqlString udfs_CLR_GetCSV_AdhocSQL(string SQL, object parameterValue)
    {
        /*
        SQL: a adhoc sql statement including a parameter, for example,
            SELECT Name from CLRDemo.dbo.Department WHERE GroupName=@GroupName
        parameterValue: a parameter value. Since data type is unknown, use "object" type
        */

        //handle parameter in the SQL
        SQL = ModifyParameter(SQL, parameterValue);

        //Modify SQL so that it returns a CSV string
        SQL = ModifySQL(SQL);

        //execute SQL to return a CSV string
        return ExecuteSQL(SQL);
    }
    private static string ModifyParameter(string SQL, object parameterValue)
    {
        //A parameter exists in the SQL statement similar to "Where GroupName=@GroupName"
        if (parameterValue != null && SQL.IndexOf("@") > -1)
        {
            int pos = SQL.IndexOf("@");
            
            //Get a string from the @ position to the end of the SQL string
            string ParameterName = SQL.Replace(SQL.Substring(0, pos), "");
            //Get the parameter name by removing trailing characters after the parameter name
            string[] temp = ParameterName.Split(' ');
            ParameterName = temp[0];
            //Repplace parameter name with parameter value based on sql data type
            if (parameterValue.GetType().ToString().ToLower().IndexOf("sqlstring") > -1)
            {
                //parameter value of a sql string (char or varchar) type
                SQL = SQL.Replace(ParameterName, "'" + parameterValue.ToString() + "'");
            }
            else
            {
                //parameter value of a int type
                SQL = SQL.Replace(ParameterName, parameterValue.ToString());
            }
        }
        return SQL;
    }
    private static string ModifySQL(string SQL)
    {
        /*Modify sql statement to return a CSV string

          Original SQL sample: 
          SELECT Name FROM CLRDemo.dbo.Department WHERE GroupName=@GroupName

          Modified SQL sample:
          SELECT STUFF((
              SELECT  ', ' + Name FROM Department WHERE GroupName=@GroupName FOR XML PATH('')
              ), 1, 2, '')
        */
        SQL = SQL.ToUpper();
        SQL = SQL.Replace("SELECT ", "SELECT ', ' + ") + " FOR XML PATH('')";
        SQL = "SELECT STUFF((" + SQL + "), 1, 2, '')";
        return SQL;
    }
    private static string ExecuteSQL(string SQL)
    {
        string CSV = "";
        //No server and database name are required in the connection. 
        using (SqlConnection con = new SqlConnection("Context Connection = true;"))
        {
            SqlCommand cmd = new SqlCommand(SQL, con);
            con.Open();
            object o = cmd.ExecuteScalar();
            if (o != null)
            {
                CSV = o.ToString();
            }
        }
        return CSV;
    }
}

 

7. Right click on the SqlCLRFunction project, and select Build.

 

8. After the “Build” is complete, right click on the project again and select Publish.

 

 

9. In the Publish dialog box, select your SQL server and the CLRDemo database.

 

10. Click Ok in the Connection Properties dialog above and then the Publish button.

 

11. The publish progress is displayed at the bottom left of the Visual Studio screen, and ends with “Publish completed successfully”.

12. Go to the CLRDemo database in SSMS to see the published SQL CLR function, udfs_CLR_GetCSV_AdhocSQL. The udfs_SelectCSV is a regular SQL user-defined function created by scripts earlier.

 

13. Use the scripts in Listing 6 to test in SSMS. The CLR function and the regular SQL function return the same results.

Listing 6:

--CLR Function
SELECT [CLRDemo].[dbo].[udfs_CLR_GetCSV_AdhocSQL](
'SELECT Name from CLRDemo.dbo.Department WHERE GroupName=@GroupName', 
'Executive General and Administration') AS Departments

--Regular SQL Function
SELECT [CLRDemo].[dbo].[udfs_SelectCSV]('Executive General and Administration')

 

CLR Function Code Details

The complete C# code for the SQL CLR function is presented in Listing 5 above, in which a public static method: udfs_GetCSV_by_AdhocSQL(SQL, parameterValue) is the CLR function published to the CLRDemo database. The method basically executes three private static methods that process ad-hoc SQL query and return a CSV string upon execution. Let's take a look at the private methods: ModifyParameter(), ModifySQL() and ExecuteSQL() below.

 

ModifyParameter()

private static string ModifyParameter(string SQL, object parameterValue)

The method accepts two parameters: SQL and parameterValue. SQL is the ad-hoc SQL query in Listing 6 as shown below:

SELECT Name from CLRDemo.dbo.Department WHERE GroupName = @GroupName

The query simply retrieves a list of department names in a group. It contains a SQL parameter named as @GroupName. The parameterValue is an actual value for @GroupName. In our example, the value is “Executive General and Administration”.  Note that data type for parameterValue is an object. This is because that the data type of a value passed in is not known until runtime. The method detects the data type of parameterValue first (int or varchar) and then carries out string concatenation accordingly based on SQL syntax. In our example, it replaces @GroupName with the value of “Executive General and Administration”, and returns a complete SQL statement like: 

SELECT Name from CLRDemo.dbo.Department WHERE GroupName= 'Executive General and Administration'

 

ModifySQL()

private static string ModifySQL(string SQL)

After the parameter value is combined into the ad-hoc SQL query, this method transforms the ad-hoc query into the one that produces a CSV string. That is,

From: 

SELECT Name from CLRDemo.dbo.Department WHERE GroupName = 'Executive General and Administration'

To:

SELECT STUFF((SELECT  ', ' + Name FROM Department 
	WHERE GroupName='Executive General and Administration' 
	FOR XML PATH('')), 1, 2, '')

 

ExecuteSQL()

private static string ExecuteSQL(string SQL)

Lastly, this method executes the SQL query prepared through previous two methods using ADO.NET and returns a literal CSV string. Since C# and ADO.NET are utilized here, ad-hoc SQL query can be executed without any issues. That is how the limitation of a standard SQL user-defined function is overcome. Due to the fact that a published CLR function resides inside the CLRDemo database, a special connection string of "Context Connection = true;" is required. However, a “regular” connection string with a server name, a database name and database credentials often seen in other ADO.NET applications is not needed. 

SqlConnection con = new SqlConnection("Context Connection = true;");

 

Points of Interest

This article has explained how to use a SQL CLR function to overcome the limitation of a standard SQL user-defined function to execute an ad-hoc SQL query. In the demo, a simple SQL query with a single parameter is involved in order to illustrate the concept. Additional study may be required to expand its functionalities, such as handling SQL keywords, like “TOP” and “DISTINCT”, and handling multiple SQL parameters, etc. Furthermore, instead of a CSV, a different delimitor like | or * may be passed into the CLR function to create a custom delimited string. Although C# is a powerful programming language, as far as SQL CLR is concerned, some features may not be applicable. For example, method overloading has been tested without success.