SQL Dinâmico Compilado

Livros publicado pelo autor:

 

 

 

Olá pessoal meu nome é Maurício Júnior, estou disposto a mostrar uma nova maneira de criar stored procedures dinâmicas já compiladas. Anteriormente falei sobre sp’s dinâmicas que são compiladas em tempo de execução. Agora ensinarei um modo de criar as mesmas compiladas mantendo assim uma produtividade maior do que as que não são compiladas. Espero poder ajudar a todos que gostam de banco de dados, stored procedures e para quem gosta de conhecer.

 

         O artigo anterior falado sobre procedures foi publicado no endereço abaixo, caso queira olhar gratuitamente o mesmo acesse o link abaixo:

 

Trabalhando com Stored Procedure

http://www.aspneti.com/visualizar/downloadArtigo.aspx?ch_artigos=206

 

         Tudo começa abrindo o programa Microsoft Sql Server Management Studio Express que pode ser pego gratuitamente no link http://msdn.microsoft.com/vstudio/express/sql/register/default.aspx. Você pode baixar e instalar gratuitamente o programa.

 

 

 

 

  

Na Prática

 

         Criei o banco de dados e uma tabela de usuário simples e fácil para melhor entendimento. O nome da tabela é TB004_USUARIO.

 

 

 

 

         Os campos estão definidos cada um em seu tempo com seus definidos dados.

 

 

CH_USUARIO	Chave do usuário onde é do tipo INT e auto incremento de 1 em 1.
NO_USUARIO	Nome do usuário com um tamanho de 50 caracteres.
STATUS_USUARIO	Status do usuário, pode ser 0 ou 1, ou seja, ativo ou inativo.
CH_OPERADOR	Chave do operador cujo faz relacionamento com outra tabela que não vem ao caso no momento.
DT_USUARIO	Um campo de data e hora. Do tipo DATETIME.
CH_SALA	Chave de uma outra tabela de SALA, do tipo UNIQUEIDENTIFIER cujo gera id() automaticamente e com vários caracteres como: 6b4263df-2c08-43ae-bbda-61aec1fb74bc

Referência: 1.3

 

        

         Depois de criar os campos, cadastrei alguns dados para preenchimento da tabela. (Referência 1.4)

 

 

 

        

   O passo seguinte foi criar uma stored procedure dinâmica e compilada cujo pode passar o valor do campo CH_USUARIO ou não. Segue o código da SP. (Referência 1.5)

 

 

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go       CREATE PROCEDURE [dbo].[PROC010_SELECT_USUARIO]            (@CH_USUARIO       AS INT = 0)   AS            SELECT                    CH_USUARIO,                    NO_USUARIO,                    STATUS_USUARIO,                    CH_OPERADOR,                    DT_USUARIO,                    CH_SALA          FROM                    TB004_USUARIO          WHERE                    1=1                    AND (@CH_USUARIO = 0 OR CH_USUARIO = @CH_USUARIO)

Referência: 1.5

 

        

         O nome da procedure é PROC010_SELECT_USUARIO, significa que é um comando “SELECT” da tabela de usuário. Note que comecei o nome da procedure como PROC010. É uma dica legal porque uma SP começando por SP_FULANA_DE_TAL pode ter um problema. O banco de dados, ou seja, o sistema do banco de dados fica verificando todas as SPs que começam com as letras “S” e “P”, com isso o sistema pode ficar lento.

 

Explicação:

 

         Logo no começo adicionei uma variável do tipo INT e atribui o valor igual a ZERO. (Referência 1.6)

 

(@CH_USUARIO       AS INT = 0)

Referência: 1.6

 

         Em seguida, digitei o SELECT normalmente para que buscasse no banco de dados os dados inseridos na tabela. (Referência 1.7)

 

SELECT                    CH_USUARIO,                    NO_USUARIO,                    STATUS_USUARIO,                    CH_OPERADOR,                    DT_USUARIO,                    CH_SALA          FROM                    TB004_USUARIO

Referência: 1.7

 

         Note que o “select” não possui a condição “WHERE” ainda. (Referência 1.8)

 

 

WHERE                    1=1                    AND (@CH_USUARIO = 0 OR CH_USUARIO = @CH_USUARIO)

Referência: 1.8

 

         A referência 1.8 mostra que estou igualando o 1=1, a variável @CH_USUARIO a zero ou o campo CH_USUARIO igual a variável @CH_USUARIO. Isso significa que: no começo da procedure a mesma foi igualada a zero, caso o usuário não passe qualquer valor, caso a variável é igual a zero ou o campo igual a variável passada. Se a SP chamada não for passado parâmetro, sempre pegará a primeira condição e não terá problemas.

 

         Esse é o grande segredo para a SP dinâmica. Caso a variável não seja do tipo INT e seja do tipo VARCHAR, basta igualar a NULL, com isso na condição basta perguntar se a mesma é NULL no lugar do 0 junto a referência 1.8.

 

         Para melhor visualizar a stored procedure segue todo o exemplo criado junto a referência 1.9.

 

 

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go       ALTER PROCEDURE [dbo].[PROC010_SELECT_USUARIO]          (@CH_USUARIO       AS INT = 0)   AS            SELECT                    CH_USUARIO,                    NO_USUARIO,                    STATUS_USUARIO,                    CH_OPERADOR,                    DT_USUARIO,                    CH_SALA          FROM                    TB004_USUARIO          WHERE                    1=1                    AND (@CH_USUARIO = 0 OR CH_USUARIO = @CH_USUARIO)

Referência: 1.9

 

         Bom, fico por aqui. Qualquer dúvida favor entrar em contato através do e-mail mauricio@aspneti.com.

 

Mauricio Junior

www.aspneti.com

www.mauriciojunior.org