Artigo
09/28/2010

Procedimentos armazenados CLR

Os procedimentos armazenados são rotinas que não podem ser usadas em expressões escalares. Diferentemente das funções escalares, eles podem retornar resultados tabulares e mensagens para o cliente, invocar instruções DDL (linguagem de definição de dados) e DML (linguagem de manipulação de dados) e retornar parâmetros de saída. Para obter informações sobre as vantagens da integração CLR e a escolha entre o código gerenciado e Transact-SQL, consulte Visão geral da integração CLR.

Requisitos dos procedimentos armazenados CLR

No CLR, os procedimentos armazenados são implementados como métodos estáticos públicos em uma classe de um assembly Microsoft.NET Framework. O método estático pode ser declarado como nulo ou retornar um valor inteiro. Se retornar um valor inteiro, o inteiro retornado será tratado como o código de retorno do procedimento. Por exemplo:

EXECUTE @return\_status = procedure_name

A variável @return\_status conterá o valor retornado pelo método. Se o método for declarado nulo, o código de retorno será 0.

Se o método assumir parâmetros, o número de parâmetros na implementação .NET Framework deverá ser o mesmo daqueles usados na declaração Transact-SQL do procedimento armazenado.

Os parâmetros passados para um procedimento armazenado CLR podem ser de qualquer um dos tipos do SQL Server nativos que tenham um equivalente em código gerenciado. Para que a sintaxe Transact-SQL crie o procedimento, esses tipos devem ser especificados com o equivalente do tipo do SQL Server nativo mais apropriado. Para obter mais informações sobre conversões de tipo, consulte Mapeando dados de parâmetro CLR.

Parâmetros com valor de tabela

Os TVPs (parâmetros com valor de tabela), ou seja, tipos de tabela definidos pelo usuário transmitidos para um procedimento ou uma função, oferecem uma maneira eficiente de passar várias linhas de dados para o servidor. Os TVPs proporcionam funcionalidade semelhante para matrizes de parâmetro, porém com maior flexibilidade e integração com Transact-SQL. Também garantem o potencial de melhor desempenho. Os TVPs também ajudam a reduzir o número de viagens de ida e volta para o servidor. Em vez de enviar várias solicitações ao servidor, como com uma lista de parâmetros escalares, os dados podem ser enviados ao servidor como um TVP. Um tipo de tabela definido pelo usuário não pode ser passado como um parâmetro com valor de tabela para, ou ser retornado de, um procedimento armazenado ou função gerenciada(o) que é executada(o) no processo do SQL Server. Para obter mais informações sobre TVPs, consulte Parâmetros com valor de tabela (Mecanismo de Banco de Dados).

Retornando resultados de procedimentos armazenados CLR

As informações podem ser retornadas dos procedimentos armazenados do .NET Framework de várias maneiras. Isso inclui parâmetros de saída, resultados tabulares e mensagens.

Parâmetros OUTPUT e procedimentos armazenados CLR

Assim como nos procedimentos armazenados Transact-SQL, as informações podem ser retornadas dos procedimentos armazenados do .NET Framework que usam parâmetros OUTPUT. A sintaxe de DML Transact-SQL usada para criar procedimentos armazenados do .NET Framework é a mesma usada para criar procedimentos armazenados escritos em Transact-SQL. O parâmetro correspondente no código de implementação da classe do .NET Framework deve usar um parâmetro de passagem-por-referência como argumento. Observe que o Visual Basic não suporta parâmetros de saída da mesma maneira que o Visual C#. Você precisa especificar o parâmetro por referência e aplicar o atributo <Out()> para representar um parâmetro OUTPUT, da seguinte maneira:

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

A seguir está um procedimento armazenado que retorna informações por um 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);
            }
         }         
      }
   }
}

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

Depois de o assembly que contém o procedimento armazenado CLR acima tiver sido compilado e criado no servidor, o Transact-SQL a seguir será usado para criar o procedimento no banco de dados e especificará sum como parâmetro OUTPUT.

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

Observe que sum é declarado como um tipo de dados int do SQL Server e que o parâmetro value definido no procedimento armazenado CLR é especificado como tipo de dados CLR SqlInt32. Quando um programa de chamada executar o procedimento armazenado CLR, o SQL Server converterá automaticamente o tipo de dados CLR SqlInt32 em um tipo de dados intSQL Server. Para obter mais informações sobre quais tipos de dados CLR podem e não podem ser convertidos, consulte Mapeando dados de parâmetro CLR.

Retornando resultados tabulares e mensagens

O retorno dos resultados tabulares e mensagens para o cliente é executado através do objeto SqlPipe, que é obtido usando a propriedade Pipe da classe SqlContext. O objeto SqlPipe tem um método Send. Chamando o método Send, você pode transmitir dados pelo pipe para o aplicativo de chamada.

Há várias sobrecargas do método SqlPipe.Send, incluindo uma que envia um SqlDataReader e outra que simplesmente envia uma cadeia de caracteres de texto.

Retornando mensagens

Use SqlPipe.Send(string) para enviar mensagens para o aplicativo cliente. O texto da mensagem é limitado a 8000 caracteres. Se a mensagem ultrapassar os 8000 caracteres, ela será truncada.

Retornando resultados tabulares

Para enviar os resultados de uma consulta diretamente para o cliente, use uma das sobrecargas do método Execute no objeto SqlPipe. Essa é a maneira mais eficiente de retornar os resultados para o cliente, porque os dados são transferidos para os buffers de rede sem ser copiados para a memória gerenciada. Por exemplo:

[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 os resultados de uma consulta que foi executada anteriormente através do provedor interno ao processo (ou pré-processar os dados usando uma implementação personalizada de SqlDataReader), use a sobrecarga do método Send que assume um SqlDataReader. Esse método é ligeiramente mais lento do que o método direto descrito anteriormente, mas oferece maior flexibilidade para manipular os dados antes de serem enviados para o 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 criar um conjunto de resultados dinâmicos, preenchê-lo e enviá-lo para o cliente, você pode criar registros da conexão atual e enviá-los usando 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 seguir está um exemplo de envio de um resultado tabular e uma mensagem atravé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

O primeiro Send envia uma mensagem para o cliente, enquanto o segundo envia um resultado tabular que usa SqlDataReader.

Observe que esses exemplos são meramente para fins ilustrativos. As funções CLR são mais apropriadas do que instruções Transact-SQL simples para aplicativos com uso intenso de cálculos. Um procedimento armazenado Transact-SQL quase equivalente ao exemplo anterior é:

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

Observação
Mensagens e conjuntos de resultados são recuperados de maneira diferente no aplicativo cliente. Por exemplo, os conjuntos de resultados SQL Server Management Studio aparecem na exibição Resultados, enquanto as mensagens aparecem no painel Mensagens.

Se o código Visual C# anterior for salvo em um arquivo MyFirstUdp.cs e compilado com:

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

Ou então, se o código Visual Basic anterior for salvo em um arquivo MyFirstUdp.vb e compilado com:

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

Observação
A partir do SQL Server 2005, os objetos de banco de dados de Visual C++ (como os procedimentos armazenados) compilados com /clr:pure não são suportados para execução.

O assembly resultante pode ser registrado, e o ponto de entrada invocado, com o DDL a seguir:

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

Observação
A partir do SQL Server 2005, em um banco de dados do SQL Server com um nível de compatibilidade "80", não é possível criar gatilhos, agregações, funções, procedimentos armazenados ou tipos de dados definidos pelo usuário gerenciados. Para tirar proveito desses recursos de integração CLR do SQL Server, você precisa usar o procedimento armazenado sp_dbcmptlevel para definir o nível de compatibilidade do banco de dados como "100".

A partir do SQL Server 2005, em um banco de dados do SQL Server com um nível de compatibilidade "80", não é possível criar gatilhos, agregações, funções, procedimentos armazenados ou tipos de dados definidos pelo usuário gerenciados. Para tirar proveito desses recursos de integração CLR do SQL Server, você precisa usar o procedimento armazenado sp_dbcmptlevel para definir o nível de compatibilidade do banco de dados como "100".