SP e Multiplos Parametros

Question

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,

Answer 1

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.

Answer 2

Alguma duvidas do seu exemplo..
O numero de parametros é aleatorio e as actividades nem sempre são sequenciais para poder fazer um between

Answer 3

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 ''

Answer 4

 

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

 

 

 

 

Answer 5

Obrigado pelo exemplo..
Vou experimentar, e depois digo algo..

Obrigado..

Answer 6

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?

 

 

Answer 7

 

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

Answer 8

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..

Answer 9

Alexandre,

 

Excelente alternativa para o problema....

Me ajudou bastante, obrigado....

 

Lucio F.

Brazil-MG

Answer 10

 

Que bom Lúcio!!!

 

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

 

 

Grande abraço