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 acerca de las ventajas de la integración CLR y el modo de elegir entre código administrado y Transact-SQL, vea Información general de 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 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 variable @return_status 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 toma parámetros, el número de parámetros de la implementación de .NET Framework debe ser igual al número de parámetros utilizados 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 crear el procedimiento mediante la sintaxis de Transact-SQL, estos tipos deben especificarse con el tipo nativo de SQL Server equivalente más adecuado. Para obtener más información acerca de las conversiones de tipos, vea Asignar datos de parámetros CLR.

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 presentan una funcionalidad similar a las matrices de parámetros, pero proporcionan más flexibilidad y una mayor integración 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 puede pasarse como un parámetro con valores de tabla a un procedimiento almacenado administrado o a una función que se ejecuta en el proceso de SQL Server, así como tampoco puede devolverse desde dicho procedimiento o función. Para obtener más información acerca de los TVP, vea Parámetros de valores de tabla (motor de base de datos).

Devolver resultados de los procedimientos almacenados CLR

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

Parámetros OUTPUT y procedimientos almacenados CLR

Al igual que ocurre con los procedimientos almacenados de Transact-SQL, puede devolverse información de los procedimientos almacenados de .NET Framework mediante el uso de parámetros OUTPUT. La sintaxis DML de Transact-SQL que se utiliza para crear procedimientos almacenados de .NET Framework es igual que la que se utiliza 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 de paso por referencia como argumento. Tenga en cuenta que Visual Basic no admite parámetros de salida del mismo modo en que lo hace Visual C#. Debe especificar el parámetro por referencia y aplicar el atributo <Out()> para representar un parámetro OUTPUT, tal y 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:

C#

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);
            }
         }         
      }
   }
}

Visual Basic

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 generado y creado en el servidor el ensamblado que contiene el procedimiento almacenado CLR anterior, el siguiente código de Transact-SQL se usa para crear el procedimiento en la base de datos y especifica sum como parámetro OUTPUT.

CREATE PROCEDURE PriceSum (@sum int OUTPUT)
AS EXTERNAL NAME TestStoredProc.StoredProcedures.PriceSum

Tenga en cuenta que sum se declara como un tipo de datos int de SQL Server y que el parámetro value definido en el procedimiento almacenado CLR se especifica como un tipo de datos SqlInt32 de CLR. Cuando un programa de llamada ejecuta el procedimiento almacenado CLR, SQL Server convierte automáticamente el tipo de datos SqlInt32 de CLR en un tipo de datos int de SQL Server. Para obtener más información acerca de los tipos de datos CLR que pueden y no pueden convertirse, vea Asignar 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:

[C#]

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);
      }
   }
}

[Visual Basic]

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);
      }
   }
}

[Visual Basic]

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);
   }
}

[Visual Basic]

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);
      }
   }
}

[Visual Basic]

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 resultan más apropiadas que las instrucciones Transact-SQL simples para las aplicaciones que requieren un uso intensivo de los recursos para realizar cálculos. Un procedimiento almacenado Transact-SQL casi equivalente al ejemplo anterior es el siguiente:

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, 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, no se admiten la ejecución de objetos de base de datos de Visual C++ (como procedimientos almacenados) compilados con /clr:pure.

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

Nota

A partir de SQL Server 2005, en una base de datos SQL Server con un nivel de compatibilidad de "80", no pueden crearse tipos administrados definidos por el usuario, procedimientos almacenados, funciones, agregados o desencadenadores. Para aprovechar estas características de integración CLR de SQL Server, debe usar el procedimiento almacenado sp_dbcmptlevel a fin de establecer el nivel de compatibilidad de la base de datos en "100".

Vea también

Conceptos

Tipos definidos por el usuario de CLR

Desencadenadores CLR

Opción del nivel de compatibilidad de la base de datos

Otros recursos

Funciones CLR definidas por el usuario