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).
SqlContext.Pipe.Send("SQL From Hell!!!");
E adicionamos o seguinte comando SQL no arquivo script de teste do projeto para executar nossa Stored Procedure.
Mais informações sobre DEBUG de projetos SQL 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.
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:
[SqlProcedure(Name = "USP_Primeiro")]
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:
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":
public static void Primeiro
([SqlFacet(MaxSize = 40)]SqlString texto)
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).
([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):
(out SqlString texto)
texto = new SqlString("Executado");
EXEC USP_Primeiro @VAR OUT
Ou parâmetros que têm seus valores alterados durante a execução de procedimentos armazenados (INPUT/OUTPUT):
(ref SqlString texto)
texto = new SqlString("VALOR 2");
SET @VAR = N"VALOR 1"
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.
public static SqlInt32 Primeiro()
EXEC @VAR = USP_Primeiro
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.