Feb 12, 2014

Output parameters of CLR stored procedure in vb.net


Vb.net code for output parameters of CLR stored procedure

Output parameter of CLR procedure must be passed by reference. Output parameters are must have attribute <Out ()>. For this we have to import System.Runtime.InteropServices

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

Partial Public Class StoredProcedures

    Private Shared command As SqlCommand
    Private Shared reader As SqlDataReader

    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub uspGetEmployeeInfo(<Out()> ByRef StatusMsg As String)

        Try

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

                connection.Open()
                command = New SqlCommand("SELECT EmpName,Age,Country FROM Employee", connection)
                reader = command.ExecuteReader()

                SqlContext.Pipe.Send(reader)

            End Using

            StatusMsg = "Success"

        Catch ex As Exception

            StatusMsg = ex.Message

        End Try

    End Sub
End Class

Sql query to execute:

DECLARE  @StatusMsg NVARCHAR(4000)

EXEC [dbo].[uspGetEmployeeInfo] @StatusMsg OUTPUT

No comments:

Post a Comment