none
Localizar um Item em Várias tabelas RRS feed

  • Pergunta

  • Tenho um banco de dados somente de cadastros de clientes das lojas. Cada filial tem sua propria tabela com os dados dos clientes. Quando abre uma nova filial, abre uma nova tabela para aquela nova filial.

    Preciso fazer uma consulta que retorne o nome da tabela onde aquele determinado Cliente se encontra.

    Fiz esta Function mas esta falhando pois ocorre um erro na cláusula FROM  que nao aceita macrosubstituicao.

    -- TENTATIVA 1 

       

    CREATE FUNCTION ProcuraCliente ( @cpf_informado NVARCHAR(14) )
    RETURNS NVARCHAR(255)
    AS
    BEGIN 
    DECLARE @ClienteLocalizado NVARCHAR (14)
    DECLARE @nometabela NVARCHAR (255)
    DECLARE @retorno NVARCHAR(255)

    DECLARE db_cursor CURSOR FOR
    SELECT Name FROM SYSOBJECTS WHERE XTYPE='U'

    OPEN db_cursor 
    FETCH NEXT FROM db_cursor
    INTO @nometabela

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT @ClienteLocalizado  = 'SELECT cpf FROM ' + @nometabela + 'WHERE cpf =' + @cpf_informado

        IF @ClienteLocalizado <> '' 
    BEGIN
    SET @retorno = @nometabela
    BREAK
    END

       FETCH NEXT FROM db_cursor
       INTO @nometabela

    END
    CLOSE db_cursor
    DEALLOCATE db_cursor
    RETURN @retorno

    END

    --- TENTATIVA 2

    tentei mudar o formato de consulta. (e tambem ocorre um erro).. veja:

            

    CREATE FUNCTION ProcuraCliente(@cpf_informado NVARCHAR(14))
    RETURNS NVARCHAR(255)
    AS
    BEGIN 
        DECLARE @SQL NVARCHAR(255)
    DECLARE @ClienteLocalizado NVARCHAR (14)
    DECLARE @nometabela NVARCHAR (255)
    DECLARE @retorno NVARCHAR(255)
    DECLARE db_cursor CURSOR FOR
    SELECT Name FROM SYSOBJECTS WHERE XTYPE='U'

    OPEN db_cursor 
    FETCH NEXT FROM db_cursor
    INTO @nometabela

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @SQL = N'SELECT @ClienteLocalizado = cpf from ' + @nometabela + N' WHERE cpf = '' + @@cpf_informado + N'''
    EXEC sp_executesql 
    @sql, N'@ClienteLocalizado NVARCHAR(60) OUT', @ClienteLocalizado OUTPUT

        IF @ClienteLocalizado <> '' 
    BEGIN
    SET @retorno = @nometabela
    BREAK
    END

       FETCH NEXT FROM db_cursor
       INTO @nometabela

    END
    CLOSE db_cursor
    DEALLOCATE db_cursor
    RETURN @retorno
    END

    segunda-feira, 19 de maio de 2014 01:09

Respostas

Todas as Respostas

  • Bom dia Lionardo,

    "Geralmente utilizamos Inner Join, Left Join, Right Join, quando desejamos fazer uma junção e buscar dados em mais de uma tabela ou então validarmos dados, mesmo que seja retornando apenas de uma delas."

    Olhe nesses liks abaixo:

    http://forum.outerspace.terra.com.br/index.php?threads/consulta-sql-em-duas-tabelas-simult%C3%A2neas.168461/

    http://www.metropoledigital.ufrn.br/aulas_avancado/web/disciplinas/banco_de_dados/aula_11.html

    http://social.technet.microsoft.com/Forums/pt-BR/6e92bb25-ea85-43dc-ba25-e467bf7769be/select-usando-vrias-tabelas?forum=transactsqlpt

    Abraço e fico aguardando uma resposta sua pra saber se deu certo ok?

    segunda-feira, 19 de maio de 2014 11:23
  • Deleted
    segunda-feira, 19 de maio de 2014 12:10
  • José Diz, este banco de dados fica exclusivo para cadastro de cliente das filiais. O volume é muito grande pois isto achamos melhor nao unificar todos os cadastros numa unica tabela...

    Sendo assim, nao ha perigo de do codigo  SELECT Name FROM SYSOBJECTS WHERE XTYPE='U'  retornar outra coisa que nao seja uma tabela de cadastro de cliente.

    Tudo funciona de forma perfeita.. so da zebra quando eu preciso pesquisar (tabela por tabela) se um determinado cpf ja se encontra cadastrado (pois se um cpf já foi cadastrado em determinada filial, ele nao pode ser cadastrado como cliente de outra filial)... 

    Se o uso de cursor nao é a melhor opcao, tem alguma ideia de como fazer FUNCTION funcionar no sqlserver?

    A versao que estou usando é a SQL Express 2012

    segunda-feira, 19 de maio de 2014 15:34
  • Eu poderia fazer um UNION ALL em todas as tabelas e depois fazer a procura..

    mas achei que seria desperdício de processamento, pois quero fazer a procura tabela por tabela.. e quando localiza o ítem, ja nao precisa continuar fazendo pesquisa em outras tabelas..pode parar no momento que encontra e me informa em qual tabela foi localizado o cpf.

    E tambem nao tenho de antemão o nome de todas as tabelas quando faço a consulta pois tem novas filiais sendo acrescida semanalmente, por isto o uso do codigo  SELECT Name FROM SYSOBJECTS WHERE XTYPE='U'

    segunda-feira, 19 de maio de 2014 15:38
  • Você precisa pesquisar de um determinado CPF esta em algum campo da base?

    Se for isso, a sintaxe abaixo pode lhe ajudar, mas CUIDADO AO RODAR EM AMBIENTE DE PRODUÇÃO!!!!

    Tem CURSOR e vai onerar sua base.

    Não deu tempo de mudar isso para while, mas como nao utilizo o codigo nem mexi nisso.

    !!!CUIDADO!!!

    declare @idtabela int, @tabela varchar(max), @coluna varchar(max), @valorProcurado varchar(max)
    
    --Coloque aqui a palavra ou expressão que deseja procurar
    set @valorProcurado = 'XXX.XXX.XXX-XX' 
    
    if OBJECT_ID('tempdb..#tmpFindString') is not null
    drop table #tmpFindString
    
    create table #tmpFindString (table_name varchar(255), string varchar(255))
    
    DECLARE db_cursor CURSOR FOR  
    		select id from sys.sysobjects where xtype = 'u'
    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @idtabela
    
    WHILE @@FETCH_STATUS = 0   
    BEGIN   
    
    		DECLARE db_cursorColunas CURSOR FOR  
    			select a.name as tabela, b.name as coluna from sys.sysobjects a
    				inner join
    					sys.syscolumns b
    				on a.id = b.id 
    			 where /*b.xtype in (167,175,231,239) and*/ a.xtype = 'u'
    					and a.id = @idtabela
    			/*
    			167 varchar
    			175 char
    			231 nvarchar
    			239 nchar*/
    		OPEN db_cursorColunas   
    		FETCH NEXT FROM db_cursorColunas INTO @tabela, @coluna
    
    		WHILE @@FETCH_STATUS = 0   
    		BEGIN   
    				exec('
    				insert #tmpFindString
    				select '''  + @tabela + ''', string = '''+@valorProcurado+'''
    						from ' + @tabela + ' where ['+@coluna+'] like ''%'+@valorProcurado+'%''')
    			   
    			   FETCH NEXT FROM db_cursorColunas INTO @tabela, @coluna 
    		END   
    
    		CLOSE db_cursorColunas   
    		DEALLOCATE db_cursorColunas 
    	   
    	   FETCH NEXT FROM db_cursor INTO @idtabela 
    END   
    
    CLOSE db_cursor   
    DEALLOCATE db_cursor 
    
    	   
    select distinct * from #tmpFindString
    
    
    
    

    segunda-feira, 19 de maio de 2014 17:08
  • Este metodo que voce usou é realmente perigoso para algo que "aparentemente" é simples. Pois voce nao sai do loop ate que termine e usa dois while (dois cursor).

    Eu acredito que devemos usar um while para repassar tabela por tabela procurando o CPF informado, se ele existe em uma das tabelas. Uma vez localizado ele RETORNA o nome da tabela onde ele encontrou o cpf e sai do loop.

    As tabelas possuem a mesma estrutura... e eu poderia ate usar um UNION ALL porem achei que seria mais "pesado" pois usando WHILE eu consigo parar a procura quando encontrar o que desejo... e assim nao perder tempo rastreando milhoes de cpf desnecessário uma vez que ja encontrei o que procurava.

    Ou meu raciocinio esta errado?

    segunda-feira, 19 de maio de 2014 17:44
  • Deleted
    terça-feira, 20 de maio de 2014 12:04
  • Deleted
    terça-feira, 20 de maio de 2014 13:27
  • Usei o exemplo que voce fez, e quando fui testar apareceu o seguinte erro:

    Only functions and some extended stored procedures can be executed from within a function.

    Veja a Função Criada:

      

    USE [Filiais]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author: <Author,,Name>
    -- Create date: <Create Date, ,>
    -- Description: <Description, ,>
    -- =============================================
    CREATE FUNCTION [dbo].[ProcuraCliente]
    (

    @cpf_informado NVARCHAR(14)
    )
    RETURNS NVARCHAR(255)
    AS
    BEGIN 
    declare @cpf_Localizado nvarchar(14), @ComandoSQL nvarchar(400), @NomeTabela nvarchar(255)
    declare db_cursor CURSOR 
    FOR SELECT Name FROM SYSOBJECTS WHERE XTYPE='U'

    OPEN db_cursor
    FETCH NEXT from db_cursor into @NomeTabela

    while @@FETCH_STATUS = 0
     begin
     set @ComandoSQL = N'SELECT @cpf_Localizado = cpf from ' + @NomeTabela + N' where cpf = ' + QuoteName(@cpf_informado, '''')
     EXECUTE sp_executesql @ComandoSQL, N'@cpf_Localizado varchar(60) OUT', @cpf_Localizado OUTPUT
     IF @cpf_Localizado is not null
    -- achou!
    break
     else
    -- lê próximo nome de tabela
    FETCH NEXT from db_cursor into @NomeTabela
     end

    CLOSE db_cursor
    DEALLOCATE db_cursor
    RETURN @NomeTabela
    END

    e chamei a funcao da seguinte forma numa consulta:

    Select dbo.ProcuraCliente('348.458.510-34')

    Mensagem 557, Nível 16, Estado 2, Linha 1
    Only functions and some extended stored procedures can be executed from within a function.

    terça-feira, 20 de maio de 2014 15:20
  • Deleted
    terça-feira, 20 de maio de 2014 18:51
  • Pode ser uma Procedure, mais a função lhe agregará mais.
    terça-feira, 20 de maio de 2014 18:54
  • Pode ser uma procedure... mas como vou receber o valor de retorno usando o vb.net ?
    quarta-feira, 21 de maio de 2014 00:20