Stored Procedure Dinâmica

Neste artigo o autor mostra como desenvolver e utilizar SQL Dinâmico com Stored Procedure.

por Mauricio Junior

Olá pessoal, mostrarei com este artigo como desenvolver e utilizar SQL Dinâmico com Stored Procedure. Algumas empresas não gostam ou não aceitam muito as mesmas dinâmicas, mas isso é de empresa para empresa. Elas podem ajudar muito ao desenvolvedor agregando vários tipos de SELECTs ou qualquer outro tipo de função de acordo com as condições.

Pré-requisito:

- Banco de dados SQL Server 2000.

- Um pouco de conhecimento sobre SP (Stored Procedure)

- Enterprise Manager

- Query Analyzer

Gosto muito de mostrar na prática os meus artigos, porém tente fazê-lo passo a passo, seguindo-os corretamente para não ter problemas mais a frente. Qualquer dúvida poderá entrar em contato.

Na Prática do Banco de Dados

Para começar direto na prática, estou usando Enterprise Manager para gerar os SELECTs, criar tabelas e stored procedures no banco de dados. Segue a ilustração para reconhecimento.

Criei um banco de dados chamado BD_Dinamico. Em seguida criei uma tabela dentro do banco chamada TB001_Usuario. Irei mexer dentro dela inserindo parâmetros e finalmente executando um select dinamicamente.

Nome do banco de dados: BD_Dinamico Tabela do banco: TB001_Usuario Campos: Tipo: ID_USU uniqueidentifier NO_USU nvarchar (100) DT_NASC_USU datetime EMAIL_USU nvarchar(50)

A ilustração mostra perfeitamente como ficou a tabela do banco de dados.

Coloquei o campo ID_USU como uniqueidentifie para inserir um número difícil de ser burlado ou copiado. É gerado automaticamente. O GUID é um conjunto de caracteres criados a cada valor inserido no banco de dados. Clicando no campo ID_USU, o mesmo existe na opção COLUMNS com o nome Is RowGuid. O valor desse campo é Yes para que seja gerado automaticamente.

Pronto, depois que criei o banco de dados, tabela e campos; irei inserir alguns valores e desenvolver o SELECT dinâmico. O próximo passo é preencher os campos da tabela com valores; em seguida filtrá-los com SQL dinâmico.

Note que o campo ID_USU foi gerado com alguns caracteres como de criptografia de dados. O mesmo acontece porque foi selecionado o GUID.

SQL Dinâmico com Stored Procedure

Para começar a desenvolver um SQL dinâmico, cliquei na opção stored procedure e depois cliquei com o botão direito do mouse. logo depois cliquei na opção New Stored Procedure... Como desenvolvedor, não posso esquecer de falar para sempre comentar em suas stored procedures, qualquer pessoa ou você mesmo se lembre do que foi feito.

Quando digo a idéia de criar SP dinâmica, isto quer dizer em pesquisar tanto pelo Nome do Usuário, quanto data de nascimento e e-mail. Isso acaba economizando em torno de no mínimo 4 SPs.

Segue a primeira tela de criação da mesma:

Criei a SP, segue o código:

CREATE PROCEDURE DINAMICASP001_CONSUTAUSUARIO @NO_USU AS VARCHAR(200), @DT_NASC_USU AS DATETIME, @EMAIL_USU AS VARCHAR(100) AS -- ######### DECLARACAO DA VARIAVEL DE SQL PARA SER USADA NO DECORRER DA SP DECLARE @SQL AS VARCHAR(4000) -- ######### MONTANDO SELECT PARA EXECUTAR MAIS TARDE SET @SQL = " SELECT ID_USU, NO_USU, DT_NASC_USU, EMAIL_USU FROM TB001_USUARIO WHERE 1=1 " -- ######### VERIFICANDO CONDICOES PARA GERAR SQL IF (@NO_USU IS NOT NULL) BEGIN SET @SQL = @SQL + " AND NO_USU = "" + CONVERT(VARCHAR, @NO_USU) + """ END IF (@DT_NASC_USU IS NOT NULL) BEGIN SET @SQL = @SQL + " AND DT_NASC_USU = "" + CONVERT(VARCHAR, @DT_NASC_USU) + """ END IF (@EMAIL_USU IS NOT NULL) BEGIN SET @SQL = @SQL + " AND EMAIL_USU = "" + CONVERT(VARCHAR, @EMAIL_USU) + """ END --#### EXECUTANDO SQL MONTADO ANTERIORMENTE EXECUTE(@SQL) -- #### COM O PRINT O MESMO MOSTRA O SQL QUE FOI MONTADO. SOH DESCOMENTAR. -- PRINT(@SQL) GO

Figura de ilustração:

Explicação:

Antes de tudo, note que criei uma SP começando com o nome DINAMICASP001_CONSUTAUSUARIO; isso tem uma razão legal. O sistema do banco de dados SQL SERVER internamente faz um “for” pegando todas as STORED PROCEDURE que começam com SP portanto, se no sistema possuir muitas SPs, isso pode influenciar um pouco no tempo de execução. Quando são poucas, esse tempo nem dá para perceber. Não esqueça de que esse nome pode ir até 30 caracteres, se passar disso, a IDE reclama.

As primeiras linhas que descrevi, foram apenas declarações de parâmetros que terão passadas obrigatoriamente. Não importa que valor passado, só deve ser passado.

-- ######### DECLARACAO DA VARIAVEL DE SQL PARA SER USADA NO DECORRER DA SP DECLARE @SQL AS VARCHAR(4000)

Essa declaração de variável chamada @SQL é porque usarei a mesma para montar o script dinamicamente, ou seja, de acordo com o que usuário ou sistema mandou.

-- ######### MONTANDO SELECT PARA EXECUTAR MAIS TARDE SET @SQL = " SELECT ID_USU, NO_USU, DT_NASC_USU, EMAIL_USU FROM TB001_USUARIO WHERE 1=1 "

Nessa instrução acima, monto apenas o select com os campos e tabela mantendo uma condição onde 1=1 e não ter um trabalho de montar uma flag para controlar isso.

-- ######### VERIFICANDO CONDICOES PARA GERAR SQL IF (@NO_USU IS NOT NULL) BEGIN SET @SQL = @SQL + " AND NO_USU = "" + CONVERT(VARCHAR, @NO_USU) + """ END IF (@DT_NASC_USU IS NOT NULL) BEGIN SET @SQL = @SQL + " AND DT_NASC_USU = "" + CONVERT(VARCHAR, @DT_NASC_USU) + """ END IF (@EMAIL_USU IS NOT NULL) BEGIN SET @SQL = @SQL + " AND EMAIL_USU = "" + CONVERT(VARCHAR, @EMAIL_USU) + """ END

Aqui eu verifico primeiramente as condições de parâmetros passados para depois criar e adicionar a variável @SQL o que desejo buscar. Fiz isso para todos os parâmetros de entrada, porém nenhum é obrigatório, se fosse obrigatório já teria colocar em primeira condição do @SQL economizando algumas linhas de código. Por exemplo, se o nome do usuário for passado, é concatenada automaticamente a condição ao meu script @SQL. Isso acontece com todos os parâmetros que são passados.

Para verificar as condições, note que sempre faço uma verificar perguntando se é diferente de NULL. Fiz isso porque nenhum parâmetro é do tipo int, se a mesma for, a condição de verificação seria; perguntando se é diferente de 0 (zero).

--#### EXECUTANDO SQL MONTADO ANTERIORMENTE EXECUTE(@SQL)

Finalmente depois que tudo é montado, executo a variável declarada no começo da STORED PROCEDURE contendo os valores que foram adicionados. Para imprimir o que foi montado pelas condições, basta comentar esta linha do código e usar a linha seguinte:

-- #### COM O PRINT O MESMO MOSTRA O SQL QUE FOI MONTADO. SOH DESCOMENTAR. PRINT(@SQL)

Todas as linhas que estão com “--" (traço traço) são comentários que faço dentro da STORED PROCEDURE.

Testando Código Criado

Para testar o código criado anteriormente, primeiramente tenho que abrir outra IDE e executá-lo. Segue o passo a passo para abri-la.

Estou no ENTERPRISE MANAGER, cliquei no menu TOOLS e logo depois em SQL QUERY ANALYZER. O mesmo abrirá uma outra janela como mostra na ilustração abaixo:

Essa janela aberta é chamada de SQL QUERY ANALYZER e serve para executar alguns códigos SQL tais como: pesquisa, inclusão, alteração, exclusão, criação de tabelas, criação de SP e outros.

Para que eu possa testar a SP criada anteriormente, basta copiar o nome da mesma e colar dentro do programa da seguinte forma:

exec DINAMICASP001_CONSUTAUSUARIO null,null,null

Uma palavra pequena foi colocada antes mesmo do nome da SP criada, esse comando é para executá-la passando os parâmetros necessários. Note que passei tudo null. Clicando F5 ou no botão EXECUTE QUERY o comando será executado automaticamente mostrando os valores retornados na parte de baixo da janela.

Segue a figura de ilustração:

Passe todos os parâmetros como null porque não quis colocar nenhuma condição para filtrar. Agora que já mostrei como executar uma SP para testar, colocarei uma condição.

Apenas colocarei o e-mail do usuário que gostaria, o restante será tudo null.

exec DINAMICASP001_CONSUTAUSUARIO null,null,"mauricio@aspneti.com"

Note a diferença entre as duas executas, inclusive a SP trouxe valores diferentes neste momento do que no primeiro momento da execução, isto é, agora teve uma condição montada que executou trazendo apenas o e-mail que foi solicitado. Da mesma forma, posso passar os outros parâmetros como data ou nome, se o nome ou data de nascimento estiveram corretos, a SP irá trazer normalmente, se for ao contrário o sistema não irá trazer nada. Com isso acabei economizando pelo menos quatro SPs para serem criadas pelo desenvolvedor ou DBA.

É bem simples o desenvolvimento das SPs.

Executando o Comando Print

Essa parte do artigo mostra, apenas o que foi montado pela SP, veja na tela. Para que isso seja feito corretamente, abri a SP, comentei a linha de EXECUTE e descomentei a linha PRINT.

Note como ficou na ilustração:

Na ultima linha veja que foi descomentada. A mesma voltará apenas o select montado.

--#### EXECUTANDO SQL MONTADO ANTERIORMENTE --EXECUTE(@SQL) -- #### COM O PRINT O MESMO MOSTRA O SQL QUE FOI MONTADO. SOH DESCOMENTAR. PRINT(@SQL)

Depois cliquei em APLICAR e depois OK. Executei a mesma coisa anteriormente.

Segue o resultado mostrado pela mesma execução:

Como foi dito antes, veio apenas o SELECT que foi montado.

Bom, eu fico por aqui. Tente executar alguns parâmetros para que entenda melhor, executando a cada dia isso fica mais claro. Se no caso tiver alguma dúvida, pode entrar em contato.

Mauricio Junior.

Site: www.mauriciojunior.org

Comunidade: www.aspneti.com