[RESOLVIDO] Como percorrer as colunas de um unico registro retornado por uma query dinâmica?

Question

Boa tarde pessoal,  gostaria de saber como percorrer as colunas de um unico registro retornado por uma query dinâmica, ou seja a query retorna apenas um registro, porem o numero de colunas do registro retornado pode variar.

Preciso percorrer estas colunas e para cada uma realizar um insert em uma tabela que armazena o nome e o valor da coluna.

Obs: Não preciso percorrer linhas (Cursor), preciso percorrer colunas.

Alguem tem conhecimento de como fazer isso?

[]'s

Answer 1

Vinicius,

O resultado dessa consulta é um simples result set, ou você pode jogá-lo em uma tabela temporária? Caso possa fazer isso ficaria mais fácil de resolver seu problema. Não conheço uma forma "bonita" de fazer isso que você quer, só conheço soluções orientadas a gambiarra.

Como por exemplo:

Crio uma tabela para armazenar os valores e monto uma query para o exemplo (como você disse que o número de colunas retornadas é dinâmica, imagino que tenha sido montada por string).

CREATE TABLE LogValores 
	(
		Coluna VARCHAR(100),
		Valor  VARCHAR(100)
	)
DECLARE @TbValores VARCHAR(100)
DECLARE @TbColunas VARCHAR(100)

DECLARE @Sql       NVARCHAR(MAX)
--Tabelas temporárias que irão armazenar os valores e as colunas
SELECT @TbValores = '##Valores_' + REPLACE(REPLACE(REPLACE(NEWID(), '-', ''), '}', ''), '{', '')
SELECT @TbColunas = '##Colunas_' + REPLACE(REPLACE(REPLACE(NEWID(), '-', ''), '}', ''), '{', '')

SET @Sql = '
	SELECT IdAula,
		   Data
	  INTO ' + @TbValores + '
	  FROM Frequencia 
	 WHERE IdAula = 1  '

EXECUTE sp_ExecuteSql @Sql

Com a temporária contendo os valores da query, faço uma gambiarra para tentar resolver o problema.

SET @Sql = N' 
	SELECT cl.name,
		   ROW_NUMBER()OVER(ORDER BY cl.Id) AS Linha
	  INTO ' + @TbColunas + '
	  FROM tempdb..syscolumns cl
	 INNER JOIN tempdb..sysobjects ob
		ON ob.id = cl.id
	 WHERE ob.id = OBJECT_ID(''tempdb..' + @TbValores + ''')'
	 
EXECUTE sp_ExecuteSql @Sql
--Insiro os registros dentro da tabela
SET @Sql = '
	DECLARE @i INT
	DECLARE @NomeColuna VARCHAR(100)
	DECLARE @SqlAux     NVARCHAR(500)
	
	SELECT @i = COUNT(*)
	  FROM ' + @TbColunas + '
	
	WHILE @i > 0
		BEGIN
			SELECT @NomeColuna = name
			  FROM ' + @TbColunas + '
			 WHERE Linha = @i

			SET @SqlAux = ''
			INSERT LogValores
			SELECT '''''' + @NomeColuna +'''''',
				   CONVERT(VARCHAR, ''+ @NomeColuna +'')
			  FROM tempdb..' + @TbValores + '''
			
			EXECUTE sp_ExecuteSql @SqlAux
						
			SET @i = @i - 1
			
		END '

EXECUTE sp_ExecuteSql @Sql
	
SELECT * FROM LogValores	

Essa é uma maneira "feia" de resolver seu problema. Se souber algum outro jeito de fazer isso compartilhe aqui, porque fiquei curioso.

---

[]'s
Philipe Souza
E-mail: [e-mail]

Answer 2

Olá Philipe,

Sua solução funciona.

Pesquisei bastante e ainda não encontrei nada a respeito de uma maneira elegante de se fazer isso, o que é uma pena.

Imaginei algo como percorrer as colunas através de indices, porem estou começando a acreditar que não tem como, rs.

Sua resposta foi muito útil, mais fica a pergunta, será que realmente não existe uma maneira melhor de fazer isso?

[]'s

Answer 3

Vinícius,

Veja se esta loucura de código que eu criei pode ajudar:

Create Table T1
 (Codigo Int Identity(1,1),
  Valores Varchar(10) Default 'Valores '+Convert(Varchar(3),@@Identity+1))
Insert Into T1 default values
Go 10
Declare @EstruturaTable Table
 (ColumnID TinyInt,
  ColumnName Varchar(50),
  ColumnValue SQL_Variant Null)
Insert Into @EstruturaTable (ColumnID, ColumnName)
 Select Ordinal_Position, Column_Name from information_schema.columns
 where table_name='T1'
Select * from T1
Select * from @EstruturaTable
Update @EstruturaTable
Set ET.ColumnValue = T.Valores  
From @EstruturaTable ET Inner Join T1 T
                         On ET.ColumnID = T.Codigo
Select * from @EstruturaTable

---

Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

Answer 4

Oi Vinicius,

Atualmente, os codigos passados pelos colegas Philipe e Junior são a solucao. Se vc puder esperar mais um pouco, no Denali, existe uma view nova que te ajudaria (veja post em http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/20/sql-server-v-next-denali-metadata-discovery.aspx):

SELECT 
name, 
system_type_name, 
is_nullable
FROM 
sys.dm_exec_describe_first_result_set
(
N'SELECT * FROM dbo.x;', NULL, 0
) AS f
ORDER BY
column_ordinal;

Te retornaria um resultSet com o nome da coluna, o tipo e se é nullable. Bastaria processar o result set para vc fazer as insercoes que vc quer.

Abracos, Daniel.

Answer 5

Muito interessante dimamura.

Answer 6

Boa noite Junior,

Este código funcionou perfeitamente, e como não existe nenhuma forma "nativa" de se fazer isso, diria que esta é a melhor solução.

Muito bom.

Obrigado e bom final de semana.

[]'s