Writing a CLR Stored Procedure on SQL Server

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.

image

 

Name your file “GetFileList”

image

Click “Add”

 

This is what you get:

image

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

image

 

At left tab menu, click on Database

image

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>

image

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:

image

 

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'

image

 

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.

2 comments
Posted by arlen on 10/17/2010 5:00:50 AM
Hi, thx for article
Could you please tell me when and where to use SQL CLR ?
Posted by Carlos Bercero on 10/23/2010 2:25:16 PM
Hello arlen,

See if this post answers your question:
http://carlosbercero.com/post/?post=What_is_SQL_CLR

Regards,

Carlos
Leave a comment
Display Name:  


Email:    


Comment:  



Domain Registrations starting at $9.69
Powered by Carlos Bercero Blog Platform © 2009