terça-feira, 3 de abril de 2012

Query Dinâmica + Paginação - SQL Server

Agora, nenhuma novidade, só juntei os posts anteriores para criar uma Query Dinâmica com Paginação, aplicando os conceitos de melhoria de performance.

As dicas de performance já foram explicadas nos 2 posts anteriores:
http://solucaocompilada.blogspot.com.br/2012/04/query-dinamica-sqlserver.html
http://solucaocompilada.blogspot.com.br/2012/04/paginacao-sql-server-rownumber-e.html

Segue exemplo juntando tudo:
create procedure sp_modelo_pesquisa_paginacao (
@cpf varchar(14) = null,
@codigo int = null,
@nome varchar(55) = null,
@email varchar(45) = null,
@pagina int = 0,
@pagina_tam int = 0)
as
begin

declare @sql nvarchar(4000)
declare @count nvarchar(4000)
declare @campos nvarchar(4000)
declare @tabelas nvarchar(4000)
declare @filtros nvarchar(4000)
declare @error int
declare @rowcount int

/* Seleciona os campos
O primeiro campo é utilizado na paginação.
O segundo campo mostra a quantidade de páginas caso seja usada a paginação. */
select @campos = N'select ROW_NUMBER() OVER (ORDER BY codigo ASC) AS Row, '
+ N'case when @ppagina_tam = 0 then 0 else (@ptotalregistros) / @ppagina_tam + case when (@ptotalregistros) % @ppagina_tam > 0 then 1 else 0 end end as total_pagina , '
+ N'codigo, nome, cpf '

/* Seleciona as tabelas. */
select @tabelas = N'from cliente '

/* Monta os critérios para a seleção. */
select @filtros = N'where 1=1 '
+ case when @cpf is not null then N'and cpf = @pcpf ' else N'' end
+ case when @codigo is not null then N'and codigo = @pcodigo ' else N'' end
+ case when @nome is not null then N'and nome like ''%'' + @pnome + ''%''' else N'' end
+ case when @email is not null then N'and email = @pemail ' else N'' end

/* Calcula a quantidade de registros */
select @count = N'declare @ptotalregistros int '
+ N'select @ptotalregistros = count(*) '
+ @tabelas
+ @filtros

/* Monta Query */
select @sql = @campos + @tabelas + @filtros

/* Paginação. */
select @sql = @count
+ N'select ' + case when @pagina_tam = 0 then N'' else N'top (@ppagina_tam)' end
+ N'* from (' + @sql + N') as lista where Row > @ppagina_tam * ( @ppagina - 1 )'

/* Faz a chamada da query utilizando a sp_executesql. */
exec sp_executesql @sql,
N'@ppagina as int,
@ppagina_tam as int,
@pcpf varchar(14),
@pcodigo int,
@pnome varchar(55),
@pemail varchar(45)'
,
@ppagina = @pagina,
@ppagina_tam = @pagina_tam,
@pcpf = @cpf,
@pcodigo = @codigo,
@pnome = @nome,
@pemail = @email

/* Armazena as variáveis globais de erro e linhas afetadas. */
select @error = @@ERROR, @rowcount = @@ROWCOUNT

/* Verifica se houve erro na execução da query dinâmica. */
if @error <> 0
begin
    select coderro = 1, msgerro = 'Erro ao executar procedure'
    return ( 1 )
end

/* Verifica se a consulta retornou algum registro. */
if @rowcount = 0
begin
    select coderro = 2, msgerro = 'Consulta no retornou nenhum registro com os critérios informados'
    return 2
end

end

Nenhum comentário:

Postar um comentário