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

Home
SQL Server / T-SQL Tutorial
1.Query
2.Insert Delete Update
3.Table
4.Table Join
5.Data Types
6.Set Operations
7.Constraints
8.Subquery
9.Aggregate Functions
10.Date Functions
11.Math Functions
12.String Functions
13.Data Convert Functions
14.Analytical Functions
15.Sequence Indentity
16.View
17.Index
18.Cursor
19.Database
20.Transact SQL
21.Procedure Function
22.Trigger
23.Transaction
24.XML
25.System Functions
26.System Settings
27.System Tables Views
28.User Role
29.CLR
SQL Server / T-SQL Tutorial » CLR » CLR 




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', ','3Choice

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

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

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 SqlInt16as 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
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.