Localizar um Item em Várias tabelas

Question

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

Answer 1

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?

Answer 2

Deleted

Answer 3

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

Answer 4

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'

Answer 5

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

Answer 6

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?

Answer 7

Deleted

Answer 8

Deleted

Answer 9

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.

Answer 10

Deleted

Answer 11

Pode ser uma Procedure, mais a função lhe agregará mais.

Answer 12

Pode ser uma procedure... mas como vou receber o valor de retorno usando o vb.net ?