Creating a CLR Scalar User-Defined Function : CLR « CLR « SQL Server / T-SQL Tutorial





CREATE ASSEMBLY SQLArrayBuilder FROM 'C:\SQLArrayBuilder.DLL'
WITH PERMISSION_SET = SAFE
GO

CREATE FUNCTION dbo.CountSalesOrderHeader
(@ArrayString nvarchar(400), 
 @ArrayDelimiter nchar(1), 
 @ArrayItemSelection smallint)
RETURNS nvarchar(400)
AS
EXTERNAL NAME SQLArrayBuilder.SQLArrayBuilder.ChooseValueFromArray
GO

SELECT dbo.CountSalesOrderHeader
('B,S', ',', 3) Choice

SELECT dbo.CountSalesOrderHeader
('B D', ',', 2) Choice

///////////////////

Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Text.RegularExpressions

Public Class SQLArrayBuilder
    Public Shared Function ChooseValueFromArray(ArrayString as String, ArrayDelimiter as String, ArrayItemSelection as SqlInt16) as SqlString
    
    Dim NewArrayString as String() = Regex.Split(ArrayString, ArrayDelimiter)
    Dim NewArrayItemSelection as SqlInt16=ArrayItemSelection-1
    Dim ReturnString as SQLString = NewArrayString(NewArrayItemSelection)
    Return ReturnString
    
    End Function
End Class










29.2.CLR
29.2.1.Converting SQL Server to CLR Data Types
29.2.2.--Enabling CLR Support in SQL Server 2005
29.2.3.Loading the Assembly Into SQL Server
29.2.4.Creating the CLR Stored Procedure
29.2.5.Creating a CLR Scalar User-Defined Function
29.2.6.Creating a CLR Trigger