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
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:
SELECT * FROM sys.configurations WHERE name = 'clr enabled'
Go
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 = ModifyParameter(SQL, parameterValue);
SQL = ModifySQL(SQL);
return ExecuteSQL(SQL);
}
private static string ModifyParameter(string SQL, object parameterValue)
{
if (parameterValue != null && SQL.IndexOf("@") > -1)
{
int pos = SQL.IndexOf("@");
string ParameterName = SQL.Replace(SQL.Substring(0, pos), "");
string[] temp = ParameterName.Split(' ');
ParameterName = temp[0];
if (parameterValue.GetType().ToString().ToLower().IndexOf("sqlstring") > -1)
{
SQL = SQL.Replace(ParameterName, "'" + parameterValue.ToString() + "'");
}
else
{
SQL = SQL.Replace(ParameterName, parameterValue.ToString());
}
}
return SQL;
}
private static string ModifySQL(string SQL)
{
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 = "";
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:
SELECT [CLRDemo].[dbo].[udfs_CLR_GetCSV_AdhocSQL](
'SELECT Name from CLRDemo.dbo.Department WHERE GroupName=@GroupName',
'Executive General and Administration') AS Departments
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.