Usuário com melhor resposta
Stored Procedure com vários parâmetros utilizando IN e retornando zero registros na consulta

Pergunta
-
Caros colegas
Criei a procedure abaixo para utilizar no reporting services, porém estou fazendo os testes na mesma antes de incluí-la no reporting services e detectei o seguinte problema:
Como possuo vários parâmetros na clausula where e a maioria deles multivalue, se passo um valor para cada parametro, ela traz o resultado corretamente. Agora se passo uma lista de valores, ela retorna zero.
Ex: Na área se passo 'C' ela retorna 50 registros, se passo 'A,C,D' ela retorna zero, o que pode ser?
CREATE
PROCEDURE [dbo].[rs_sp_subcontratos_datas]@projeto
AS VARCHAR(1000) , @area AS VARCHAR(100) , @pacote_subpacote AS VARCHAR(1000) , @seq_oc AS VARCHAR(3000) , @seq_rco AS VARCHAR(3000) , @fornecedor AS VARCHAR(3000) , @inicio_previsto AS VARCHAR(10) , @fim_previsto VARCHAR(10) , @ini_fim_previsto VARCHAR(1)AS
BEGIN
DECLARE
@strSQL AS VARCHAR(1000) SET NOCOUNT ON;SET
@strSQL = 'SELECT Projeto, Área
, Pacote
, [Sub pac]
, [Seq RCO]
, [Rev RCO]
, [ Seq OC] AS "Seq Oc"
, [Rev OC]
, Fornecedor
, escopo AS Escopo
, [Inicio Contratual] AS "Início Contratual"
, [Fim Contratual] AS "Fim Contratual"
, Evento
, Descrição
, Situação
, Ação
, [Inicio Previsto] AS "Início Previsto"
, [Fim Previsto]
, Alteração
, [Inicio Real] AS "Início Real"
, [Fim Real]
, [Previsao Entrega] AS "Previsão Entrega"
, Entrega
FROM dbo.v_r_subcontratos '
IF
(@projeto IS NOT NULL)BEGIN
SET @strSQL = @strSQL + 'WHERE reg_proj IN ('+ '''' + @projeto + '''' + ') 'END
IF
(@area IS NOT NULL)BEGIN
SET @strSQL = @strSQL + 'AND Área IN ('+ '''' + @area + '''' + ') 'END
IF
(@pacote_subpacote IS NOT NULL)BEGIN
SET @strSQL = @strSQL + 'AND reg_pac IN ('+ '''' + @pacote_subpacote + '''' + ') 'END
IF
(@seq_oc IS NOT NULL)BEGIN
SET @strSQL = @strSQL + 'AND [ Seq OC] IN (' + '''' + @seq_oc + '''' + ') 'END
IF
(@seq_rco IS NOT NULL)BEGIN
SET @strSQL = @strSQL + 'AND [Seq RCO] IN (' + '''' + @seq_rco + '''' + ') 'END
IF
(@fornecedor IS NOT NULL)BEGIN
SET @strSQL = @strSQL + 'AND reg_forn IN (' + '''' + @fornecedor + '''' + ') 'END
IF
(@inicio_previsto IS NOT NULL AND @ini_fim_previsto = 'N' )BEGIN
SET @strSQL = @strSQL + 'AND [Inicio Previsto] >= ' + @inicio_previsto + ' AND [Inicio Previsto] IS NOT NULL 'END
IF
(@fim_previsto IS NOT NULL AND @ini_fim_previsto = 'N' )BEGIN
SET @strSQL = @strSQL + 'AND [Fim Previsto] <= ' + @fim_previsto + ' AND [Fim Previsto] IS NOT NULL 'END
IF
(@ini_fim_previsto = 'S')BEGIN
SET @strSQL = @strSQL + 'AND ([Inicio Previsto] IS NULL OR [Fim Previsto] IS NULL) 'END
END
EXEC
(@strSQL)Por favor me ajudem a descobrir o que esta errado!
Atenciosamente
Respostas
-
Bom Dia Neider,
A resposta é muito simples. A string 'A,C,D' é diferente dos parâmetros 'A', 'C' e 'D'.
Se você deseja trabalhar com múltiplos valores terá que incrementar um pouco mais o seu EXEC. Ao invés de passar a string 'A,C,D' terá que utilizar um REPLACE para que fique 'A','C','D'. Ex:
Code SnippetDECLARE
@Valores VARCHAR(20), @cmdSQL VARCHAR(200)SET
@Valores = 'A,C,D'-- Incorreto
SET
@cmdSQL = 'SELECT * FROM TBL WHERE PAR IN (' + @Valores + ')'PRINT
@cmdSQL-- Incorreto
SET
@cmdSQL = 'SELECT * FROM TBL WHERE PAR IN (''' + @Valores + ''')'PRINT
@cmdSQL-- Correto
SET
@cmdSQL = 'SELECT * FROM TBL WHERE PAR IN (' + CHAR(39) + REPLACE(@Valores,',',CHAR(39)+ ',' +CHAR(39)) + CHAR(39) + ')'PRINT
@cmdSQLRecentemente postei no meu blog uma dica sobre como trabalhar com Array no SQL Server. Ainda essa semana postarei a parte 2. Embora seja focado no 2005, os exemplos são adaptáveis para o 2000. A parte 1 está disponível no link abaixo:
Matrizes no SQL Server 2005 - Parte I
http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!205.entry
Considerando que você está publicando esse relatório no Reporting Services, não deixe de tratar o SQL Injection.
[ ]s,
Gustavo
Todas as Respostas
-
Bom Dia Neider,
A resposta é muito simples. A string 'A,C,D' é diferente dos parâmetros 'A', 'C' e 'D'.
Se você deseja trabalhar com múltiplos valores terá que incrementar um pouco mais o seu EXEC. Ao invés de passar a string 'A,C,D' terá que utilizar um REPLACE para que fique 'A','C','D'. Ex:
Code SnippetDECLARE
@Valores VARCHAR(20), @cmdSQL VARCHAR(200)SET
@Valores = 'A,C,D'-- Incorreto
SET
@cmdSQL = 'SELECT * FROM TBL WHERE PAR IN (' + @Valores + ')'PRINT
@cmdSQL-- Incorreto
SET
@cmdSQL = 'SELECT * FROM TBL WHERE PAR IN (''' + @Valores + ''')'PRINT
@cmdSQL-- Correto
SET
@cmdSQL = 'SELECT * FROM TBL WHERE PAR IN (' + CHAR(39) + REPLACE(@Valores,',',CHAR(39)+ ',' +CHAR(39)) + CHAR(39) + ')'PRINT
@cmdSQLRecentemente postei no meu blog uma dica sobre como trabalhar com Array no SQL Server. Ainda essa semana postarei a parte 2. Embora seja focado no 2005, os exemplos são adaptáveis para o 2000. A parte 1 está disponível no link abaixo:
Matrizes no SQL Server 2005 - Parte I
http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!205.entry
Considerando que você está publicando esse relatório no Reporting Services, não deixe de tratar o SQL Injection.
[ ]s,
Gustavo
-
-
-
-
-
Boa tarde gustavo,
Estou com duas dúvidas sobre sua explicação:
1 - No exemplo acima, me parece que você utiliza a select, sabendo a quantidade de parâmetros que está chegando.
E quando não sabemos, por exemplo, eu posso ter até 20 parâmetros recebidos, mas pode ser de 1 a 20, como ficaria, quando você não sabe a quantidade de parâmetros que vai chegar?2 - o que significa o CHAR(39)?
Obrigado pela ajuda...
-
Boa Tarde,
Minhas soluções para esse caso não se baseiam em uma quantidade fixa de parâmetros.
Uma vez que você passa a string de parâmetros (ex: ('A','B','C')) você pode informar quantos parâmetros desejar.
CHAR(39) significa a string de aspas simples.
[ ]s,
Gustavo Maia Aguiar
http://gustavomaiaaguiar.spaces.live.com
Criando uma tabela com uma seqüência de números – Parte II
http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!556.entry
Classifique as respostas. O seu feedback é imprescindível -
Bom dia Gustavo,
Eu estou desconfiado que no meu caso, nem é o replace, porque é o seguinte, quando eu envio 2 variáveis, está indo corretamente.
Vai da seguinte maneira '01,02', nesse caso, eu acho que não precisa substituir nada, o problema é que o campo que está verificando é um campo data.
Month
(CLIENTEFISICA.DataNascimento) in (@Mes)
Está dando o seguinte erro:
Conversion failed when converting the nvarchar value '01,02' to data type int.
Vc tem alguma dica para resolver o problema?
Obrigado. -
Boa Tarde,
Dê um PRINT na variável antes de executá-la. Assim poderemos visualizá-la
[ ]s,
Gustavo Maia Aguiar
http://gustavomaiaaguiar.spaces.live.com
Criando uma tabela com uma seqüência de números – Parte II
http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!556.entry
Classifique as respostas. O seu feedback é imprescindível -
Oi,
Eu usei o Profile do Sql, e o que está sendo enviado é 01,02
Dei um print na variável e deu o seguinte retorno na mensagem
01,02
Msg 245, Level 16, State 1, Procedure SP_LISTA_ANIVERSARIANTES, Line 29
Conversion failed when converting the nvarchar value '01,02' to data type int.
O engraçado é que quando é executada a procedure aparece um return value = int, mas não existe isso na procedure, será que tem alguma coisa a ver?
Se você quiser eu posto a procedure completa para ver.
Valeu -
Bom Dia,
Rode o Profiler novamente mas dessa vez passe somente um valor e veja como ficou a instrução
[ ]s,
Gustavo Maia Aguiar
http://gustavomaiaaguiar.spaces.live.com
Criando uma tabela com uma seqüência de números – Parte II
http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!556.entry
Classifique as respostas. O seu feedback é imprescindível -