SQL Server CLR Stored Procedure

Problem

You work as a software developer using Microsoft technology, such as SQL Server, Visual Studio and .NET. You heard from a colleague that with SQL Server you can write stored procedures using C# and you want to know more about it. In this tutorial, I will walk you through the things you need to know in order to make your first incursion into CLR stored procedures.

Solution

By definition stored procedures are a set of code that are stored physically in the database. Of course, the majority are written using T-SQL or another procedural extension language if we are talking about other relational databases. In SQL Server this can be extended to use other languages. This is not something new, just think about how extended stored procedures work in SQL Server. For those of you who don’t know, extended stored procedures were the first type of stored procedures not written using Transact-SQL. In fact, extended stored procedures are Windows DLL files written in C/C++ language. You will find lots of extended stored procedures if you look in the master database.

CLR Stored Procedures

With the launch of SQL Server 2005 Microsoft refactored most of the legacy Sybase code and in the process they integrating the .NET framework which was a new technology for that time with SQL Server. Just like any of the CLR objects you can make, CLR stored procedures are functions written in any of the .NET available languages like C# or Visual Basic.

To facilitate the interaction between the CLR code and the SQL Server instance we need to use the Microsoft.SqlServer.Server namespace in our CLR code. This will allow us to use the classes necessary for this interaction. We will cover these below.

The SqlPipe Class

This is the class that we will use to return data to the client, mostly by using the overloaded member function “send”. The next table is a description of each instance of this method and other methods.

The SqlMetaData Class

This class is used to hold information about the data type of columns and parameters. So, if you want to send to the client a result set with 10 columns, then you will need 10 objects of type SqlMetaData to describe each column. The same situation applies when you want to pass parameters to a stored procedure or function executed through the context connection.

The SqlDataRecord Class

As its name states, this class keeps a data record (or a single row if you want to say). Another way of looking at this is that it contains an array of items (columns) of type SqlMetaData.

The Hello World CLR Stored Procedures

Yes, it is not a typo, I use the plural because I will show you four different versions of the Hello World stored procedure. Each one of those versions shows different ways you can use to return information to the client. Keep in mind that by “client” I mean either a query executed in SSMS, an app or another stored procedure.

The following code section contains the entire code for this solution, which includes all of the stored procedures. I will explain each one of them below.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;   
 
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void HelloWorld_Text (SqlString message, SqlInt32 iterations )
    {
        for (int i = 1; i <= (int) iterations; i++)
        SqlContext.Pipe.Send(i.ToString() + " " + (string)  message  );
    }
 
 
            
    public static void HelloWorld_Rows(SqlString message, SqlInt32 iterations)
    {
        SqlMetaData[] sqlMetaData = new SqlMetaData[2];
        SqlDataRecord sqlDataRecord; 
 
        sqlMetaData[0] = new SqlMetaData("Iteration", SqlDbType.Int);
        sqlMetaData[1] = new SqlMetaData("Message", SqlDbType.NVarChar,100  );
 
        sqlDataRecord = new SqlDataRecord(sqlMetaData);
 
 
        for (int i = 1; i <= (int)iterations; i++)
        {
            sqlDataRecord.SetInt32(0, i);
            sqlDataRecord.SetSqlString(1, message);
            SqlContext.Pipe.Send(sqlDataRecord);
        }
 
    }
 
 
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void HelloWorld_Rows_2(SqlString message, SqlInt32 iterations)
    {
        SqlMetaData[] sqlMetaData = new SqlMetaData[2];
        SqlDataRecord sqlDataRecord;
 
        sqlMetaData[0] = new SqlMetaData("Iteration", SqlDbType.Int);
        sqlMetaData[1] = new SqlMetaData("Message", SqlDbType.NVarChar, 100);
 
        sqlDataRecord = new SqlDataRecord(sqlMetaData);
 
        SqlContext.Pipe.SendResultsStart(sqlDataRecord);
        for (int i = 1; i <= (int)iterations; i++)
        {
            sqlDataRecord.SetInt32(0, i);
            sqlDataRecord.SetSqlString(1, message);
            SqlContext.Pipe.SendResultsRow(sqlDataRecord);
        }
        SqlContext.Pipe.SendResultsEnd();
    }
 
 
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void HelloWorld_Rows_SqlDataReader(SqlString message, SqlInt32 iterations)
    {
        SqlCommand sqlCommand;
        SqlDataReader reader;
        string commandText =    "DECLARE @Iteration INT;" + 
                                "DECLARE @Message NVARCHAR(100);" +
                                "DECLARE @TempTable TABLE " +
                                "(" +
                                "    Iteration INT IDENTITY(1, 1)," +
                                "    Message NVARCHAR(100)" +
                                ");" +
                                "" +
                                "SELECT @Iteration = @ParamIteration;" +
                                "SELECT @Message = @ParamMessage;" +
                                "WHILE @Iteration > 0" +
                                "BEGIN" +
                                "    INSERT INTO @TempTable (Message) " +
                                "    VALUES (@Message);" +
                                "    SELECT @Iteration = @Iteration - 1;" +
                                "END;" +
                                "" +
                                "SELECT Iteration," +
                                "       Message " +
                                "FROM @TempTable;";
 
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            sqlCommand = new SqlCommand(commandText,connection);
            sqlCommand.Parameters.Add("@ParamIteration", SqlDbType.Int);
            sqlCommand.Parameters.Add("@ParamMessage", SqlDbType.NVarChar, 100);
 
            sqlCommand.Parameters["@ParamIteration"].Value = iterations;
            sqlCommand.Parameters["@ParamMessage"].Value = message;
 
            reader = sqlCommand.ExecuteReader();
            SqlContext.Pipe.Send(reader);
        }
 
 
    }
    public static void HelloWorld_Rows_ExecuteAndSend(SqlString message, SqlInt32 iterations)
    {
        SqlCommand sqlCommand;
        string commandText = "DECLARE @Iteration INT;" +
                                "DECLARE @Message NVARCHAR(100);" +
                                "DECLARE @TempTable TABLE " +
                                "(" +
                                "    Iteration INT IDENTITY(1, 1)," +
                                "    Message NVARCHAR(100)" +
                                ");" +
                                "" +
                                "SELECT @Iteration = @ParamIteration;" +
                                "SELECT @Message = @ParamMessage;" +
                                "WHILE @Iteration > 0" +
                                "BEGIN" +
                                "    INSERT INTO @TempTable (Message) " +
                                "    VALUES (@Message);" +
                                "    SELECT @Iteration = @Iteration - 1;" +
                                "END;" +
                                "" +
                                "SELECT Iteration," +
                                "       Message " +
                                "FROM @TempTable;";
        
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            connection.Open();
            sqlCommand = new SqlCommand(commandText, connection);
            sqlCommand.Parameters.Add("@ParamIteration", SqlDbType.Int);
            sqlCommand.Parameters.Add("@ParamMessage", SqlDbType.NVarChar, 100);
 
            sqlCommand.Parameters["@ParamIteration"].Value = iterations;
            sqlCommand.Parameters["@ParamMessage"].Value = message;
 
            SqlContext.Pipe.ExecuteAndSend(sqlCommand);
        }
 
 
    }
 
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void HelloWorld_Output_Parameter(SqlString message, SqlInt32 iterations, out SqlString outputString)
    {
       SqlString newLine = new SqlString("\n", message.LCID);
       SqlString space = new SqlString(" ", message.LCID);
       outputString = new SqlString("", message.LCID);
       
        for (int i = 1; i <= (int)iterations; i++)
            outputString =  outputString  + newLine + new SqlString(i.ToString(), message.LCID)  + space +  message;
 
    }
 
}

All of the functions except one contain two parameters: message of type SqlString and iterations of type SqlInt32. The exception is the HelloWorld_Output_Parameter function that has an extra parameter outputString of type SqlString which is the one used to return values from the stored procedure to the client.

We need to compile the code above and create the DLL that SQL Server will use.

After building the project we can create the assembly in our test SQL Server database by running the following command. Keep in mind that you have to replace the path to the assembly to the one on your test environment.

CREATE ASSEMBLY CLR_StoredProcedures AUTHORIZATION [dbo]
FROM 'C:\Users\Daniel\source\repos\CLR_StoredProcedures\bin\Debug\CLR_StoredProcedures.dll'
WITH PERMISSION_SET = UNSAFE;
GO

Now let’s create the CLR stored procedures in SQL Server.

CREATE PROCEDURE PRC_HelloWorld_Text
    @message NVARCHAR(500),
    @iterations INT
AS
EXTERNAL NAME CLR_StoredProcedures.StoredProcedures.HelloWorld_Text;
GO
 
CREATE PROCEDURE PRC_HelloWorld_Rows
    @message NVARCHAR(500),
    @iterations INT
AS
EXTERNAL NAME CLR_StoredProcedures.StoredProcedures.HelloWorld_Rows;
GO
 
CREATE PROCEDURE PRC_HelloWorld_Rows_2
    @message NVARCHAR(500),
    @iterations INT
AS
EXTERNAL NAME CLR_StoredProcedures.StoredProcedures.HelloWorld_Rows_2;
GO
 
CREATE PROCEDURE PRC_HelloWorld_SqlDataReader
    @message NVARCHAR(500),
    @iterations INT
AS
EXTERNAL NAME CLR_StoredProcedures.StoredProcedures.HelloWorld_Rows_SqlDataReader;
GO
 
CREATE PROCEDURE PRC_HelloWorld_Rows_ExecuteAndSend
    @message NVARCHAR(500),
    @iterations INT
AS
EXTERNAL NAME CLR_StoredProcedures.StoredProcedures.HelloWorld_Rows_ExecuteAndSend;
GO
 
CREATE PROCEDURE PRC_HelloWorld_Output_Parameter
    @message NVARCHAR(500),
    @iterations INT,
    @outputString NVARCHAR(500) OUTPUT
AS
EXTERNAL NAME CLR_StoredProcedures.StoredProcedures.HelloWorld_Output_Parameter;
GO

Notice that we don’t have to put the parameters to the CLR function, parameters must be in the create procedure header.

Finally, it’s time to analyze each procedure (or C# function if you want) code.

HelloWorld_Text

This is the basics, a simple function that writes a message to the console the number of times specified in the @iterations parameter. We use the SqlContext.Pipe.Send function to send a string to display the results as a message.

You can use the code below to run the procedure.

EXEC PRC_HelloWorld_Text 'Hello World!', 3
GO

By looking at the next image you can see the results of its execution.

HelloWorld_Rows

In this function we try to mimic the same output as the previous function with tabular results (in a grid format). To do so, we use the SqlContext.Pipe.Send, but with a SqlDataRecord passed as a parameter.

Let’s run the following code to execute the stored procedure.

EXEC PRC_HelloWorld_Rows 'Hello World!', 3
GO

As you will see in the image below, it doesn’t look as expected because we see that our function returned each iteration as a separate result set.

The reason behind this is that the SqlContext.Pipe.Send function sends whatever you pass as a parameter as a result set, so if we execute the SqlContext.Pipe.Send function three times we will see three result sets returned to the client.

HelloWorld_Rows_2

In this function we will send all the iterations as a single result set. You will see that the code is almost identical with the exception of three function calls. We call the function SqlContext.Pipe.SendResultsStart before the for loop this function will mark the beginning of our result set by using the metadata contained in the sqlDataRecord object passed as a parameter but won’t send any row to the client.

To send each row to the client we invoke the function SqlContext.Pipe.SendResultsRow with a sqlDataRecord object as a parameter.

Finally, we call the SqlContext.Pipe.SendResultsEnd function after the for loop to mark the end of the result set.

EXEC PRC_HelloWorld_Rows_2 'Hello World!', 3
GO

The following screen capture shows the execution of this function (or stored procedure if you like).

HelloWorld_Rows_SqlDataReader

With this function we will use the SqlContext.Pipe.Send function to send all the iterations in a single result set. We can do that by passing a SqlDataReader object as a parameter. Since the SqlDataReader class provides a way of reading a forward-only stream of rows from SQL Server, we have to create a SQL script that receive both parameters (Iterations and Message) and produce that stream. With the following script we can produce that stream.

DECLARE @Iteration INT;
DECLARE @Message NVARCHAR(100);
DECLARE @TempTable TABLE
(
    Iteration INT IDENTITY(1, 1),
    Message NVARCHAR(100)
);
 
SELECT @Iteration = @ParamIteration;
SELECT @Message = @ParamMessage;
WHILE @Iteration > 0
BEGIN
    INSERT INTO @TempTable (Message)
    VALUES (@Message);
    SELECT @Iteration = @Iteration - 1;
END;
 
SELECT Iteration,
       Message
FROM @TempTable;

Notice that the script has the variables @Iteration and @Message assigned with the values of two other variables (@ParamIteration and @ParamMessage) that are not declared. That’s because those variables are meant to be set at run time. To see this more clearly think about executing that code with the sp_executesql stored procedure. It will look like this.

DECLARE @ParamIteration INT;
DECLARE @ParamMessage NVARCHAR(100);
DECLARE @Sql NVARCHAR(4000);
SELECT @ParamIteration = 3, @ParamMessage=N'Hello World!'
SET @Sql = N'DECLARE @Iteration INT;
DECLARE @Message NVARCHAR(100);
DECLARE @TempTable TABLE
(
    Iteration INT IDENTITY(1, 1),
    Message NVARCHAR(100)
);
 
SELECT @Iteration = @ParamIteration;
SELECT @Message = @ParamMessage;
WHILE @Iteration > 0
BEGIN
    INSERT INTO @TempTable (Message)
    VALUES (@Message);
    SELECT @Iteration = @Iteration - 1;
END;
 
SELECT Iteration,
       Message
FROM @TempTable;'
 
EXEC sys.sp_executesql @Sql, N'@ParamIteration INT, @ParamMessage NVARCHAR(100)', @ParamIteration,@ParamMessage

The next image shows the execution of the code above.

Back to our HelloWorld_Rows_SqlDataReader function you will see that in order to execute the SQL script that will create our data stream we need a SqlConnection object to open the context connection. Then when we have our SqlDataReader object filled with the data, we can pass it as a parameter to the SqlContext.Pipe.Send function.

The next screen capture will show you the execution of the stored procedure (or C# function if you prefer).

HelloWorld_Rows_ExecuteAndSend

The HelloWorld_Rows_ExecuteAndSend function is identical to the previous one with the exception that in this case we will use the SqlContext.Pipe.ExecuteAndSend function to send the result set to the client. The SqlContext.Pipe.ExecuteAndSend requires that we pass as a parameter a SqlCommand object with the command (SQL statement or stored procedure) we want to be executed and sent to the client saving us the use of the SqlDataReader.

The following screen capture shows the execution of this function (or stored procedure if you like).

HelloWorld_Output_Parameter

In this case we will return the Hello World message using an output parameter. In C# the way we use to send something from inside our function to the caller is by preceding the parameter declaration with the keyword “out” without the quotes as you will see in the next code section.

public static void HelloWorld_Output_Parameter(SqlString message, SqlInt32 iterations, out SqlString outputString)

The only complexity of this function underlies on the use of SqlString data type and string concatenation. As you may know, the SqlString data type is equivalent to the NVARCHAR data type in SQL Server, which is a locale dependent type. That’s why I had to create the newline variable to hold the “\n” new line escape character. Otherwise we will get a runtime error because the concatenation of strings will fail. For us in the SQL Server world its like trying to mix collations.

Next Steps

• To learn more about SQL Server CLR Integration check out these articles:
  • SQL Server CLR Scalar Functions
  • How to Enable SQL Server CLR Integration using T-SQL
  • SQL Server CLR Introduction
  • SQL Server CLR User Defined Types Example
  • Remove HTML tags from strings using the SQL Server CLR