Procedimientos almacenados de CLR

Los procedimientos almacenados son rutinas que no pueden usarse en expresiones escalares. A diferencia de las funciones escalares, pueden devolver mensajes y resultados tabulares al cliente, invocar instrucciones del lenguaje de definición de datos (DDL) e instrucciones del lenguaje de manipulación de datos (DML), así como devolver parámetros de salida. Para obtener información sobre las ventajas de la integración clR y la elección entre código administrado y Transact-SQL, consulte Información general sobre la integración clR.

Requisitos de los procedimientos almacenados CLR

En Common Language Runtime (CLR), los procedimientos almacenados se implementan como métodos estáticos públicos en una clase de un ensamblado de Microsoft.NET Framework. El método estático puede declararse como void o devolver un valor entero. Si devuelve un valor entero, el entero devuelto se trata como el código de retorno del procedimiento. Por ejemplo:

EXECUTE @return_status = procedure_name

La @return_status variable contendrá el valor devuelto por el método . Si el método se declara como void, el código de retorno es 0.

Si el método acepta parámetros, el número de parámetros en la implementación de .NET Framework debe ser igual al número de parámetros usados en la declaración Transact-SQL del procedimiento almacenado.

Los parámetros que se pasan a un procedimiento almacenado CLR pueden ser cualquiera de los tipos nativos de SQL Server que tengan un equivalente en código administrado. Para que la sintaxis Transact-SQL cree el procedimiento, estos tipos se deben especificar con el equivalente del tipo nativo de SQL más adecuado. Para obtener más información sobre las conversiones de tipos, vea Mapping CLR Parameter Data.

Parámetros con valores de tabla

Los parámetros con valores de tabla (TVP), tipos de tabla definidos por el usuario que se pasan a un procedimiento o función, proporcionan un modo eficaz de pasar varias filas de datos al servidor. Los TVP proporcionan una funcionalidad similar a las matrices de parámetros, pero ofrecen mayor flexibilidad y una integración más estrecha con Transact-SQL. También proporcionan la posibilidad de obtener mayor rendimiento. Además, los TVP ayudan a reducir el número de ciclos de ida y vuelta al servidor. En lugar de enviar varias solicitudes al servidor, como con una lista de parámetros escalares, los datos pueden enviarse al servidor como un TVP. Un tipo de tabla definido por el usuario no se puede pasar como un parámetro con valores de tabla a un procedimiento almacenado administrado o una función que se ejecuta en el proceso de SQL Server. Para obtener más información sobre los TVP, vea Usar parámetros de Table-Valued (motor de base de datos).

Devolver resultados de los procedimientos almacenados CLR

La información se puede devolver de los procedimientos almacenados de .NET Framework de varias maneras. entre los que se incluyen parámetros de salida, resultados tabulares y mensajes.

Parámetros OUTPUT y procedimientos almacenados CLR

Al igual que con los procedimientos almacenados de Transact-SQL, se puede devolver información de procedimientos almacenados de .NET Framework mediante parámetros OUTPUT. La sintaxis DML de Transact-SQL usada para la creación de procedimientos almacenados de .NET Framework es la misma que se utiliza para la creación de procedimientos almacenados escritos en Transact-SQL. El parámetro correspondiente en el código de implementación de la clase de .NET Framework debe utilizar un parámetro de paso por referencia como argumento. Tenga en cuenta que Visual Basic no admite parámetros de salida de la misma manera que C#. Debe especificar el parámetro por referencia y aplicar el <atributo Out()> para representar un parámetro OUTPUT, como se muestra a continuación:

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

A continuación se muestra un procedimiento almacenado que devuelve información a través de un parámetro OUTPUT:

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  

Una vez que el ensamblado que contiene el procedimiento almacenado CLR anterior se ha compilado y creado en el servidor, se usa el siguiente Transact-SQL para crear el procedimiento en la base de datos y especifica la suma como un parámetro OUTPUT.

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  

Tenga en cuenta que la suma se declara como un int tipo de datos SQL Server y que el parámetro de valor definido en el procedimiento almacenado CLR se especifica como un SqlInt32 tipo de datos CLR. Cuando un programa de llamada ejecuta el procedimiento almacenado CLR, SQL Server convierte automáticamente el SqlInt32 tipo de datos CLR en un inttipo de datos SQL Server. Para obtener más información sobre qué tipos de datos CLR se pueden convertir y no se pueden convertir, vea Asignación de datos de parámetros CLR.

Devolver mensajes y resultados tabulares

La devolución de mensajes y resultados tabulares al cliente se realiza a través del objeto SqlPipe, que se obtiene utilizando la propiedad Pipe de la clase SqlContext. El objeto SqlPipe tiene un método Send. Al llamar al método Send, puede transmitir datos a la aplicación que realiza la llamada a través de la canalización.

Existen varias sobrecargas del método SqlPipe.Send, incluida una que envía SqlDataReader y otra que simplemente envía una cadena de texto.

Devolver mensajes

Use SqlPipe.Send(string) para enviar mensajes a la aplicación cliente. El texto del mensaje está limitado a 8000 caracteres. Si el mensaje supera los 8000 caracteres, se truncará.

Devolver resultados tabulares

Para enviar los resultados de una consulta directamente al cliente, use una de las sobrecargas del método Execute en el objeto SqlPipe. Se trata del modo más eficaz de devolver resultados al cliente, puesto que los datos se transfieren a los búferes de red sin copiarse en la memoria administrada. Por ejemplo:

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  

Para enviar los resultados de una consulta previamente ejecutada a través del proveedor en proceso (o para preprocesar los datos mediante una implementación personalizada de SqlDataReader), use la sobrecarga del método Send que toma SqlDataReader. Este método es algo más lento que el método directo descrito anteriormente, pero ofrece mayor flexibilidad para manipular los datos antes de enviarlos al cliente.

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  

Para crear un conjunto de resultados dinámico, rellénelo y envíeselo al cliente; puede crear registros de la conexión actual y enviarlos mediante 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   

A continuación se muestra un ejemplo del envío de un mensaje y un resultado tabular a través de 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   

El primer Send envía un mensaje al cliente, mientras que el segundo envía un resultado tabular mediante SqlDataReader.

Tenga en cuenta que se trata de ejemplos meramente ilustrativos. Las funciones CLR son más adecuadas que las instrucciones Transact-SQL simples para aplicaciones que consumen muchos cálculos. Un procedimiento almacenado de Transact-SQL casi equivalente al ejemplo anterior es:

CREATE PROCEDURE HelloWorld() AS  
BEGIN  
PRINT('Hello world!')  
SELECT ProductNumber FROM ProductMaster  
END;  

Nota

Los mensajes y los conjuntos de resultados se recuperan de manera diferente en la aplicación cliente. Por ejemplo, SQL Server Management Studio conjuntos de resultados aparecen en la vista Resultados y los mensajes aparecen en el panel Mensajes.

Si el código de Visual C# anterior se guarda en un archivo MyFirstUdp.cs y se compila con:

csc /t:library /out:MyFirstUdp.dll MyFirstUdp.cs   

O bien, si el código de Visual Basic anterior se guarda en un archivo MyFirstUdp.vb y se compila con:

vbc /t:library /out:MyFirstUdp.dll MyFirstUdp.vb   

Nota

A partir de SQL Server 2005, los objetos de base de datos de Visual C++ (como los procedimientos almacenados) compilados con /clr:pure no se admiten para su ejecución.

Es posible registrar el ensamblado resultante y el punto de entrada invocado con el siguiente DDL:

CREATE ASSEMBLY MyFirstUdp FROM 'C:\Programming\MyFirstUdp.dll';  
CREATE PROCEDURE HelloWorld  
AS EXTERNAL NAME MyFirstUdp.StoredProcedures.HelloWorld;  
EXEC HelloWorld;  

Consulte también

Funciones CLR definidas por el usuario
Tipos definidos por el usuario CLR
Desencadenadores de CLR