funcao Join

[Erick de Camargo 0]

Olá pessoal,

 

Eu costumo ser mais ativo no forum de ASP, mas estou precisando da ajuda de voces.....

 

Seguinte....

 

Eu tenho uma tabela relacional (usuarios e listas), sendo que uma lista pode conter varios usuarios e um usuarios pode conter varias listas. Basicamente, permissao de acesso a uma determinada lista.

 

E uma consulta na minha pagina retorna exatamente isto, tendo por base a lista....

 

Entao eu tenho

 

--tb_listas

lista_id

lista_nome

 

--sis_users

user_id

user_nome

 

--tr_users_listas

tr_id

tr_user_id

tr_lista_id

 

Pegou a ideia né?

 

Ok.... Na página eu quero trazer:

Lista | usuarios

lista_nome1 | user_nome1, user_nome2, ...

lista_nome2 | user_nome3, user_nome2, ...

 

 

Bom, utilidade explicada, vamos a solucao....

 

Na consulta sql eu estou tentando consultar da TR inner join LISTAS, on lista_id = tr_lista_id

(tah abreviado, mas o negócio é passar a idéia).....

Pra isso.....

 

 

SELECT l.lista_nome, dbo.fnJoin(USUARIOS_DA_LISTA) 
FROM tr_user_lista tr inner join tb_listas l 
	on tr.tr_lista_id = l.lista_id

 

Ok, para isso, eu quero Join, Concatenar, Juntar, Agrupar, ou como preferirem, em uma STRING (varchar) ........

 

Entao estou tentando criar uma funcao fnJoin generica, para que me sirva a outras utilidades!!!!!

Vai então....

 

CREATE FUNCTION dbo.fnJoin ( 
@COLUNA VARCHAR(MAX),
@TABELA VARCHAR(MAX),
@FILTRO VARCHAR(MAX),
@DELIMITER VARCHAR(20) = ', '
)
RETURNS VARCHAR(MAX)
AS
BEGIN

DECLARE @CONCATED varchar(MAX);
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = '';
SET @SQL = 'SET @R = '''';
			SELECT @R = @R + ' + @COLUNA + ' + ''' + @DELIMITER + '''
			FROM ' + @TABELA + '';
IF (@FILTRO <> '') SET @SQL = @SQL + ' 
			WHERE ' + @FILTRO;


EXECUTE sp_executesql @SQL, N'@R VARCHAR(MAX) OUTPUT', @R=@CONCATED OUTPUT;

RETURN @CONCATED;
END

 

Parece simples né ??

 

Exceto .....................

 

Agora ao problema......

 

A p0rr@ do SQL me retorna....

 

Mensagem 557, Nível 16, Estado 2, Linha 1
Somente as funções e alguns procedimentos armazenados estendidos podem ser executados em uma função.

Obvio que esse nivel, estado e linha sao da execucao DENTRO do sp_executesql....

 

 

E AI, alguem pode me salvar ???

 

Ja pensei em transformar em procedure, mas ai nao vou poder usar direto em campos do select,

Alem do mais, funcoes nao inserem, nao criam tabelas temporarias, nao criam tabelas normais, enfim, tah f0d@...........

 

HELP MEEE !

[advaldomesquita 93]

ERICK,

 

antes de executar, pode postar o resultado destes valores:

 

--     EXECUTE sp_executesql @SQL, N'@R VARCHAR(MAX) OUTPUT', @R=@CONCATED OUTPUT;
SELECT @CONCATED, @SQL, @COLUNA, @TABELA, @FILTRO, @R

aBÇS

[Erick de Camargo 0]

Então cara, seguinte....

 

Como é uma funcão, ele não deixa eu fazer um SELECT, se eu executar fora da função, como uma query comum, o código funciona....

 

Aí para efeito de demonstração, executando como uma query comum....

 

DECLARE 
@COLUNA VARCHAR(MAX),
@TABELA VARCHAR(MAX),
@FILTRO VARCHAR(MAX),
@DELIMITER VARCHAR(20) = ', ';


SELECT @COLUNA = 'user_nome', @TABELA = 'sis_users', @FILTRO = '', @DELIMITER = ', ';

DECLARE @CONCATED varchar(MAX);
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = '';
SET @SQL = 'SET @R = '''';
			SELECT @R = @R + ' + @COLUNA + ' + ''' + @DELIMITER + '''
			FROM ' + @TABELA + '';
IF (@FILTRO <> '') SET @SQL = @SQL + ' 
			WHERE ' + @FILTRO;
SET @SQL = @SQL + ';';
PRINT @SQL;
EXECUTE sp_executesql @SQL, N'@R VARCHAR(MAX) OUTPUT', @R=@CONCATED OUTPUT;

SET @CONCATED = LEFT(@CONCATED, LEN(@CONCATED) - LEN(@DELIMITER));

SELECT 
	@CONCATED as '@concated'
	, @SQL as '@sql'
	, @COLUNA as '@coluna'
	, @TABELA as '@tabela'
	, @FILTRO as '@filtro'
;

 

RESULTADO:

 

===================

@concated

Administrador, Erick de Camargo,

===================

@sql

SET @R = ''; SELECT @R = @R + user_nome + ', ' FROM sis_users;

===================

@coluna

user_nome

===================

@tabela

sis_users

===================

@filtro

 

===================

 

O PRINT RETORNOU ISSO:

SET @R = '';
			SELECT @R = @R + user_nome + ', '
			FROM sis_users;

 

Bom, quando eu só troco o declare pela syntax de criação da função:

DECLARE 
@COLUNA VARCHAR(MAX),
@TABELA VARCHAR(MAX),
@FILTRO VARCHAR(MAX),
@DELIMITER VARCHAR(20) = ', ';

 

POR

 

CREATE FUNCTION dbo.fnJoin ( 
@COLUNA VARCHAR(MAX),
@TABELA VARCHAR(MAX),
@FILTRO VARCHAR(MAX) = '',
@DELIMITER VARCHAR(20) = ', '
)
RETURNS VARCHAR(MAX)
AS
BEGIN

 

O sql reclama do PRINT e do SELECT... NORMAL !!!!

Então eu comento esses caras, e faço o RETURN voltar a concatenação dos campos em um único varchar....

 

então... em vez de

SELECT 
	@CONCATED as '@concated'
	, @SQL as '@sql'
	, @COLUNA as '@coluna'
	, @TABELA as '@tabela'
	, @FILTRO as '@filtro'
;

 

eu uso...

 

RETURN @CONCATED + '||' + @SQL + '||' + @COLUNA + '||' + @TABELA + '||' + @FILTRO;

 

Ele cira a função:

--Comando(s) concluído(s) com êxito.

 

 

Mas, nem tudo são flores.....

 

Eu executo a seguinte linha....

Select dbo.fnJoin('user_nome','sis_user','',',')

[/sq]

 

Retorno: ERRO !!!!

 

Mensagem 557, Nível 16, Estado 2, Linha 1

Somente as funções e alguns procedimentos armazenados estendidos podem ser executados em uma função.

 

E aí gente, alguma idéia de como resolver ???

[Fernando Silveira 1]

Erick

 

Só para ver se eu entendi. Você possui uma relação de usuários com listas e deseja exibir por linha as listas e por colunas os usuários relacionados às listas?

 

Se for isso, poderá utilizar a função PIVOT a partir do SQL Server 2005. Essa função faz exatamente isso, transforma linhas em colunas.

 

Link sobre a função: Usando Pivot

 

Por exemplo:

 

lista1 - joão - maria - josé

lista2 - pedro - paulo - josé

 

Quanto ao uso de função é certo que há muitas limitações em relação ao uso de procedures mesmo.

 

[]'s

 

Fernando Silveira

[advaldomesquita 93]

Up !! Ajuda ae genteee !

 

Erick, cuidado com os up´s

10ª Regra

É proibido o post de mensagens com o objetivo de evidenciar o tópico, tornando-o primeiro da lista de tópicos (up), tal como mensagens repetitivas e/ou que não condizem com o assunto do tópico em questão (flood). Tais mensagens serão excluídas e o usuário advertido por MP.

 

eu estou tentando pensar em algo, mas meu tempo estes dias anda escasso. Se conseguir te falo ;)

 

Fernando, o que ele precisa eh criar uma funcao para passar outros parametros e nao "pivotar" o resultado ^^ (eu entendi isso)...