December 13, 2007 at 5:05 am
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
December 13, 2007 at 7:29 am
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?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply