SQL Dinâmico Compilado

 

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.

 

Referência: 1.1

 

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.

 

Referência: 1.2

 

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

 

Referência: 1.3

 

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

 

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