CODEWITHSUNDEEP
.net
josephj1989
josephj1989

Reputation: 9709

.NET CLR Stored procedure OUTPUT Parameter

I am creating a CLR stored procedure in VB.NET (.NET 3.5,SQLServer 2005). The procedure has 4 BYVal parameter and 4 BYRef parameters. It compiles and deploys correctly into SQL Server and a Stored procedure is created. However when I try to run the SP it insists on passing the OUTPUT parameters in. It gives the following error

I am running it from SQL Server as Below

DECLARE @return_value int, @outI int, @outS nvarchar(4000), @outD datetime, @outB bit

EXEC    @return_value = [dbo].[ProofOfConcept]
        @inI = 23,
        @inS = N'john',
        @inD = N'12/12/2009',
        @inB = 1,
        @outI = @outI OUTPUT,
        @outS = @outS OUTPUT,
        @outD = @outD OUTPUT,
        @outB = @outB OUTPUT

'TestProc' failed because parameter 5 is not allowed to be null.

My VB.NET procedure declaration is below

 Public Shared Sub TestProc(ByVal inI As Integer, ByVal inS As String, ByVal inD As DateTime, ByVal inB As Boolean, _
                                     ByRef outI As Integer, ByRef outS As String, ByRef outD As DateTime, ByRef outB As Boolean)

Upvotes: 0

Views: 3164

Answers (1)

evpo
evpo

Reputation: 2531

Use Out() attribute before ByRef as in the code below

Imports System.Runtime.InteropServices
…
Public Shared Sub PriceSum ( <Out()> ByRef value As SqlInt32)

Upvotes: 2

Related Questions