Concatenar conteúdo de um registro SQL Server 2005
13/05/2015
0
Estou com uma necessidade de retornar o conteúdo de um registro de uma determinada tabela. Exemplo:
Tenho uma tabela de PRODUTOS com os seguintes campos:
CODIGO NOME MARCA VALOR
------------ -------------------------------- -------------- ---------
2514 MOUSE 3 BOTOES USB LOGITECH 45,00
Gostaria de saber se existe algum comando SQL ou Função ou StoreProcedure para que ao passar o nome da tabela como parâmetro a mesma me retorne o conteúdo do registro devidamente concatenado. Seguindo o exemplo do registro acima ficaria assim:
2514MOUSE 3 BOTÕES USBLOGITECH45,00
Atualmente estou utilizando uma função em Delphi7 para essa finalidade mas devido a alguns clientes terem muitos registros esta um pouco lento.
if not QAux.FieldByName(CampoChave).IsNull then
begin
//Loop nos Fields
for i := 0 to QAux.FieldCount -1 do
begin
if QAux.FieldDefs[i].Name <> NomeCampo then //elimina o campo _HASH para que o mesmo não faça parte do calculo MD5
StringAux := StringAux + QAux.Fields.Fields[i].AsString;
end;
end;
Minha necessidade desta rotina é para atender um requisito do PAF-ECF para detectar se houve algum tipo de manipulação no banco de dados que não passou pelo meu sistema.
Carlos Eduardo
Post mais votado
13/05/2015
Vou testar a consulta sql e assim que possível postarei o resultado.
Grato pela contribuição.
Carlos Eduardo
Mais Posts
13/05/2015
Marcos P
select CODIGO+NOME+MARCA+VALOR
Se existirem campos numéricos, converta-os para char ( via convert, rtrim, str,... como no exemplo abaixo ) e depois execute a concatenação :
declare @Numerico1 Int declare @Numerico2 Float set @Numerico1 = 83 set @Numerico2 = 15.76 select @Numerico1, @Numerico2, rtrim(convert(varchar(20),@Numerico1)), rtrim(str(@Numerico2,10,2))
13/05/2015
Carlos Eduardo
select CODIGO+NOME+MARCA+VALOR
Se existirem campos numéricos, converta-os para char ( via convert, rtrim, str,... como no exemplo abaixo ) e depois execute a concatenação :
declare @Numerico1 Int declare @Numerico2 Float set @Numerico1 = 83 set @Numerico2 = 15.76 select @Numerico1, @Numerico2, rtrim(convert(varchar(20),@Numerico1)), rtrim(str(@Numerico2,10,2))
Marcos,
Como citado no post preciso de uma função ou algo parecido onde passo o nome da tabela como parâmetro sem a necessidade de informar os campos pois são varias tabelas.
Já consegui pegar o nome dos campos. rs
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'PRODUTOS'
Grato pela atenção!
13/05/2015
Ceilton M
"detectar se houve algum tipo de manipulação no banco de dados que não passou pelo meu sistema. "
Você quer saber se a estrutura da tabela foi alterada?
Se for isso você tem que estudar triggers DDL....
13/05/2015
Ceilton M
Esse trigger impede que tabelas sejam alteradas ou apagadas de um banco.
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
ROLLBACK ;
13/05/2015
Marcos P
Se sua ideia é fazer isso dinamicamente, crie uma stored procedure no banco que, passando o nome da tabela, leia sua estrutura e gere a concatenação conforme os campos disponíveis.
Você fará isso através de uma query dinâmica, tratando das devidas conversões para char / varchar.
Vai dar um "trabalhinho", mas eu testaria isso antes de maneira fixa, para verificar se a performance do lado da aplicação melhora...
13/05/2015
Carlos Eduardo
"detectar se houve algum tipo de manipulação no banco de dados que não passou pelo meu sistema. "
Você quer saber se a estrutura da tabela foi alterada?
Se for isso você tem que estudar triggers DDL....
Minha necessidade não consiste em saber se a estrutura do banco foi alterada e sim um registro. Exemplo:
Tenho um sistema de Frente de Loja onde é feito uma emissão de cupom fiscal. Ao gravar o registro pertinente a venda eu também calculo o HASH do registro e armazeno num campo da tabela para que depois eu possa verificar se o conteúdo do registro esta igual ao que foi gravado.
Desta forma se alguém acessar uma ferramente de manutenção e modificar os dados deste registro eu tenho como saber se o mesmo foi manipulado.
Esta é uma exigência para quem vai homologar o sistema na legislação PAF-ECF.
Existe um arquivo eletrônico que é gerado todos os dias após a Redução Z e caso algum registro de produto, cliente, venda, estoque, etc... for manipulado eu tenho que gerar interrogações no lugar do campo. "????????"
13/05/2015
Carlos Eduardo
Esse trigger impede que tabelas sejam alteradas ou apagadas de um banco.
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
ROLLBACK ;
Vou guardar essa trigger achei bem interessante.
Obrigado!
13/05/2015
Carlos Eduardo
Se sua ideia é fazer isso dinamicamente, crie uma stored procedure no banco que, passando o nome da tabela, leia sua estrutura e gere a concatenação conforme os campos disponíveis.
Você fará isso através de uma query dinâmica, tratando das devidas conversões para char / varchar.
Vai dar um "trabalhinho", mas eu testaria isso antes de maneira fixa, para verificar se a performance do lado da aplicação melhora...
Marcos, vou tentar criar essa StoreProcedure e caso eu consiga postarei aqui o resultado e principalmente a performance. Pois hoje já faço isso via Delphi a qual está muito lento pois um de meus clientes tem mais de 50 mil produtos. Atualmente minha estrutura de Produtos tem 60 campos. Fazendo uma conta rápida temos:
60 X 50.000 = 3.000.000 (essa é a quantidade de vezes que meu programa executa).
O cliente me disse que todo final de dia após a Redução Z todo o processo de geração do arquivo eletrônico demora aproximadamente 15 minutos.
13/05/2015
Marcos P
Tente desenvolver a procedure, caso você tenha dificuldades... retorne a esse post que posso lhe ajudar via Skype.
13/05/2015
Carlos Eduardo
Tente desenvolver a procedure, caso você tenha dificuldades... retorne a esse post que posso lhe ajudar via Skype.
SQL Server 2005 Express
Beleza Marcos assim que eu conseguir fazer algo lhe aviso sim.
Grato pela atenção!
13/05/2015
Ceilton M
Só deu erro em colunas do tipo timestamp.
declare @nometabela varchar(200) = 'TABELA';
declare @objectid int --= 124956663;
declare @name varchar(200), @system_type_id int;
declare @SQL varchar(2000) = ' select '
DECLARE @UID INT, @schemaname varchar(20)
select @objectid = id from sysobjects where type = 'U' and name = @nometabela
declare cursorcolumns cursor for SELECT name,system_type_id FROM sys.columns where object_id = @objectid
open cursorcolumns;
fetch cursorcolumns into @name, @system_type_id;
while @@FETCH_STATUS = 0
begin
if len (@SQL) > 8 set @SQL = @SQL + ' + ';
set @SQL = @SQL + ' convert(varchar(200), isnull(' + @name + ','''') ) ';
/*
if @system_type_id not in (35, 167, 175,231,239) -- Se nao for do tipo character
begin
set @SQL = @SQL + ' convert(varchar(50), ' + @name + ' ) '
end;
*/
fetch cursorcolumns into @name, @system_type_id
end;
close cursorcolumns;
deallocate cursorcolumns;
select @UID = UID from sysobjects where name IN ( 'GN_USUARIO' )
SELECT @schemaname = name from sys.SCHEMAS WHERE SCHEMA_ID = @UID
set @SQL = @SQL + ' from ' + @schemaname + '.' + @nometabela
select @SQL;
exec @SQL;
13/05/2015
Ceilton M
14/05/2015
Carlos Eduardo
Caro Ceilton,
Fiz testes no exemplo que você disponibilizou mas não obtive sucesso pois ao executar a consulta informando minha tabela (@nometabela = 'PRODUTOS') a consulta esta retornando vazia.
Hoje consegui fazer uma store procedure e estou quase lá, só está faltando o procedimento me retornar o conteúdo do select que estou compondo no loop. Não consegui fazer com que este procedimento me retornasse o resultado do select mas sim a instrução sql. Exemplo: "select campo1, campo2 from PRODUTOS"
Estou postando meu procedimento para que vocês possam me ajudar:
@nome_tabela varchar(50),
@campo_chave varchar(50),
@codigo varchar(10),
@resultado varchar(8000) OUTPUT)
AS
set nocount on
declare cursorcolumns cursor for
select column_name, data_type
from information_schema.columns
where table_name = @nome_tabela
and (column_name not like '%HASH')
declare @column_name varchar(100), @data_type varchar(100), @SQL varchar(5000)
set @SQL = 'select '
open cursorcolumns
fetch next from cursorcolumns into @column_name, @data_type
while (@@FETCH_STATUS = 0)
begin
if @data_type in ('char', 'nchar', 'nvarchar', 'varchar')
set @SQL = @SQL + 'coalesce(' + @column_name + ','''') + '
else
set @SQL = @SQL + 'coalesce(convert(varchar(8000), ' + @column_name + '),'''') + '
fetch next from cursorcolumns into @column_name, @data_type
end
close cursorcolumns
deallocate cursorcolumns
if right(@SQL,2) = '+ ' --remove o caracter "+" resultado da concatenação no loop dos campos
set @SQL = left(@SQL,(len(@SQL)-1))
----------------- É neste ponto que não consigo retornar o resultado deste select -----------------
select @Resultado = (@SQL + ' from ' + @nome_tabela + ' where ' + @campo_chave + ' = ' + @codigo)
set nocount off
Estou realizando os testes no Delphi:
Var
sp_Retorna_Conteudo_Registro: TSQLStoredProc;
begin
//Store Procedure
try
try
sp_Retorna_Conteudo_Registro := TSQLStoredProc.Create(nil);
sp_Retorna_Conteudo_Registro.SQLConnection := AutoConnection;
sp_Retorna_Conteudo_Registro.SchemaName := 'dbo';
sp_Retorna_Conteudo_Registro.StoredProcName := 'sp_Retorna_Conteudo_Registro';
sp_Retorna_Conteudo_Registro.Params.FindParam('@nome_tabela').AsString := lbledtTabela.Text;
sp_Retorna_Conteudo_Registro.Params.FindParam('@campo_chave').AsString := lbledtChave.Text;
sp_Retorna_Conteudo_Registro.Params.FindParam('@codigo').AsString := lbledtCodigo.Text;
sp_Retorna_Conteudo_Registro.ExecProc;
lbledtResultSP.Text := sp_Retorna_Conteudo_Registro.Params.FindParam('@resultado').AsString;
finally
FreeAndNil(sp_Retorna_Conteudo_Registro);
end;
except on E: Exception do
Application.MessageBox(PChar(E.Message), 'Erro', MB_ICONERROR);
end
end;
14/05/2015
Marcos P
select @Resultado = (@SQL + ' from ' + @nome_tabela + ' where ' + @campo_chave + ' = ' + @codigo)
Não rola !
Faça...
set @SQL = @SQL + ' from ' + @nome_tabela + ' where ' + @campo_chave + ' = ' + @codigo exec(@SQL)
Se quiser ver a query antes de rodar, troque o "exec" por "print".
Uma pergunta... um item cuja descrição é "SUPORTE PARA MONITOR", deve retornar "SUPORTE PARA MONITOR" ou "SUPORTEPARAMONITOR" ?
14/05/2015
Carlos Eduardo
O procedimento que você pediu para alterar funcionou somente quando executo o procedimento no "SQL Management Studio":
exec sp_Retorna_Conteudo_Registro 'PRODUTOS', 'PRD_CODIGO', 1, ''
Resultado:
Mas no Delphi executa sem da erro mas nao traz nada no retorno. Alguma sugestão? No post anterior eu inseri como estou utilizando a Store Procedure no Delphi.
Respondendo sua pergunta a descrição deve retornar com os devidos espaços: "SUPORTE PARA MONITOR"
Esse ta dando trabalho rs.
Grato pela atenção!
Clique aqui para fazer login e interagir na Comunidade :)
