none
Função Return Table RRS feed

  • Pergunta

  •    Boa Tarde colegas, estou com problema em uma função com opção de return table. Coloquei o codigo dela abaixo, mas é claro de forma enxuta pegando só o que interessa. O problema que vejo é ao colocar a linha " SET @NOME = 'TESTE'"  a função dá problema . Se eu tiro essa linha dá certo. É claro tb que nesse caso essa linha não faz o minimo sentido, pois a variável está com um valor fixo, mas como disse a vocês é só um exemplo pra mostrar o problema que estou tendo.

    CREATE function [dbo].[Teste_Func] ( @CODIGO_VAR int, @SIGLA_VAR INT )

    returns @temp_resultado table (CDGRUPO1 INT, VALOR1 NUMERIC(12,2), NOME1 VARCHAR(30))
    as
    BEGIN  
        declare 
    @NOME VARCHAR(30)
            ;with temp_valores (CDGRUPO2 , VALOR2 ) as 
           (
              select CDGRUPO, VALOR FROM GRUPO where CODIGO = @CODIGO_VAR and SIGLA = @SIGLA_VAR
            )
            SET @NOME = 'TESTE'
    INSERT INTO @temp_resultado SELECT CDGRUPO , VALOR, @NOME FROM temp_valores 
    Return
    END

    Grata!!!

    terça-feira, 3 de janeiro de 2017 15:22

Respostas

  • Isso Ocorre por que vc esta usando uma definição de  CTE  ,então logo após de finalizar a CTE deve se ter uma instrução select , update, delete

    /* trecho do MSDN     https://technet.microsoft.com/pt-br/library/ms190766(v=sql.105).aspx

    Uma CTE é constituída de um nome de expressão representando a CTE, uma lista de colunas opcional e uma consulta que define a CTE. Após a definição da CTE, ela poderá ser referenciada como uma tabela ou exibição em uma instrução SELECT, INSERT, UPDATE ou DELETE. Uma CTE também pode ser usada em uma instrução CREATE VIEW como parte da instrução SELECT que a define.

    */

    ou algo que a faça referencia vc pode fazer de duas formas 

    #CodigoExemplo

    CREATE FUNCTION [dbo].[Teste_Func](@CODIGO_VAR INT ,@SIGLA_VAR INT)
    RETURNS @temp_resultado TABLE
        (
          CDGRUPO1 INT ,
          VALOR1 NUMERIC(12, 2) ,
          NOME1 VARCHAR(30)
        )
    AS
        BEGIN  
            DECLARE @NOME VARCHAR(30);
            SET @NOME = 'TESTE';
            WITH    temp_valores ( CDGRUPO2, VALOR2 )
                      AS ( SELECT   CDGRUPO ,
                                    VALOR
                           FROM     GRUPO
                           WHERE    CODIGO = @CODIGO_VAR
                                    AND SIGLA = @SIGLA_VAR
                         )
                INSERT  INTO @temp_resultado
                        SELECT  CDGRUPO ,
                                VALOR ,
                                @NOME
                        FROM    temp_valores; 
            RETURN;
        END;

    ou tirar a CTE e usar uma nested subquery

    #Exemplo

    CREATE FUNCTION [dbo].[Teste_Func](@CODIGO_VAR INT ,@SIGLA_VAR INT)
    RETURNS @temp_resultado TABLE
        (
          CDGRUPO1 INT ,
          VALOR1 NUMERIC(12, 2) ,
          NOME1 VARCHAR(30)
        )
    AS
        BEGIN  
            DECLARE @NOME VARCHAR(30);
            SET @NOME = 'TESTE';
    
    
    		INSERT INTO @temp_resultado
    				  SELECT   T.CDGRUPO ,
                                T.VALOR ,
                                @NOME FROM   temp_valores T
            FROM    ( SELECT    CDGRUPO ,
                                VALOR
                      FROM      GRUPO
                      WHERE     CODIGO = @CODIGO_VAR
                                AND SIGLA = @SIGLA_VAR
                    ) AS temp_valores;
    
            RETURN;
        END;



    Saudações,,,

    Por favor,  marque-o como respondidas se está respondeu a sua pergunta 
    ou marcá-lo como útil se está ajudou a resolver o seu problema 
    Wesley Neves
    MTA-Database Fundamentals 
    Analista Desenvolvedor.NET


    • Editado Wesley Neves terça-feira, 3 de janeiro de 2017 15:40 correção
    • Marcado como Resposta LaraW terça-feira, 3 de janeiro de 2017 16:45
    terça-feira, 3 de janeiro de 2017 15:40

Todas as Respostas

  • Isso Ocorre por que vc esta usando uma definição de  CTE  ,então logo após de finalizar a CTE deve se ter uma instrução select , update, delete

    /* trecho do MSDN     https://technet.microsoft.com/pt-br/library/ms190766(v=sql.105).aspx

    Uma CTE é constituída de um nome de expressão representando a CTE, uma lista de colunas opcional e uma consulta que define a CTE. Após a definição da CTE, ela poderá ser referenciada como uma tabela ou exibição em uma instrução SELECT, INSERT, UPDATE ou DELETE. Uma CTE também pode ser usada em uma instrução CREATE VIEW como parte da instrução SELECT que a define.

    */

    ou algo que a faça referencia vc pode fazer de duas formas 

    #CodigoExemplo

    CREATE FUNCTION [dbo].[Teste_Func](@CODIGO_VAR INT ,@SIGLA_VAR INT)
    RETURNS @temp_resultado TABLE
        (
          CDGRUPO1 INT ,
          VALOR1 NUMERIC(12, 2) ,
          NOME1 VARCHAR(30)
        )
    AS
        BEGIN  
            DECLARE @NOME VARCHAR(30);
            SET @NOME = 'TESTE';
            WITH    temp_valores ( CDGRUPO2, VALOR2 )
                      AS ( SELECT   CDGRUPO ,
                                    VALOR
                           FROM     GRUPO
                           WHERE    CODIGO = @CODIGO_VAR
                                    AND SIGLA = @SIGLA_VAR
                         )
                INSERT  INTO @temp_resultado
                        SELECT  CDGRUPO ,
                                VALOR ,
                                @NOME
                        FROM    temp_valores; 
            RETURN;
        END;

    ou tirar a CTE e usar uma nested subquery

    #Exemplo

    CREATE FUNCTION [dbo].[Teste_Func](@CODIGO_VAR INT ,@SIGLA_VAR INT)
    RETURNS @temp_resultado TABLE
        (
          CDGRUPO1 INT ,
          VALOR1 NUMERIC(12, 2) ,
          NOME1 VARCHAR(30)
        )
    AS
        BEGIN  
            DECLARE @NOME VARCHAR(30);
            SET @NOME = 'TESTE';
    
    
    		INSERT INTO @temp_resultado
    				  SELECT   T.CDGRUPO ,
                                T.VALOR ,
                                @NOME FROM   temp_valores T
            FROM    ( SELECT    CDGRUPO ,
                                VALOR
                      FROM      GRUPO
                      WHERE     CODIGO = @CODIGO_VAR
                                AND SIGLA = @SIGLA_VAR
                    ) AS temp_valores;
    
            RETURN;
        END;



    Saudações,,,

    Por favor,  marque-o como respondidas se está respondeu a sua pergunta 
    ou marcá-lo como útil se está ajudou a resolver o seu problema 
    Wesley Neves
    MTA-Database Fundamentals 
    Analista Desenvolvedor.NET


    • Editado Wesley Neves terça-feira, 3 de janeiro de 2017 15:40 correção
    • Marcado como Resposta LaraW terça-feira, 3 de janeiro de 2017 16:45
    terça-feira, 3 de janeiro de 2017 15:40
  • Deleted
    terça-feira, 3 de janeiro de 2017 15:41
  •    Wesley, a opção um não me atende porque o SET tem que estar depois que já usei a CTE, pois esse usa o resultado dela. Coloquei ele assim pra simplicar , mas ficaria mais ou menos assim:

     SET @COUNT_VALOR = (SELECT COUNT(*) FROM TEMP_VALORES)

    Agora, a segunda opção vou analisar para ver se tem como eu adequar.

    Obrigada por enquanto

    terça-feira, 3 de janeiro de 2017 16:07
  •    Wesley, a opção um não me atende porque o SET tem que estar depois que já usei a CTE, pois esse usa o resultado dela. Coloquei ele assim pra simplicar , mas ficaria mais ou menos assim:

     SET @COUNT_VALOR = (SELECT COUNT(*) FROM TEMP_VALORES)


    troque o seu SET  por 

    SELECT @COUNT_VALOR = (SELECT COUNT(*) FROM TEMP_VALORES)


    Ficando a sua Function mais ou menos assim

    CREATE function [dbo].[Teste_Func] ( @CODIGO_VAR int, @SIGLA_VAR INT )
    returns @temp_resultado table (CDGRUPO1 INT, VALOR1 NUMERIC(12,2), NOME1 VARCHAR(30))
    as
    BEGIN  
        declare 
    @NOME VARCHAR(30)
            ;with temp_valores (CDGRUPO2 , VALOR2 ) as 
           (
              select CDGRUPO, VALOR FROM GRUPO where CODIGO = @CODIGO_VAR and SIGLA = @SIGLA_VAR
            )
            SELECT @NOME =( SELECT COUNT(*) FROM temp_valores)
    INSERT INTO @temp_resultado SELECT CDGRUPO , VALOR, @NOME FROM temp_valores 
    Return
    END


    • Editado Wesley Neves terça-feira, 3 de janeiro de 2017 16:42 Codigo
    terça-feira, 3 de janeiro de 2017 16:40
  •    Wesley, realmente não deu certo. Meu problema é que preciso criar uma tabela dentro dessa função que posteriormente em qualquer parte do codigo eu possa manipula-la. E NÃO é a tabela de return. Ou seja são duas situações, preciso de uma função que retorne uma tabela e que dentro dela tenha outra tabela que vou manipular. Tentei usar a CTE não deu, tentei usar uma tabela temporaria, mas mostra mensagem de que não posso usar comando de CREATE TABLE nesse tipo de função. Tentei criar uma visão com o resultado da CTE para poder manipular a visão mais abaixo no código e não deu também.

       Tá complicado viu.

    Grata!!!

    terça-feira, 3 de janeiro de 2017 16:45
  • vc trocou o SET pelo SELECT ? e não deu certo ?

    pergunto 

    1) qual valor deve ser atribuído para a variável  @COUNT_VALOR

    2)Aonde essa váriavel vai ser chamada ?

    3) vc pode postar o código completo  da Function ??


    Wesley Neves

    terça-feira, 3 de janeiro de 2017 16:51
  •    Wesley, substitui o SET pelo SELECT, ufa!!! deu certo, pelo menos em parte. A função criou sem erros, e antes não tava criando por causa disso. Agora vou testar o resultado final e te falo.

    Obrigada por enquanto.

    Valeu !!!

    terça-feira, 3 de janeiro de 2017 17:01
  • Deleted
    terça-feira, 3 de janeiro de 2017 17:16
  •    É para ser usado em qualquer parte da função. Não fora dela.

       Testei e não funcionou. Usei o codigo:

       ;with temp_valores (CDGRUPO2 , VALOR2 ) as 
           (
              select CDGRUPO, VALOR FROM GRUPO where CODIGO = @CODIGO_VAR and SIGLA = @SIGLA_VAR
            )
            SELECT @NOME =( SELECT COUNT(*) FROM temp_valores)

      e deu certo, mas quando faço referencia a CTE em um código mais embaixo dentro da mesma função não reconhece.

    Não consigo achar uma solução. Não posso usar criar tabelas temporárias, não posso criar uma CTE e usa-la mais abaixo em outro código. Tô quase usando duas funções separando os códigos e uma chamando a outra.

    Grata!!!

    terça-feira, 3 de janeiro de 2017 17:59
  • Vamos seguir a orientação do grande   José Diz , por  favor , postá o código completo e o que vc precisa como resultado ,

    talvez tenhamos outras soluções menos custosa que criar duas functions.

     

    Wesley Neves

    terça-feira, 3 de janeiro de 2017 18:12
  • Deleted
    quarta-feira, 4 de janeiro de 2017 10:36