Usuário com melhor resposta
SP e Multiplos Parametros

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,
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 ''
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.
-
-
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 '' -
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 SnippetCREATE 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
ENDSua procedure ficaria desta maneira:
Code SnippetCREATE PROC spuConsultaContatos @CodAtividades VARCHAR (200)
AS
SELECT C.* FROM Contatos C
WHERE C.CodAtividade IN ( SELECT IDs FROM dbo.fnStringToInt (@CodAtividades, ',') )
GOE por final, a chamada dela desta maneira:
Code SnippetEXEC spuConsultaContatos @CodAtividades = '2, 3, 5'
Se tiveres problemas na utilização da função, retorne que lhe ajudaremos.
Abraço
-
-
Não consegui aplicar o seu exemplo Alexandre..
Não percebi a seguinte parte:
E por final, a chamada dela desta maneira:
Code SnippetEXEC 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?
-
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
-
-
-