none
CLR user function in SQL Server 2008 R2

    Question

  • Hi, I am working on learning to CLR functions and wrote this code snippet in VB.Net.

            <SqlFunction(DataAccess:=DataAccessKind.None, IsDeterministic:=True, _
                         IsPrecise:=True, Name:="RegExMatches", _
                         SystemDataAccess:=SystemDataAccessKind.None, _
                FillRowMethodName:="NextMatchedRow")> _
            Public Shared Function RegExMatches(ByVal pattern As SqlString, _
                                                 ByVal input As SqlString, _
                                                 ByVal Options As SqlInt32) _
                                  As IEnumerable
                If (input.IsNull OrElse pattern.IsNull) Then
                    Return Nothing
                End If
                Dim RegexOption As New System.Text.RegularExpressions.RegexOptions
                RegexOption = Options
                Return Regex.Matches(input.Value, pattern.Value, RegexOption)
            End Function

    Now I am trying to create an Equivalent function in SQL Server 2008 R2 & get error.

    CREATE FUNCTION RegExMatches
       (
        @Pattern NVARCHAR(4000),
        @Input NVARCHAR(MAX),
        @Options INT
       )
    RETURNS TABLE (
    	[String] [nvarchar](MAX) NULL
    ) 
    AS EXTERNAL NAME RegExFunction.[UserDefinedFunction].RegExMatches
    GO

    Error Message:

    CREATE FUNCTION failed because the parameter count for the FillRow method should be one more than the SQL declaration for the table valued CLR function.

    How do I re-write the above function in T-SQL.

    Thanks in advance.............. 
    • Moved by Naomi N Wednesday, June 26, 2013 7:03 PM Better answer can be here
    Wednesday, June 26, 2013 6:30 PM

Answers

  • OK. Well, that's what's amiss with your T-SQL. You are passing back three columns from SQLCLR, and only declare one column in the T-SQL. Try this:

    CREATE FUNCTION RegExMatches
      
    (
       
    @Pattern NVARCHAR(4000),
       
    @Input NVARCHAR(MAX),
       
    @Options INT
      
    )
    RETURNS
    TABLE (
    [String] [nvarchar](MAX), [MatchIndex] int, [MatchLength] int
    )
    AS EXTERNAL NAME RegExFunction.[UserDefinedFunction].RegExMatches
    GO

    Cheers, Bob

    • Marked as answer by ione721 Thursday, June 27, 2013 9:24 PM
    Thursday, June 27, 2013 8:56 PM

All replies

  • Seems you are using a TSQL create structure for a table function instead of a scaar function,

    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, June 26, 2013 6:49 PM
  • You need to somehow convert RegEx.Matches collection into a datatable.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, June 26, 2013 7:03 PM
  • I'm not quite sure what you want to do. I'm guessing that you want to fix the SQL definition of your .NET function and change the .NET implementation to a TVF, or do you want to rewrite the entire thing in T-SQL? (you can't call .NET RegEx in T-SQL).

    Here's the information on how you'd write a table-valued function in SQLCLR: http://msdn.microsoft.com/en-us/library/ms131103.aspx

    The System.Text.RegularExpressions.MatchCollection class (which is what is returned from the Regex.Matches function) DOES implement IEnumerable, so you need to write an appropriate FillRow method. That's what the error message is complaining about. What does your NextMatchedRow method look like? There's an example FillRow method in VB.NET on the SQLCLR reference above. You FillRow method should take 2 parameters, a ByVal parameter of type Object and a <Out()> parameter of type SqlString. There should be also be a SqlFacet attribute on the output parameter that specifies MaxSize=-1 if you really intend to return an nvarchar(max) value of more than 4000 characters.

    Hope this helps, Bob

    • Proposed as answer by Naomi N Thursday, June 27, 2013 2:42 AM
    • Unproposed as answer by ione721 Thursday, June 27, 2013 2:37 PM
    Wednesday, June 26, 2013 10:37 PM
  • Hi, Thanks all for your response. @Bob: This is what I am trying to do I have a Visual Basic source file and compiling this Visual Basic source file and created and assembly using the DLL file generated & now working on writing the SQL definition for this .NET function. And I am not able to get past that error message. Could you please help write it.

    Thanks........


    Ione

    Thursday, June 27, 2013 2:42 PM
  • Your T-SQL syntax for declaring the function is correct, however the error message is complaining about your FillRow method, the one you declared in the SqlFunction attribute, named NextMatchedRow. Since I don't see that in your source code, I don't know what's wrong with it.

    Could you post the source for the method NextMatchedRow, please? If you don't have a method named NextMatchedRow in your SQLCLR class, that, in itself, is the problem.

    Cheers, Bob


    Thursday, June 27, 2013 6:09 PM
  • Thanks Bob, Please find code snippet below.

            <SqlFunction(DataAccess:=DataAccessKind.None, IsDeterministic:=True, _
                         IsPrecise:=True, Name:="RegExMatches", _
                         SystemDataAccess:=SystemDataAccessKind.None, _
                FillRowMethodName:="NextMatchedRow")> _
            Public Shared Function RegExMatches(ByVal pattern As SqlString, _
                                                 ByVal input As SqlString, _
                                                 ByVal Options As SqlInt32) _
                                  As IEnumerable
                If (input.IsNull OrElse pattern.IsNull) Then
                    Return Nothing
                End If
                Dim RegexOption As New System.Text.RegularExpressions.RegexOptions
                RegexOption = Options
                Return Regex.Matches(input.Value, pattern.Value, RegexOption)
            End Function
            Private Shared Sub NextMatchedRow(ByVal input As Object, _
                      <Out()> ByRef match As SqlString, _
                      <Out()> ByRef matchIndex As SqlInt32, _
                      <Out()> ByRef matchLength As SqlInt32)
                Dim match2 As Match = DirectCast(input, Match)
                match = New SqlString(match2.Value)
                matchIndex = New SqlInt32(match2.Index)
                matchLength = New SqlInt32(match2.Length)
            End Sub

    Regards..................

    Ione

    Thursday, June 27, 2013 6:54 PM
  • OK. Well, that's what's amiss with your T-SQL. You are passing back three columns from SQLCLR, and only declare one column in the T-SQL. Try this:

    CREATE FUNCTION RegExMatches
      
    (
       
    @Pattern NVARCHAR(4000),
       
    @Input NVARCHAR(MAX),
       
    @Options INT
      
    )
    RETURNS
    TABLE (
    [String] [nvarchar](MAX), [MatchIndex] int, [MatchLength] int
    )
    AS EXTERNAL NAME RegExFunction.[UserDefinedFunction].RegExMatches
    GO

    Cheers, Bob

    • Marked as answer by ione721 Thursday, June 27, 2013 9:24 PM
    Thursday, June 27, 2013 8:56 PM
  • Thanks Bob, This helped I missed adding those 2 fields.

    Ione

    Thursday, June 27, 2013 9:24 PM