Trabalhando com CLR: Stored Procedure

Depois de uma breve introdução sobre SQL CLR, para a nossa primeira funcionalidade da série de artigos sobre SQL CLR, vamos tratar das Stored Procedure (procedimento armazenado), que podem ser utilizadas basicamente para retornar valores, várias consultas e/ou manipular dados no SQL Server.

Depois de criado o projeto SQL CLR, para criarmos uma Stored Procedure com CLR, nós precisaremos adicionar um novo item (“New Item...” ou “Stored Procedure...”) no projeto, determinando também as características básicas deste item, como o nome do arquivo e o template, no casso “Stored Procedure”.

Já com a Stored Procedure adicionada no projeto, partimos para o código da nossa primeira funcionalidade, que é basicamente imprimir um texto qualquer nas mensagens de notificação (como uma chamada ao comando PRINT do SQL).

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[SqlProcedure]

public static void Primeiro()

{

SqlContext.Pipe.Send("SQL From Hell!!!");

}

};

E adicionamos o seguinte comando SQL no arquivo script de teste do projeto para executar nossa Stored Procedure.

EXEC Primeiro

Mais informações sobre DEBUG de projetos SQL CLR:
http://sqlfromhell.wordpress.com/2009/10/03/debug-clr

Após o Debug (F5), podemos validar o resultado na caixa de Output do Visual Studio.

O segundo passo para desenvolver nosso procedimento armazenado é a criação de parâmetros, que basicamente consiste na criação de novos parâmetros no método.

using System;

using System.Data;

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[SqlProcedure]

public static void Primeiro(SqlString texto)

{

SqlContext.Pipe.Send(texto.ToString());

}

};

EXEC Primeiro "SQL From Hell!!!"

E após o deploy deste procedimento armazenado, percebemos que o nome do método (ex.: “Primeiro”) também será o nome do procedimento armazenado no banco de dados, assim como os argumentos.

A possibilidade de criarmos sobrecargas uma procedure (dois ou mais métodos com mesmo nome, mas como parâmetros distintos) não é permitida pelo SQL Server, mesmo que seja possível gerar um assembly compilável. Desta forma é recomendado definir nomes adequados para os nomes dos métodos para que não se repitam.

Mas para não ser necessário alterar os nomes dos métodos podemos determinar os nomes das procedures, simplesmente pela customização do atributo SqlProcedure, conforme o exemplo:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[SqlProcedure(Name = "USP_Primeiro")]

public static void Primeiro(SqlString texto)

{

SqlContext.Pipe.Send(texto.ToString());

}

};

EXEC USP_Primeiro "SQL From Hell!!!"

Que se refletirá no banco de dados, sem necessidade de customização do script SQL de deploy do projeto CLR, conforme demonstrado abaixo:

Mais informações sobre Deploy de projetos SQL CLR:
http://sqlfromhell.wordpress.com/2010/05/15/publicando-assemblies-clr-no-sql-server-pelo-visual-studio

Assim como podemos customizar o nome dos procedimentos armazenados, também é possível customizar algumas características dos parâmetros do método por meio de atributos (ex.: SqlFacet), conforme demonstrada abaixo a definição da quantidade máxima de 40 caracteres do parâmetro “texto”:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[SqlProcedure(Name = "USP_Primeiro")]

public static void Primeiro

([SqlFacet(MaxSize = 40)]SqlString texto)

{

SqlContext.Pipe.Send(texto.ToString());

}

};

EXEC USP_Primeiro "SQL From Hell!!!"

E como resultado, novamente sem qualquer customização no script SQL de deploy:

Desta forma, também será possível tratar as características dos tipos decimais (ou numéricos), como no exemplo que determinamos um parâmetro decimal de tamanho 10 (precisão), tendo duas casas após a vírgula (escala).

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[SqlProcedure(Name = "USP_Primeiro")]

public static void Primeiro

([SqlFacet(Precision = 10, Scale = 2)]SqlDecimal valor)

{

SqlContext.Pipe.Send(valor.ToString());

}

};

EXEC USP_Primeiro 12345678.90

Após o deploy, os valores determinados pelos atributos estarão refletidos no banco de dados, sem qualquer intervenção direta do desenvolvedor no script SQL:

Outras características também podem ser trabalhadas com os atributos, como atributos que recebem valores do procedimento armazenado (OUTPUT):

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[SqlProcedure(Name = "USP_Primeiro")]

public static void Primeiro

(out SqlString texto)

{

texto = new SqlString("Executado");

}

};

DECLARE @VAR NVARCHAR(4000)

EXEC USP_Primeiro @VAR OUT

PRINT @VAR

Ou parâmetros que têm seus valores alterados durante a execução de procedimentos armazenados (INPUT/OUTPUT):

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[SqlProcedure(Name = "USP_Primeiro")]

public static void Primeiro

(ref SqlString texto)

{

SqlContext.Pipe.Send(texto.ToString());

texto = new SqlString("VALOR 2");

}

};

DECLARE @VAR NVARCHAR(4000)

SET @VAR = N"VALOR 1"

EXEC USP_Primeiro @VAR OUT

PRINT @VAR

Para as duas situações demonstradas anteriormente, o deploy se encarregará de criar estruturas adequadas para os procedimentos armazenados.

Com os procedimentos armazenados também é possível retornar valores inteiros, como detalhado abaixo.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[SqlProcedure(Name = "USP_Primeiro")]

public static SqlInt32 Primeiro()

{

return 333;

}

};

DECLARE @VAR INT

EXEC @VAR = USP_Primeiro

PRINT @VAR

Concluindo este artigo, espero que tenham gostado desta primeira demonstração dos procedimentos armazenados, onde podemos identificar conceitos básicos de como o deploy se comporta com a customização do CLR, e a facilidade de customizar características dos procedimentos armazenados, sem a necessidade de envolvimento direto com o script de deploy.

No próximo artigo, estarei trabalhando as três principais formas de trabalhar com Stored Procedures desenvolvidas em CLR para retornar resultados de consultas ou simplesmente criar um resultado qualquer.

Banco de Dados - SQL Server

Leia também