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