CODEWITHSUNDEEP
sqlvb.netclr
redline9k
redline9k

Reputation: 11

CLR user defined function parameter passing

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?

Upvotes: 1

Views: 903

Answers (1)

takrl
takrl

Reputation: 6472

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.

Upvotes: 1

Related Questions