CLR Table Valued UDF using VB.NET

• planetmatt

  SSCrazy

  Points: 2208

  More actions

  December 13, 2007 at 5:05 am

  #201726

  I know how to create a Scalar UDF in VB.Net but how do i create a Table Valued one?

  For a Scalar, the return value gets returned as a SqlString Structure, what does a TV UDF return it's value as?

  _

  Public Shared Function udf_FuncName(ByVal strInput As String) As SqlString

• Matt Miller (4)

  SSC Guru

  Points: 124210

  More actions

  December 13, 2007 at 7:29 am

  #759821

  You set the function to return a generic IEnumerable. You also need a method to return the individual rows (unless the object you return has a default enumeration method)

  Here's a sample:

  Imports System

  Imports System.Data

  Imports System.Data.SqlClient

  Imports System.Data.SqlTypes

  Imports Microsoft.SqlServer.Server

  Imports System.Runtime.InteropServices

  Partial Public Class UserDefinedFunctions

  <Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="FillRowSplit", IsDeterministic:=True, IsPrecise:=True, TableDefinition:="val nvarchar(100)")> _

  Public Shared Function Regexsplit(ByVal input As SqlString, ByVal pattern As SqlString) As IEnumerable

  Dim rex As System.Text.RegularExpressions.Regex = New System.Text.RegularExpressions.Regex(pattern.Value, optionS)

  If input.IsNull = True Then

  input = ""

  End If

  Dim T As String()

  T = rex.Split(input.Value)

  Return T

  End Function

  Public Shared Sub FillRowSplit(ByVal obj As Object, ByRef Val As SqlString)

  Val = CType(obj, String).ToString

  End Sub

  End Class

  ----------------------------------------------------------------------------------
  Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?