CLR user defined function parameter passing

Question

Im a SQL/VB person thats new to CLR. Basically, I wanted to create a SQL function to pass an ID parameter, and return the corresponding data from a SQL table;

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class UserDefinedFunctions
    <Microsoft.SqlServer.Server.SqlFunction(DataAccess:= DataAccessKind.Read)> _
    Public Shared Function FalloutProfile(ByVal CRFNID As Integer) As SqlString

        Using connection As New SqlConnection("context connection=true")

            connection.Open()
            Dim command As New SqlCommand("SELECT RATE0ORIG FROM dbo.clsgfunc WHERE CRFNID=@pnum", connection)
            command.Parameters.Add("@pnum", SqlDbType.SmallInt).Value = CRFNID

            Dim reader As SqlDataReader
            reader = command.ExecuteReader()

            SqlContext.Pipe.Send(reader)

        End Using

    End Function
End Class

I cant seem to get this working:

System.NullReferenceException: Object reference not set to an instance of an object.

any ideas?

can you give us more of the stack trace? just telling us there was a NullReferenceException doesn't give any leads — Mgetz
– Mgetz, Commented Jun 28, 2013 at 18:00
not familiar enough to get more than that. The NullReferenceException is from SQL 08 R2 after I deploy the above CLR. Hoping it was something obvious in the code. — redline9k
– redline9k, Commented Jun 28, 2013 at 18:43
A NullReferenceException means that something in your code is null when you try to access it. Can you try to debug locally? — Mgetz
– Mgetz, Commented Jun 28, 2013 at 18:46

takrl · Accepted Answer · 2014-06-13 11:15:32Z

1

Microsoft has an answer to this, it's hidden in the description of the SqlContext.Pipe Property:

An instance of SqlPipe if a pipe is available, or null if called in a context where pipe is not available (for example, in a user-defined function).

So, if you try this from within a CLR procedure, it should work. From within a CLR function, it won't.

answered Jun 13, 2014 at 11:15

takrl

6,5003 gold badges64 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

CLR user defined function parameter passing

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related