HV-Lima Peru

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

Category Archives: T-SQL

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
        ”2′,’3′,’4′
        ‘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() & "’"
            Else
                output = "’" & input & "’"
            End If
        Else
            ‘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 & "’,"
                Next
                output = output.TrimEnd(",")
            Else
                For i = 0 To returnValue.Length – 1
                    output &= "’" & returnValue(i) & "’,"
                Next
                ‘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: