none
SP e Multiplos Parametros RRS feed

  • Pergunta

  • Boas..

    Estou a desenvolver uma aplicação que serve como Lista de Contactos, em que cada contacto têm um Actividade associada..

    A minha dúvida prende-se no seguinte:
    Ao fazer uma pesquisa eu posso escolher mais do que uma Actividade, como passo isso por SP Parameters?

    Exemplo:
    Pesquisar pela actividade 1, 3 e 5, e o SP devolver-me só os contactos que têm como actividade as mencionadas

    Se não fosse por SP, mas por Text, era fácil, era só fazer um SELECT parecido com o seguinte:
    "SELECT * FROM Contactos WHERE CodActividade=1 AND codActividade=3 AND Codactividade=5"

    Com os melhores cumprimentos,
    segunda-feira, 25 de junho de 2007 13:36

Respostas

  • Vitor,

    imaginando que sua query seja:

    "SELECT * FROM Contactos WHERE CodActividade=1 OR codActividade=3 OR Codactividade=5"
    vc pode fazer assim:

    create procedure atividade_sps
    @atividade varchar(200)
    as
    set nocount on

    declare @tabela table(atividade_id int)
    declare @posicao int
    declare @max int

    --inicializa o posicionamento de caracteres
    set @posicao = 1
    set @max = 0

    --verifica se existem atividades
    if len(@atividade) > 0
    begin
    --captura o tamanho do string
    set @max = len(@atividade)
    --percorre as atividades
    while @posicao < @max
    begin
    --insere a empresa na tabela temporária
    insert into @tabela(atividade_id)values(substring(@atividade,@posicao,5))
    --incrementa posicionador de caractere
    set @posicao = @posicao + 5
    end
    SELECT a.*
    FROM Contactos a,
    @tabela b
    where a.CodActividade = b.atividade_id
    end
    else
    begin
    --não foi passada nenhuma atividade
    SELECT a.*
    FROM Contactos a
    end

    Então, se vc quer as atividades 1, 3 e 5, chame a SP assim:
    procedure atividade_sps '000010000300005'

    Se quiser listar todos os registros sem qualquer filtro, basta chamar assim:
    procedure atividade_sps ''



    segunda-feira, 25 de junho de 2007 14:03

Todas as Respostas

  • Vitor,

     

    Você deverá uma stored procedure contendo vários parâmetros, neste caso, por exemplo três, de acordo com a sua necessidade.

     

    Veja o exemplo:

     

    Create Procedure P_Consulta @CodAtividade1 Int, @CodAtividade2 Int, @CodAtividade3 Int

     As

     Begin

      Select * from Contactos

      Where CodAtividade = @CodAtividade1

      And      BetWeen @CodAtividade2 And @CodAtividade3

     End

     

     

    Incialmente você poderia trabalhar assim, mas estou procurando outras possibilidade que possam te atender.

    segunda-feira, 25 de junho de 2007 13:54
  • Alguma duvidas do seu exemplo..
    O numero de parametros é aleatorio e as actividades nem sempre são sequenciais para poder fazer um between
    segunda-feira, 25 de junho de 2007 14:02
  • Vitor,

    imaginando que sua query seja:

    "SELECT * FROM Contactos WHERE CodActividade=1 OR codActividade=3 OR Codactividade=5"
    vc pode fazer assim:

    create procedure atividade_sps
    @atividade varchar(200)
    as
    set nocount on

    declare @tabela table(atividade_id int)
    declare @posicao int
    declare @max int

    --inicializa o posicionamento de caracteres
    set @posicao = 1
    set @max = 0

    --verifica se existem atividades
    if len(@atividade) > 0
    begin
    --captura o tamanho do string
    set @max = len(@atividade)
    --percorre as atividades
    while @posicao < @max
    begin
    --insere a empresa na tabela temporária
    insert into @tabela(atividade_id)values(substring(@atividade,@posicao,5))
    --incrementa posicionador de caractere
    set @posicao = @posicao + 5
    end
    SELECT a.*
    FROM Contactos a,
    @tabela b
    where a.CodActividade = b.atividade_id
    end
    else
    begin
    --não foi passada nenhuma atividade
    SELECT a.*
    FROM Contactos a
    end

    Então, se vc quer as atividades 1, 3 e 5, chame a SP assim:
    procedure atividade_sps '000010000300005'

    Se quiser listar todos os registros sem qualquer filtro, basta chamar assim:
    procedure atividade_sps ''



    segunda-feira, 25 de junho de 2007 14:03
  •  

    Vitor,

     

    Caso você tenha mais de uma Atividade relacionada, você poderá passar um parâmetro contendo todas atividades desejadas por texto e depois transformar em INT. Para fazer isso, use esta função que criei:

     

    Code Snippet

    CREATE FUNCTION [dbo].[fnStringToInt] (@String VARCHAR(1000), @Separador CHAR(1))
    RETURNS @Inteiros TABLE (IDs int)
    AS
    BEGIN
     DECLARE @Int INT
     IF PATINDEX ('%' + @Separador + '%', @String) = 0
      INSERT INTO @Inteiros VALUES (@String)
     ELSE
     BEGIN
      WHILE PATINDEX ('%' + @Separador + '%', @String) > 0
      BEGIN
       INSERT INTO @Inteiros VALUES (CONVERT (INT, SUBSTRING (@String, 1, PATINDEX ('%' + @Separador + '%', @String) - 1)))
       SET @String = SUBSTRING (@String, PATINDEX ('%' + @Separador + '%', @String) + 1, LEN(@String))
      END
      INSERT INTO @Inteiros VALUES (@String)
     END
     RETURN
    END

     

     Sua procedure ficaria desta maneira:

     

    Code Snippet
    CREATE PROC spuConsultaContatos @CodAtividades VARCHAR (200)
    AS
     SELECT C.* FROM Contatos C
     WHERE C.CodAtividade IN ( SELECT IDs FROM dbo.fnStringToInt (@CodAtividades, ',') )
    GO

     

    E por final, a chamada dela desta maneira:

    Code Snippet

     

    EXEC  spuConsultaContatos @CodAtividades = '2, 3, 5'

     

     

     Se tiveres problemas na utilização da função, retorne que lhe ajudaremos.

     

     

     

    Abraço

     

     

     

     

    segunda-feira, 25 de junho de 2007 14:07
  • Obrigado pelo exemplo..
    Vou experimentar, e depois digo algo..

    Obrigado..
    segunda-feira, 25 de junho de 2007 14:07
  • Não consegui aplicar o seu exemplo Alexandre..
    Não percebi a seguinte parte:

    E por final, a chamada dela desta maneira:

    Code Snippet

     

    EXEC  spuConsultaContatos @CodAtividades = '2, 3, 5'

     

     

    ..

    Consegui aplicar a do Gesiel, no entanto, não agradava muito ter que inserir os valor como 000100030005...

    No seu código, como passo o sqlparameter no VB.net?

     

     


    segunda-feira, 25 de junho de 2007 16:14
  •  

    Vitor,

     

    Em geral, se recomenda fazer qualquer consulta via SP. Via texto você corre dois risco, o primeiro é estar passando uma grande quantidade de informações (tamanho da instrução, causando maior tráfego de informações na rede) e além disso você abre espaço para utilização de SQL Injection (técnica de ataque ao banco de dados). Com a utilização de Stored Procedures você fica a salvo desses dois problemas.

     

    No meu exemplo, você passaria os dados de parâmetro diretamente na chamada para a procedure. A forma como exemplifiquei, seria para poder passar uma quantidade variável de parâmetros, não somente 3 como você passou no primeiro exemplo. O parâmetro que é informado na procedure é a coleção de IDs que quer retornar.

     

    Espero ter esclarecido.

     

     

    Abraço

    segunda-feira, 25 de junho de 2007 20:27
  • Essas questões eu sei..

    A questão é que não percebi como executar as instruções que mencionei que não sabia acima..

     

    Com os melhores cumprimentos e agradecendo toda a ajuda..

    terça-feira, 26 de junho de 2007 09:17
  • Alexandre,

     

    Excelente alternativa para o problema....

    Me ajudou bastante, obrigado....

     

    Lucio F.

    Brazil-MG

    quarta-feira, 15 de agosto de 2007 20:22
  •  

    Que bom Lúcio!!!

     

    Quando tiver qualquer problema, estamos por aqui para tentar ajudar.

     

     

    Grande abraço

    quinta-feira, 16 de agosto de 2007 02:12