| title | CLR Stored Procedures | Microsoft Docs | ||||||
|---|---|---|---|---|---|---|---|
| ms.custom | |||||||
| ms.date | 04/27/2017 | ||||||
| ms.prod | sql-server-2014 | ||||||
| ms.reviewer | |||||||
| ms.technology | database-engine | ||||||
| ms.topic | reference | ||||||
| dev_langs |
|
||||||
| helpviewer_keywords |
|
||||||
| ms.assetid | bbdd51b2-a9b4-4916-ba6f-7957ac6c3f33 | ||||||
| author | mashamsft | ||||||
| ms.author | mathoma | ||||||
| manager | craigg |
Stored procedures are routines that cannot be used in scalar expressions. Unlike scalar functions, they can return tabular results and messages to the client, invoke data definition language (DDL) and data manipulation language (DML) statements, and return output parameters. For information about the advantages of CLR integration and choosing between managed code and [!INCLUDEtsql], see Overview of CLR Integration.
In the common language runtime (CLR), stored procedures are implemented as public static methods on a class in a [!INCLUDEmsCoName][!INCLUDEdnprdnshort] assembly. The static method can either be declared as void, or return an integer value. If it returns an integer value, the integer returned is treated as the return code from the procedure. For example:
EXECUTE @return_status = procedure_name
The @return_status variable will contain the value returned by the method. If the method is declared void, the return code is 0.
If the method takes parameters, the number of parameters in the [!INCLUDEdnprdnshort] implementation should be the same as the number of parameters used in the [!INCLUDEtsql] declaration of the stored procedure.
Parameters passed to a CLR stored procedure can be any of the native [!INCLUDEssNoVersion] types that have an equivalent in managed code. For the [!INCLUDEtsql] syntax to create the procedure, these types should be specified with the most appropriate native [!INCLUDEssNoVersion] type equivalent. For more information about type conversions, see Mapping CLR Parameter Data.
Table-valued parameters (TVPs), user-defined table types that are passed into a procedure or function, provide an efficient way to pass multiple rows of data to the server. TVPs provide similar functionality to parameter arrays, but offer greater flexibility and closer integration with [!INCLUDEtsql]. They also provide the potential for better performance. TVPs also help reduce the number of round trips to the server. Instead of sending multiple requests to the server, such as with a list of scalar parameters, data can be sent to the server as a TVP. A user-defined table type cannot be passed as a table-valued parameter to, or be returned from, a managed stored procedure or function executing in the [!INCLUDEssNoVersion] process. For more information about TVPs, see Use Table-Valued Parameters (Database Engine).
Information may be returned from [!INCLUDEdnprdnshort] stored procedures in several ways. This includes output parameters, tabular results, and messages.
As with [!INCLUDEtsql] stored procedures, information may be returned from [!INCLUDEdnprdnshort] stored procedures using OUTPUT parameters. The [!INCLUDEtsql] DML syntax used for creating [!INCLUDEdnprdnshort] stored procedures is the same as that used for creating stored procedures written in [!INCLUDEtsql]. The corresponding parameter in the implementation code in the [!INCLUDEdnprdnshort] class should use a pass-by-reference parameter as the argument. Note that Visual Basic does not support output parameters in the same way that C# does. You must specify the parameter by reference and apply the <Out()> attribute to represent an OUTPUT parameter, as in the following:
Imports System.Runtime.InteropServices
...
Public Shared Sub PriceSum ( <Out()> ByRef value As SqlInt32) The following shows a stored procedure returning information through an OUTPUT parameter:
using System;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void PriceSum(out SqlInt32 value)
{
using(SqlConnection connection = new SqlConnection("context connection=true"))
{
value = 0;
connection.Open();
SqlCommand command = new SqlCommand("SELECT Price FROM Products", connection);
SqlDataReader reader = command.ExecuteReader();
using (reader)
{
while( reader.Read() )
{
value += reader.GetSqlInt32(0);
}
}
}
}
} 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 Once the assembly containing the above CLR stored procedure has been built and created on the server, the following [!INCLUDEtsql] is used to create the procedure in the database, and specifies sum as an OUTPUT parameter.
CREATE PROCEDURE PriceSum (@sum int OUTPUT)
AS EXTERNAL NAME TestStoredProc.StoredProcedures.PriceSum
-- if StoredProcedures class was inside a namespace, called MyNS,
-- you would use:
-- AS EXTERNAL NAME TestStoredProc.[MyNS.StoredProcedures].PriceSum Note that sum is declared as an int SQL Server data type, and that the value parameter defined in the CLR stored procedure is specified as a SqlInt32 CLR data type. When a calling program executes the CLR stored procedure, [!INCLUDEssNoVersion] automatically converts the SqlInt32 CLR data type to an int[!INCLUDEssNoVersion] data type. For more information about which CLR data types can and cannot be converted, see Mapping CLR Parameter Data.
Returning tabular results and messages to the client is done through the SqlPipe object, which is obtained by using the Pipe property of the SqlContext class. The SqlPipe object has a Send method. By calling the Send method, you can transmit data through the pipe to the calling application.
These are several overloads of the SqlPipe.Send method, including one that sends a SqlDataReader and another that simply sends a text string.
Use SqlPipe.Send(string) to send messages to the client application. The text of the message is limited to 8000 characters. If the message exceeds 8000 characters, it will be truncated.
To send the results of a query directly to the client, use one of the overloads of the Execute method on the SqlPipe object. This is the most efficient way to return results to the client, since the data is transferred to the network buffers without being copied into managed memory. For example:
using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public class StoredProcedures
{
/// <summary>
/// Execute a command and send the results to the client directly.
/// </summary>
[Microsoft.SqlServer.Server.SqlProcedure]
public static void ExecuteToClient()
{
using(SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand("select @@version", connection);
SqlContext.Pipe.ExecuteAndSend(command);
}
}
} Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
'The Partial modifier is only required on one class definition per project.
Partial Public Class StoredProcedures
''' <summary>
''' Execute a command and send the results to the client directly.
''' </summary>
<Microsoft.SqlServer.Server.SqlProcedure> _
Public Shared Sub ExecuteToClient()
Using connection As New SqlConnection("context connection=true")
connection.Open()
Dim command As New SqlCommand("SELECT @@VERSION", connection)
SqlContext.Pipe.ExecuteAndSend(command)
End Using
End Sub
End Class To send the results of a query that was executed previously through the in-process provider (or to pre-process the data using a custom implementation of SqlDataReader), use the overload of the Send method that takes a SqlDataReader. This method is slightly slower than the direct method described previously, but it offers greater flexibility to manipulate the data before it is sent to the client.
using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public class StoredProcedures
{
/// <summary>
/// Execute a command and send the resulting reader to the client
/// </summary>
[Microsoft.SqlServer.Server.SqlProcedure]
public static void SendReaderToClient()
{
using(SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand("select @@version", connection);
SqlDataReader r = command.ExecuteReader();
SqlContext.Pipe.Send(r);
}
}
} Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
'The Partial modifier is only required on one class definition per project.
Partial Public Class StoredProcedures
''' <summary>
''' Execute a command and send the results to the client directly.
''' </summary>
<Microsoft.SqlServer.Server.SqlProcedure> _
Public Shared Sub SendReaderToClient()
Using connection As New SqlConnection("context connection=true")
connection.Open()
Dim command As New SqlCommand("SELECT @@VERSION", connection)
Dim reader As SqlDataReader
reader = command.ExecuteReader()
SqlContext.Pipe.Send(reader)
End Using
End Sub
End Class To create a dynamic result set, populate it and send it to the client, you can create records from the current connection and send them using SqlPipe.Send.
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
public class StoredProcedures
{
/// <summary>
/// Create a result set on the fly and send it to the client.
/// </summary>
[Microsoft.SqlServer.Server.SqlProcedure]
public static void SendTransientResultSet()
{
// Create a record object that represents an individual row, including it's metadata.
SqlDataRecord record = new SqlDataRecord(new SqlMetaData("stringcol", SqlDbType.NVarChar, 128));
// Populate the record.
record.SetSqlString(0, "Hello World!");
// Send the record to the client.
SqlContext.Pipe.Send(record);
}
} Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
'The Partial modifier is only required on one class definition per project.
Partial Public Class StoredProcedures
''' <summary>
''' Create a result set on the fly and send it to the client.
''' </summary>
<Microsoft.SqlServer.Server.SqlProcedure> _
Public Shared Sub SendTransientResultSet()
' Create a record object that represents an individual row, including it's metadata.
Dim record As New SqlDataRecord(New SqlMetaData("stringcol", SqlDbType.NVarChar, 128) )
' Populate the record.
record.SetSqlString(0, "Hello World!")
' Send the record to the client.
SqlContext.Pipe.Send(record)
End Sub
End Class Here is an example of sending a tabular result and a message through SqlPipe.
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void HelloWorld()
{
SqlContext.Pipe.Send("Hello world! It's now " + System.DateTime.Now.ToString()+"\n");
using(SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT ProductNumber FROM ProductMaster", connection);
SqlDataReader reader = command.ExecuteReader();
SqlContext.Pipe.Send(reader);
}
}
} Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
'The Partial modifier is only required on one class definition per project.
Partial Public Class StoredProcedures
''' <summary>
''' Execute a command and send the results to the client directly.
''' </summary>
<Microsoft.SqlServer.Server.SqlProcedure> _
Public Shared Sub HelloWorld()
SqlContext.Pipe.Send("Hello world! It's now " & System.DateTime.Now.ToString() & "\n")
Using connection As New SqlConnection("context connection=true")
connection.Open()
Dim command As New SqlCommand("SELECT ProductNumber FROM ProductMaster", connection)
Dim reader As SqlDataReader
reader = command.ExecuteReader()
SqlContext.Pipe.Send(reader)
End Using
End Sub
End Class The first Send sends a message to the client, while the second sends a tabular result using SqlDataReader.
Note that these examples are for illustrative purposes only. CLR functions are more appropriate than simple [!INCLUDEtsql] statements for computation-intensive applications. An almost equivalent [!INCLUDEtsql] stored procedure to the previous example is:
CREATE PROCEDURE HelloWorld() AS
BEGIN
PRINT('Hello world!')
SELECT ProductNumber FROM ProductMaster
END; Note
Messages and result sets are retrieved differently in the client application. For instance, [!INCLUDEssManStudioFull] result sets appear in the Results view, and messages appear in the Messages pane.
If the above Visual C# code is saved in a file MyFirstUdp.cs and compiled with:
csc /t:library /out:MyFirstUdp.dll MyFirstUdp.cs Or, if the above Visual Basic code is saved in a file MyFirstUdp.vb and compiled with:
vbc /t:library /out:MyFirstUdp.dll MyFirstUdp.vb Note
Beginning with [!INCLUDEssVersion2005], Visual C++ database objects (such as stored procedures) compiled with /clr:pure are not supported for execution.
The resulting assembly can be registered, and the entry point invoked, with the following DDL:
CREATE ASSEMBLY MyFirstUdp FROM 'C:\Programming\MyFirstUdp.dll';
CREATE PROCEDURE HelloWorld
AS EXTERNAL NAME MyFirstUdp.StoredProcedures.HelloWorld;
EXEC HelloWorld; CLR User-Defined Functions
CLR User-Defined Types
CLR Triggers