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.
|
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)
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
