HV-Lima Peru

Offshore your .NET/SQL Development to Latin America! Languages: en-de-es

How to implement a CLR Assembly in SQL Server 2005?

Why that?
Manipulating strings with T-SQL is overly slow.
When it comes to strings, consider implementing a CLR Assembly, it will run around 100 times faster!

In VS2005 Create New Project, Visual Basic, Database, SQL Server Project

Below follows an example on how to add single quotes to comma separated values.
This is necessary in T-SQL when using the IN-clause.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Partial Public Class UserDefinedFunctions
    <Microsoft.SqlServer.Server.SqlFunction()> _
    Public Shared Function SingleQuote(ByVal input As String, ByVal trim As Integer, _
                                       ByVal separator As String) As String
        ‘input is csv-string or only one value
        ‘e.g. 2,3,4
        ‘that should be converted to
        ‘trim = 0 means that output is as input
        ‘trim = 1 means that each output string is left- and right-trimmed from spaces
        ‘separator is the character that separates the values, normally a comma ","
        Dim output As String = ""
        ‘if there is only one input value without separator
        ‘process this one value directly
        If input.IndexOf(separator) = -1 Then
            ‘trim the output if necessary
            If trim = 1 Then
                output = "’" & input.Trim() & "’"
                output = "’" & input & "’"
            End If
            ‘there is more than one value
            Dim returnValue As String()
            ‘csv-input is split in a collection of strings
            ‘separated by ‘separator’
            returnValue = input.Split(separator)
            Dim i As Integer
            If trim = 1 Then
                ‘trim the output if necessary
                For i = 0 To returnValue.Length – 1
                    output &= "’" & returnValue(i).Trim & "’,"
                output = output.TrimEnd(",")
                For i = 0 To returnValue.Length – 1
                    output &= "’" & returnValue(i) & "’,"
                ‘cut off the last comma
                output = output.TrimEnd(",")
            End If
        End If
        Return output
    End Function
End Class

Deployment in SSMS:

create assembly stringmanipulation from ‘c:\testsql\StringManipulation.dll’
create function InvertInt (@InStr int) returns int
external name stringmanipulation.UserDefinedFunctions.InvertInt
create function SingleQuote (@a nvarchar(max), @b int, @c nvarchar(max)) returns nvarchar(max)
external name stringmanipulation.UserDefinedFunctions.SingleQuote

Test in SSMS:


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: