Função Return Table

Question

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

Answer 1

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

Answer 2

Deleted

Answer 3

   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

Answer 4

   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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

Deleted

Answer 9

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

Answer 10

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

Answer 11

Deleted