How to Output to SQL Enterprise Manager in CLR Stored Procedures
- 1). Click "Start" and then click "Microsoft Visual Studio."
- 2). Click "File," select "Open" and double-click on the CLR file in which you want to insert the procedures of passing output parameters to SQL Enterprise Manager.
- 3). Enter the following code in the beginning of the file:
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Imports System.Runtime.InteropServices
'The Partial modifier is only required on one class definition per project.
Partial Public Class StoredProcedures
''' <summary>
''' Executes a query and iterates over the results to perform a summation.
''' </summary>
<Microsoft.SqlServer.Server.SqlProcedure> _
Public Shared Sub PriceSum( <Out()> ByRef value As SqlInt32)
Using connection As New SqlConnection("context
connection=true")
value = 0
Connection.Open()
Dim command As New SqlCommand("SELECT Price FROM
Products", connection)
Dim reader As SqlDataReader
reader = command.ExecuteReader()
Using reader
While reader.Read()
value += reader.GetSqlInt32(0)
End While
End Using
End Using
End Sub
End Class - 4). Click "File," select "Save" and then click "OK" to add the function of passing output parameters to SQL Enterprise Manager in the CLR stored procedure.
