Ozimar Henrique
Consultas dinâmicas
Formas diferentes de consultas dinâmicas
Neste post mostrarei algumas formas diferentes de fazer consultas dinĂąmicas.
Em muitos relatĂłrios temos diversos filtros possĂveis e temos que criar procedures flexĂveis para este cenĂĄrio.
Vale ressaltar que cada forma tem que ser analisada pro o caso em questĂŁo.
Criação do ambiente:
create table #Cliente
(
ID int identity(1,1) primary key,
Nome varchar(50),
Idade int,
Vendas int
)
GO
SET NOCOUNT ON
INSERT INTO #Cliente
VALUES(NEWID(), convert(int,RAND()*100), convert(int,RAND()*100))
GO 100000
SET NOCOUNT OFF
CREATE NONCLUSTERED INDEX IX_CLIENTE_Idade ON #Cliente([Idade]) INCLUDE([Nome])
GO
OPĂĂO 1) EXECUTE - Esta Ă© a mais utilizada
CREATE PROCEDURE usp_ConsultaCliente
@pn_Nome varchar(50)=NULL, @pn_Idade int=NULL, @pn_Vendas int=NULL
AS
BEGIN
DECLARE @SQL varchar(1000)
SET @SQL = 'Select Nome from #Cliente where 1=1'
IF @pn_Idade is not null
SET @SQL = @SQL + ' and Idade=' + convert(varchar(5),@pn_Idade)
IF @pn_Vendas is not null
SET @SQL = @SQL + ' and Vendas=' + convert(varchar(5),@pn_Vendas)
IF @pn_Nome is not null
SET @SQL = @SQL + ' and Nome like ''' + convert(varchar(50),@pn_Nome) + '%'''
EXECUTE(@SQL)
END
GO
OPĂĂO 2)SP_EXECUTESQL
Definição da Microsoft: "Para executar uma cadeia de caracteres, recomendamos que vocĂȘ use o procedimento armazenado sp_executesql em vez da instrução EXECUTE."
O sp_executesql permite definirmos o que Ă© parĂąmetro dentro da string da consulta e passar estes parĂąmetros com tipos bem definidos e separadamente, o que facilita ao SQL Server de criar um plano efetivo.
Com isto a probabilidade do SQL Server criar um plano de execução mais efetivo e reutiliza-lo depois é maior.
CREATE PROCEDURE usp_ConsultaCliente2
@pn_Nome varchar(50)=NULL, @pn_Idade int=NULL, @pn_Vendas int=NULL
AS
BEGIN
DECLARE @SQL nvarchar(1000)
DECLARE @Param nvarchar(1000)
SET @SQL = 'Select Nome from #Cliente where 1=1'
IF @pn_Idade is not null
SET @SQL = @SQL + ' and Idade=' + convert(varchar(5),@pn_Idade)
IF @pn_Vendas is not null
SET @SQL = @SQL + ' and Vendas=' + convert(varchar(5),@pn_Vendas)
IF @pn_Nome is not null
SET @SQL = @SQL + ' and Nome like ''' + convert(varchar(50),@pn_Nome) + '%'''
Set @Param='@pn_Nome varchar(50), @pn_Idade int, @pn_Vendas int'
EXECUTE sp_executesql @SQL, @Param, @pn_Nome, @pn_Idade, @pn_Vendas
END
GO
OPĂĂO 3) Utilizando OR e NULL
CREATE PROCEDURE usp_ConsultaCliente3
@pn_Nome varchar(50)=NULL, @pn_Idade int=NULL, @pn_Vendas int=NULL
AS
BEGIN
Select Nome from #Cliente
where (Idade=@pn_Idade or @pn_Idade is null)
AND (Vendas=@pn_Vendas or @pn_Vendas is null)
AND (Nome like @pn_Nome + '%' or @pn_Nome is null)
END
GO
Vamos executar as 3 procedures:
exec usp_ConsultaCliente @pn_Idade=55
exec usp_ConsultaCliente2 @pn_Idade=55
exec usp_ConsultaCliente3 @pn_Idade=55
Analisando as estatĂsticas de IO temos o seguinte:
(932 row(s) affected)
Table 'Worktable'. Scan count 1, logical reads 10
(932 row(s) affected)
Table 'Worktable'. Scan count 1, logical reads 10
(932 row(s) affected)
Table 'Worktable'. Scan count 1, logical reads 761
Veja que a opção 3 é a que utilizou mais leitura de påginas.
Vamos ver o plano de execução:
Veja que a opção 3 é a que gera mais custos pro servidor.
Coloque a opção OPTION(RECOMPILE) no final da consulta da opção 3 e veja a diferença que ela gera.
O objetivo deste Post é apenas mostrar as 3 opçÔes, num post futuro irei explicar mais internamente cada um destes comandos.
Subscribe ecode10.com
Receive our latest updates about programming languages, software, database, books, ebooks, classes, jobs and more.
You can cancel anytime.
Log In
Related articles
Creating a SiteMap using SQL Database
Como indexar melhor meu site e links?
Transformando linhas em colunas usando SQL Server
veja como funciona
Comando LEFT no database
veja como