none
Execute de Procedure retornando em uma variavel RRS feed

  • Pergunta

  • eu tenho uma variavel chamada @VarStrParametro

    que recebe um select da seguinte forma:

    Ex:

    set @VarStrParametro = @VarStrParametro + ' select top 1 cod_produto from produto'

     

    e estou executando essa query pelo comando execute

    Ex:

    Execute @VarStrParametro

     

    so que eu gostaria que uma outra variavel recebesse o resultado desse select

    algo tipo ou parecido com:

    Ex:

    set @VarRetornaResultado = execute @VarStrParametro

     

    porem da erro

     

    alguem poderia me ajudar nisso???

     

     

    terça-feira, 20 de novembro de 2007 19:08

Todas as Respostas

  •  

    Olá Fernando, o mais simples neste caso é usar o sp_executesql, veja o exemplo:

     

     

    Code Block

    declare @ret int

    exec sp_executesql N'select top 1 @Xret = cod_produto from produto', N'@Xret int OUTPUT', @ret OUTPUT

    select @ret

     

     

     

    Se tiver problemas retorne.

     

     

     

    Abraço

    terça-feira, 20 de novembro de 2007 19:18
  •  

    tem algum lugar que eu possa ler sobre sp_executesql ??

     

    pois quero usala da seguinte forma

     

    set dateformat dmy
    SELECT day(L.l_dtefetivacao) as dia, month(L.l_dtefetivacao) as mes, *
    FROM lancamento L
    INNER JOIN tipolancamento TL on TL.cod_tipolancamento = L.cod_tipolancamento
    INNER JOIN fonterecurso FR on FR.cod_fonte = L.cod_fonte
    LEFT JOIN subgrupoplanocontas S on L.cod_subgrupoplanocontas = S.cod_subgrupoplanocontas
    LEFT JOIN grupoplanocontas G on S.cod_grupoplanocontas = G.cod_grupoplanocontas
    WHERE
    L.cod_cliente = 451 and
    (L.l_cancelado <> 1 or L.l_cancelado is null)
    having ooh.SP_RetornaValPai(l.cod_lancamento,451,3,-1,9,'1/1/1900','1/1/1900','#',-1) <> 0
    ORDER BY L.l_dtefetivacao, FR.fr_nomeabreviacao, L.l_ordenacao, L.l_dtcadastro

     

     

    na verdade eu deveria fazer isso com uma função correto???

    o having somente funciona com funções ou posso usar uma procedure que retorne um registro???

    terça-feira, 20 de novembro de 2007 20:09
  • Você pode utilizar um SP, mas para isso é importante reduzir os campos de retorno do seu select.

     

     

     

    Espero ter ajudado

    terça-feira, 20 de novembro de 2007 20:14
  •  

    Fernando,

     

    Dá uma olhada nesse post: http://forums.microsoft.com/MSDN-BR/ShowPost.aspx?PostID=2429784&SiteID=21

     

    Estava falando com um colega do fórum exatamente sobre o sp_executesql. Nele coloquei um link de um site que explica muito bem sobre a utilização dessa sp em casos de execução de consulta dinâmicas, e claro, também descrevi com minhas palavras como usar a sp.

     

     

    No caso do seu problema, sim, pode resolver com uma função. Uma sp não pode ser usada dentro de uma consulta, mas a função sim.

     

     

    Se quiser, te ajudo a montar a função, basta postar qual o código que quer colocar nela.

     

     

    Grande abraço

    terça-feira, 20 de novembro de 2007 20:47
  • Fernando,

     

    No próprio Books On-Line do SQL Server você poderá obter mais informações, sobre a system stored procedure SP_ExecuteSQL.

     

    Mas gostaria de destacar uma das principais vantagens em utilizar a SP_ExecuteSQL é que ela utiliza o plano de execução do SQL Server, com isso, você poderá obter ganho de performance.

     

    quarta-feira, 21 de novembro de 2007 10:40
  • consegui implementar minha procedure da seguinte forma:

     

    ************************************************************************************************************************

    CREATE Procedure ooh.SP_RetornaValPai @cod_lancamento int ,@cod_cliente int,@cod_fonte int,@cod_tipolancamento int,@cod_grupoplanocontas int,
    @dataefetivacaoinicio smalldatetime,@dataefetivacaofim smalldatetime,@beneficiarioprovedor nvarchar(50),@cod_subgrupoplanocontas int
    As
    Begin


     Declare @CodLancamentoFilho int
     Declare @VarRetornaResultado bit
     Declare @VarStrParametro nvarchar(1000)
     Declare @paramlist  nvarchar(1000)   


     set @VarStrParametro = 'set dateformat dmy  '
     set @VarStrParametro = @VarStrParametro + ' SELECT top 1 l.cod_lancamento '
     set @VarStrParametro = @VarStrParametro + ' FROM lancamento L '
     set @VarStrParametro = @VarStrParametro + ' INNER JOIN tipolancamento TL on TL.cod_tipolancamento = L.cod_tipolancamento '
     set @VarStrParametro = @VarStrParametro + ' INNER JOIN fonterecurso FR on FR.cod_fonte = L.cod_fonte '
     set @VarStrParametro = @VarStrParametro + ' LEFT JOIN subgrupoplanocontas S on L.cod_subgrupoplanocontas = S.cod_subgrupoplanocontas '
     set @VarStrParametro = @VarStrParametro + ' LEFT JOIN grupoplanocontas G on S.cod_grupoplanocontas = G.cod_grupoplanocontas '
     set @VarStrParametro = @VarStrParametro + ' WHERE '
     set @VarStrParametro = @VarStrParametro + ' l.cod_lancamentocheque=@xcod_lancamento '
     set @VarStrParametro = @VarStrParametro + '  and (L.l_cancelado <> 1 or L.l_cancelado is null)   '


      if (@cod_cliente <> -1)
      begin
       set @VarStrParametro = @VarStrParametro + '  and L.cod_cliente=@xcod_cliente'
      end
     
      if (@cod_fonte <> -1)
      begin
       set @VarStrParametro = @VarStrParametro + '  and L.cod_fonte=@xcod_fonte '
      end
     
      if (@cod_tipolancamento <> -1)
      begin
       set @VarStrParametro = @VarStrParametro + '  and L.cod_tipolancamento=@xcod_tipolancamento'
      end
      if (@cod_grupoplanocontas <> -1)
      begin
     
       set @VarStrParametro = @VarStrParametro + ' and G.cod_grupoplanocontas=@xcod_grupoplanocontas'
      end
     
      if (@cod_subgrupoplanocontas <> -1)
      begin
       set @VarStrParametro = @VarStrParametro + '  and L.cod_subgrupoplanocontas=@xcod_subgrupoplanocontas'
      end
     
      if (@dataefetivacaoinicio <> '1/1/1900')
      begin
       set @VarStrParametro = @VarStrParametro + ' and  l_dtefetivacao>=@xdataefetivacaoinicio'
      end
     
      if (@dataefetivacaofim <> '1/1/1900')
      begin
       set @VarStrParametro = @VarStrParametro + '  and l_dtefetivacao<=@xdataefetivacaofim'
      end
     
      if (@beneficiarioprovedor <>'#')
      begin
       set @VarStrParametro = @VarStrParametro + '  and L.l_benefprovedor like ''%@xbeneficiarioprovedor%'''
      end

     Set @VarStrParametro = @VarStrParametro + ' ORDER BY L.l_dtefetivacao, FR.fr_nomeabreviacao, L.l_ordenacao, L.l_dtcadastro'


     SELECT @paramlist = '@xcod_lancamento int output,
                 @xcod_cliente int,
                 @xcod_fonte int,
                 @xcod_tipolancamento int,
                 @xcod_grupoplanocontas int,
                 @xdataefetivacaoinicio smalldatetime,
                 @xdataefetivacaofim smalldatetime,
                 @xbeneficiarioprovedor nvarchar(50),
                 @xcod_subgrupoplanocontas int'
     
     Exec sp_executesql @VarStrParametro, @paramlist ,
             @cod_lancamento output,
             @cod_cliente,
             @cod_fonte,
             @cod_tipolancamento,
             @cod_grupoplanocontas,
             @dataefetivacaoinicio,
             @dataefetivacaofim,
             @beneficiarioprovedor,
             @cod_subgrupoplanocontas              

    End
    GO

    ****************************************************************************************************************************

     

     

    so que tem o seguinte,

    no query analizer , eu executo o seguinte procedimento:

     

    execute ooh.SP_RetornaValPai 1168,451,3,-1,9,'1/1/1900','1/1/1900','#',-1

     

    e ela me retorna exatamente o valor do output

    ou seja, funciona perfeitamente

    mas quando executo ela dentro da minha query, tambem no query analizer

    ela da o seguinte erro :

     

    Server: Msg 208, Level 16, State 1, Line 2
    Invalid object name 'ooh.SP_RetornaValPai'.

     

    O QUE PODE ESTAR ACONTECENDO???????

     

    a minha query é a seguinte:

     

    ********************************************************************************

    set dateformat dmy
    SELECT day(L.l_dtefetivacao) as dia, month(L.l_dtefetivacao) as mes, *
    FROM lancamento L
    INNER JOIN tipolancamento TL on TL.cod_tipolancamento = L.cod_tipolancamento
    INNER JOIN fonterecurso FR on FR.cod_fonte = L.cod_fonte
    LEFT JOIN subgrupoplanocontas S on L.cod_subgrupoplanocontas = S.cod_subgrupoplanocontas
    LEFT JOIN grupoplanocontas G on S.cod_grupoplanocontas = G.cod_grupoplanocontas
    WHERE
    L.cod_cliente = 451 and
    (L.l_cancelado <> 1 or L.l_cancelado is null)
    having ooh.SP_RetornaValPai(l.cod_lancamento,451,3,-1,9,'1/1/1900','1/1/1900','#',-1) <> 0
    ORDER BY L.l_dtefetivacao, FR.fr_nomeabreviacao, L.l_ordenacao, L.l_dtcadastro

    *********************************************************************************************

     

    obs.: não considerar os parametros fixos,eles estão sendo enviados pra procedure

    deliberadamente para retornar algo que preciso como teste.

     

     

    quarta-feira, 21 de novembro de 2007 13:35
  •  

    Fernando,

     

    No meu post anterio eu comentei que não é possível usar procedures dentro de consultas! Você vai ter que fazer uma function!

     

     

    Abraço

    quarta-feira, 21 de novembro de 2007 15:11
  • è verdade, essa parte li rapido e não entendi anteriormente

    bom...., agora sei que devo abandonar a procedure pra isso e usarei uma função

     

    então como e o que eu posso aproveitar dessa procedure pra fazer a função?

    por que esse metodo de fazer a query dinamica eu preciso utilizar

    pensei em algo abaixo só trocando a chamada sp_executesql

    pode ser???

     

     

    vc tem uma ideia de como fazer essa função?

     

    *********************************************************************************

    CREATE Function f_ValidaLancamentoPai( @cod_lancamento int ,@cod_cliente int,@cod_fonte int,@cod_tipolancamento int,@cod_grupoplanocontas int,
    @dataefetivacaoinicio smalldatetime,@dataefetivacaofim smalldatetime,@beneficiarioprovedor nvarchar(50),@cod_subgrupoplanocontas int)
    Returns int
    As
    Begin


     Declare @CodLancamentoFilho int
     Declare @VarRetornaResultado bit
     Declare @VarStrParametro nvarchar(1000)
     Declare @paramlist  nvarchar(1000)   


     set @VarStrParametro = 'set dateformat dmy  '
     set @VarStrParametro = @VarStrParametro + ' SELECT top 1 l.cod_lancamento '
     set @VarStrParametro = @VarStrParametro + ' FROM lancamento L '
     set @VarStrParametro = @VarStrParametro + ' INNER JOIN tipolancamento TL on TL.cod_tipolancamento = L.cod_tipolancamento '
     set @VarStrParametro = @VarStrParametro + ' INNER JOIN fonterecurso FR on FR.cod_fonte = L.cod_fonte '
     set @VarStrParametro = @VarStrParametro + ' LEFT JOIN subgrupoplanocontas S on L.cod_subgrupoplanocontas = S.cod_subgrupoplanocontas '
     set @VarStrParametro = @VarStrParametro + ' LEFT JOIN grupoplanocontas G on S.cod_grupoplanocontas = G.cod_grupoplanocontas '
     set @VarStrParametro = @VarStrParametro + ' WHERE '
     set @VarStrParametro = @VarStrParametro + ' l.cod_lancamentocheque=@xcod_lancamento '
     set @VarStrParametro = @VarStrParametro + '  and (L.l_cancelado <> 1 or L.l_cancelado is null)   '


      if (@cod_cliente <> -1)
      begin
       set @VarStrParametro = @VarStrParametro + '  and L.cod_cliente=@xcod_cliente'
      end
     
      if (@cod_fonte <> -1)
      begin
       set @VarStrParametro = @VarStrParametro + '  and L.cod_fonte=@xcod_fonte '
      end
     
      if (@cod_tipolancamento <> -1)
      begin
       set @VarStrParametro = @VarStrParametro + '  and L.cod_tipolancamento=@xcod_tipolancamento'
      end
      if (@cod_grupoplanocontas <> -1)
      begin
     
       set @VarStrParametro = @VarStrParametro + ' and G.cod_grupoplanocontas=@xcod_grupoplanocontas'
      end
     
      if (@cod_subgrupoplanocontas <> -1)
      begin
       set @VarStrParametro = @VarStrParametro + '  and L.cod_subgrupoplanocontas=@xcod_subgrupoplanocontas'
      end
     
      if (@dataefetivacaoinicio <> '1/1/1900')
      begin
       set @VarStrParametro = @VarStrParametro + ' and  l_dtefetivacao>=@xdataefetivacaoinicio'
      end
     
      if (@dataefetivacaofim <> '1/1/1900')
      begin
       set @VarStrParametro = @VarStrParametro + '  and l_dtefetivacao<=@xdataefetivacaofim'
      end
     
      if (@beneficiarioprovedor <>'#')
      begin
       set @VarStrParametro = @VarStrParametro + '  and L.l_benefprovedor like ''%@xbeneficiarioprovedor%'''
      end

     Set @VarStrParametro = @VarStrParametro + ' ORDER BY L.l_dtefetivacao, FR.fr_nomeabreviacao, L.l_ordenacao, L.l_dtcadastro'


     SELECT @paramlist = '@xcod_lancamento int output,
                 @xcod_cliente int,
                 @xcod_fonte int,
                 @xcod_tipolancamento int,
                 @xcod_grupoplanocontas int,
                 @xdataefetivacaoinicio smalldatetime,
                 @xdataefetivacaofim smalldatetime,
                 @xbeneficiarioprovedor nvarchar(50),
                 @xcod_subgrupoplanocontas int'
     
     Exec sp_executesql @VarStrParametro, @paramlist ,
             @cod_lancamento output,
             @cod_cliente,
             @cod_fonte,
             @cod_tipolancamento,
             @cod_grupoplanocontas,
             @dataefetivacaoinicio,
             @dataefetivacaofim,
             @beneficiarioprovedor,
             @cod_subgrupoplanocontas     
        
    Return @cod_lancamento   
    End

    ***********************************************************************************************************************************

    quarta-feira, 21 de novembro de 2007 15:23
  •  

    Fernando...

     

    Olha, tá mais complicado que eu imaginava... por função não vai dar certo. E não dará para usar o sp_executesql.

     

    Uma alternativa que você pode usar em relação ao sp_executeSQL é no where fazer desta forma, exemplo:

    WHERE (cod_lancamentocheque = ISNULL (@cod_lancamentocheque, cod_lancamentocheque))

    and (cod_fonte = ISNULL(@cod_fonte, cod_fonte))

     

    Assim, o resultado será o mesmo que o sp_executesql, apenas com desempenho não tão bom.

     

    E com essa consulta, usará como uma subquery. Sendo bem sincero, não gosto de usar subquerys, pelo desempenho delas, mas pode ser a única opção.

     

     

    Abraço

    quinta-feira, 22 de novembro de 2007 04:36