Procedimientos almacenados CLR

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

Requisitos para 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 se puede declarar como void o devolver un valor entero. Si devuelve un valor entero, el entero devuelto se trata como el código devuelto 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 void, el código de retorno es 0.

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

Los parámetros pasados a un procedimiento almacenado CLR pueden ser cualquiera de los tipos nativos de SQL Server que tienen un equivalente en código administrado. Para que la sintaxis de Transact-SQL cree el procedimiento, estos tipos deben especificarse con el tipo de SQL Server nativo más adecuado equivalente. Para obtener más información sobre las conversiones de tipos, vea Asignación de datos de parámetros CLR.

Parámetros de Table-Valued

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 e 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 procedimientos almacenados CLR

La información se puede devolver desde procedimientos almacenados de .NET Framework de varias maneras. Esto incluye parámetros de salida, resultados tabulares y mensajes.

Parámetros OUTPUT y procedimientos almacenados CLR

Al igual que con Transact-SQL procedimientos almacenados, se puede devolver información de procedimientos almacenados de .NET Framework mediante parámetros OUTPUT. La sintaxis DML de Transact-SQL usada para crear procedimientos almacenados de .NET Framework es la misma que la que se usa para crear procedimientos almacenados escritos en Transact-SQL. El parámetro correspondiente en el código de implementación de la clase .NET Framework debe usar un parámetro pass-by-reference como argumento. Tenga en cuenta que Visual Basic no admite parámetros de salida de la misma manera que lo hace 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 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 de 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 de SQL Server. Para obtener más información sobre qué tipos de datos CLR pueden y no se pueden convertir, consulte Asignación de datos de parámetros CLR.

Devolver resultados tabulares y mensajes

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

Estas son varias sobrecargas del SqlPipe.Send método, incluida una que envía una 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 Execute método en el SqlPipe objeto . Esta es la manera más eficaz de devolver resultados al cliente, ya 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 que se ejecutó anteriormente a través del proveedor en proceso (o para preprocesar los datos mediante una implementación personalizada de SqlDataReader), use la sobrecarga del Send método que toma un SqlDataReader. Este método es ligeramente 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ámicos, rellenarlo y enviarlo al cliente, puede crear registros desde 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   

Este es un ejemplo de envío de un resultado tabular y un mensaje 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 primero Send envía un mensaje al cliente, mientras que el segundo envía un resultado tabular mediante SqlDataReader.

Tenga en cuenta que estos ejemplos solo tienen fines ilustrativos. Las funciones CLR son más adecuadas que las instrucciones de Transact-SQL simples para aplicaciones que consumen muchos cálculos. Un procedimiento almacenado casi equivalente Transact-SQL 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 forma diferente en la aplicación cliente. Por ejemplo, los conjuntos de resultados de SQL Server Management Studio 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 procedimientos almacenados) compilados con /clr:pure no se admiten para su ejecución.

El ensamblado resultante se puede registrar 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;  

Véase también

Funciones de User-Defined CLR
Tipos de User-Defined CLR
Desencadenadores CLR