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.
