Hoje vou demostrar um pequeno exemplo de como utilizar programação dinâmica no SQL. No exemplo aqui demonstrado, utilizo o SQL dinâmico para definir a estrutura de uma tabela temporária de acordo com os dados que desejo exibir.
Eu montei rapidamente um modelo similar a um relatório que fiz recentemente. Eu precisava retornar os fornecedores como colunas, e doze meses como linhas, para montar uma grande planilha de dados. Para poder retornar o fornecedor na coluna, montei um loop nos fornecedores que deverão entrar no relatório, e utilizei o nome fantasia como nome da coluna que receberá o valor do mês/ano específico. Dentro deste mesmo loop, já busco o valor dos títulos deste fornecedor, e atualizo a tabela temporária. Depois só retorno os resultados.
Vamos então ver o código. Tentei comentar da melhor forma possível para explicar cada passo do procedimento.
| create proc spcExemploSQLDinamico as /* Autor: Fabiano Cores Data : 04/12/2010 Site : http://sqlburger.blogspot.com Desenvolvido para demonstrar a utilização de querys dinâmicas no SQL Server. A reprodução deste artigo só é permitida se forem mantidos os créditos do autor. */ declare @sql varchar(8000) declare @vcd_cadastro int declare @vfantasia varchar(20) declare @valor money declare @data smalldatetime -- Crio a temporária que receberá modificações dinâmicas create table #tmp ( mes tinyint null , ano smallint null ) -- Preencho a tabela com as datas. Pego o ano atual, -- e defino como primeiro de janeiro, para pegar o ano -- inteiro. select @data = rtrim(year(getdate())) + '-01-01' set @sql = '' while 1 = 1 begin -- Aqui estou montando uma query dinâmica. Veja que estou -- concatenando várias querys em uma variável VARCHAR, para -- posterior execução. select @sql = @sql + 'insert into #tmp (mes, ano) ' + 'values (' + rtrim(month(@data)) + ',' + rtrim(year(@data)) + ')' + char(13) + char(10) -- Caso tenha inserido os 12 meses do ano, saio do loop if month(@data) = 12 break -- A cada ciclo do loop, pulo um mês select @data = dateadd(month, 1, @data) end -- Aqui executo a query montada acima. exec (@sql) -- limitei em um TOP 25 para não trazer muitos registros -- O distinct é para que retorne apenas uma vez cada -- fornecedor neste select. declare cp1 cursor local fast_forward read_only for select distinct top 25 a.cd_cadastro , a.fantasia from cadastro a , fornecedor b , titulofor c where a.cd_cadastro = b.cd_cadastro and a.situacao = 'A' and a.cd_cadastro = c.cd_cadastro and year(c.dt_lancamento) = year(getdate()) and c.situacao in ('A', 'L') order by a.fantasia /* As tabelas aqui utilizadas e parâmetros devem ser desconsiderados. Adapte este procedimento ao seu banco de dados. */ open cp1 while 1 = 1 begin fetch next from cp1 into @vcd_cadastro , @vfantasia if @@fetch_status <> 0 break -- Adiciono colunas na tabela com o nome do fornecedor select @sql = 'alter table #tmp add [' + rtrim(ltrim(@vfantasia)) + '] money null' exec (@sql) select @data = rtrim(year(getdate())) + '-01-01' while 1 = 1 begin -- Sempre inicialize uma variável dentro de um loop. set @valor = 0; select @valor = sum(vl_titulo) from titulofor where situacao in ('A', 'L') and cd_cadastro = @vcd_cadastro and month(dt_lancamento) = month(@data) and year(dt_lancamento) = year(@data) select @valor = isnull(@valor, 0) -- Para montar o update, uso novamente o nome -- fantasia do fornecedor para saber qual o -- nome da coluna que preciso atualizar o valor. select @sql = 'update #tmp ' + ' set [' + rtrim(ltrim(@vfantasia)) + '] = ' + rtrim(@valor) + ' where mes = ' + rtrim(month(@data)) + ' and ano = ' + rtrim(year(@data)) exec (@sql) if month(@data) = 12 break select @data = dateadd(month, 1, @data) end end close cp1 deallocate cp1 select @sql = 'select * from #tmp order by ano, mes' exec (@sql) |
O resultado é um relatório estruturado dinamicamente:
Nenhum comentário:
Postar um comentário