Writing a CLR Stored Procedure on SQL Server

11/23/2009 5:42:43 AM

As I mentioned in my previous posts, CLR objects on SQL Server can be very useful in certain scenarios. Here I will show you how to create, deploy and test a simple CLR stored procedure. This stored procedure will return a file list from the file system upon the specified path.

1. Create a new SQL Server project in Visual Studio 2008.
The steps are detailed in this post: How to create an SQL CLR Project in Visual Studio 2008.

2. Add a new Stored Procedure.

At the top menu, click on Project / Add Stored Procedure.

Name your file “GetFileList”

Click “Add”

This is what you get:

An empty CLR stored procedure like this:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server


Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub  GetFileList ()
        ' Add your code here
    End Sub
End Class

3. Clear the file content and copy-paste the following code:

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures

    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub GetFileList(ByVal Path As SqlString)

        If Path.IsNull Then
            SqlContext.Pipe.Send("No path specified")
            Exit Sub

        ElseIf Not IO.Directory.Exists(Path.ToString) Then
            Throw New Exception("Path not found: " & Path.ToString)

        End If

        Dim record As New SqlDataRecord( _
          New SqlMetaData("Path", SqlDbType.NVarChar, 2048) _
        , New SqlMetaData("Name", SqlDbType.NVarChar, 256) _
        )

        SqlContext.Pipe.SendResultsStart(record)

        For Each sFilePath As String In IO.Directory.GetFiles(Path.ToString)

            record.SetString(0, (sFilePath))
            record.SetString(1, (sFilePath.Substring(sFilePath.LastIndexOf("\") + 1)))

            SqlContext.Pipe.SendResultsRow(record)
        Next

        SqlContext.Pipe.SendResultsEnd()

    End Sub

End Class


This is what this CLR stored procedure does:

1. Takes a file system path as a parameter.
2. Check if the value is null. If it is, it returns a message and terminates the procedure.
3. If the path is not null, the it checks if the specified path exists. If it doesn’t, it throws an exception with a message.
4. If the path is valid, it iterates on every file in the specified folder, and returns a results set with the name and path of the files set in that folder.

4. Change the project Permission Level to “External”.
In this case, this is needed because the assembly needs to access an external resource outside SQL Server environment.

At the top menu, click on Project / <Your Project Name> Properties

At left tab menu, click on Database

On the “Permission Level” dropdown, select “External”.

Close the properties page.

5. Set trustworthy on at the deploying database.

SQL Server does not allow execution of clr with external access by default. You have to turn on that ability with the following command.

At Management Studio, execute this:

ALTER DATABASE <Your Database Name> SET TRUSTWORTHY ON

6. Deploy your project.

At the top menu, click on Build / Deploy <Your Project Name>

This will do the following steps backstage:

1. Compile the project assembly
2. Copy the assembly to the specified database
3. Create a Stored Procedure with the same name as the method you just wrote and point it to the corresponding method in the assembly it just copied into the database

Open SQL Management Studio and see for your self:

7. Test your Stored Procedure

At SQL Management Studio, create a new query and invoke the stored procedure with a valid path in the machine the SQL Server resides.

Example:

GetFileList 'c:\test'

I hope you are now ready to get started building your own SQL CLR stored procedures. If you find something unclear, or if you want more detail on a specific matter, please, feel free to leave me a note.