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

  • 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

     

    quarta-feira, 26 de novembro de 2008 10:11

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

    quarta-feira, 26 de novembro de 2008 10:25

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

    quarta-feira, 26 de novembro de 2008 10:25
  • Neider,

     

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

    quarta-feira, 26 de novembro de 2008 10:37
  • Junior

     

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

     

    Atenciosamente

     

    quarta-feira, 26 de novembro de 2008 18:08
  • Oi Neider,

     

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

     

    [ ]s,

     

    Gustavo

    quarta-feira, 26 de novembro de 2008 18:20
  • Gustavo

     

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

     

    Muito Obrigado pela ajuda!

    Atenciosamente

     

     

    quarta-feira, 26 de novembro de 2008 18:31
  • 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...

    terça-feira, 12 de maio de 2009 19:51
  • 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
    terça-feira, 12 de maio de 2009 20:33
  • 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.

    quarta-feira, 13 de maio de 2009 14:50
  • 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
    quarta-feira, 13 de maio de 2009 16:21
  • 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

    quarta-feira, 13 de maio de 2009 17:00
  • 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
    quinta-feira, 14 de maio de 2009 11:14
  • 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.

    quinta-feira, 14 de maio de 2009 17:26