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

Question

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

 

Answer 1

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 Snippet

DECLARE @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 @cmdSQL

 

Recentemente 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

Answer 2

Neider,

 

Mas 'A,C,D' representa uma string ou cada letra deverá ser considerada strings separadas?

Answer 3

Junior

 

Cada letra é uma string, ou seja 'A', uma string, 'C' outra string e por ai vai

 

Atenciosamente

 

Answer 4

Oi Neider,

 

Foi o que pensei. Minhas considerações o ajudaram ?

 

[ ]s,

 

Gustavo

Answer 5

Gustavo

 

Suas considerações foi a solução.

 

Muito Obrigado pela ajuda!

Atenciosamente

 

 

Answer 6

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

Answer 7

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

Answer 8

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.

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

Boa Tarde Gustavo,

Passando somente 1 Parâmetro a instrução ficou assim:

exec SP_LISTA_ANIVERSARIANTES @Agencia=N'4141',@Mes=N'02',@Situação=N'1'

Passando 2 parâmetros ficou assim:

Dá o problema de conversão.