none
Função com valor de tabela com UPDATE e SELECT RRS feed

  • Pergunta

  • Pessoal é possível criar uma função com valor de tabela embutida realizando um update em um determinado registro tabela conforme o parâmetro passado e em seguida mostrar o resultado da atualização do registro? Quero usar uma função porque o problema na verdade não é só o código abaixo, mas eu conseguindo fazer nesse exemplo simples conseguiria adaptar no problema real.

    CREATE FUNCTION fn_alterarcategoria
    (
    @id int,
    @nome varchar(50)
    )
    RETURNS TABLE
    AS
    RETURN
    (
    UPDATE dbo.Categoria SET nome_categoria = @nome WHERE id_categoria = @id;
    SELECT id_categoria, nome_categoria FROM dbo.Categoria WHERE id_categoria = @id;
    );
    sexta-feira, 21 de agosto de 2015 21:28

Respostas

  • WMN8,

    Peço desculpas, mas acredito que não é possível executar esse Update dentro de uma função, pois o mesmo faz alterações no banco de dados, e pelo que vi isso não é permitido.

    Dependendo do caso, você pode conseguir o resultado desejado criando uma procedure em vez de uma função.

    Espero que ajude.


    Assinatura: http://www.imoveisemexposicao.com.br

    • Marcado como Resposta LWMN domingo, 23 de agosto de 2015 18:24
    sábado, 22 de agosto de 2015 23:07
  • Corrigindo, como o retorno da UDF é um parâmetro tabular, a chamada da função deve ser na cláusula FROM.

    Segue código;

    create table categoria
    (
     id int not null
      ,valor numeric(5,2)
     );
     
     insert into categoria
     values(1,5.0),(2,10.0);
     
    create function udf_alteraCategoria(@id int,@taxa numeric(5,2))
     RETURNS @CategoriaTab TABLE
       (
        id     int,
        valor  numeric(5,2)
        )
    AS
    BEGIN
       INSERT @CategoriaTab
            SELECT C.id, C.valor * @taxa
            FROM Categoria as C
            WHERE C.id = @id
       
       RETURN
    END
       
    select * from udf_alteraCategoria(1,1.3)

    Caso tenha te ajudado, favor marcar como resposta.

    Obrigado.

    • Marcado como Resposta LWMN domingo, 23 de agosto de 2015 18:22
    domingo, 23 de agosto de 2015 18:03

Todas as Respostas

  • Boa noite,

    Experimente utilizar a clausula Output para inserir os dados atualizados na tabela de retorno da função. Ex:

    CREATE FUNCTION fn_alterarcategoria
    (
        @id int,
        @nome varchar(50)
    )
    RETURNS @retCategoria TABLE
    (
        id_categoria int,
        nome_categoria varchar(50)
    )
    AS
    begin
        UPDATE dbo.Categoria 
        SET nome_categoria = @nome 
        WHERE id_categoria = @id
        OUTPUT inserted.id_categoria, inserted.nome_categoria
        INTO @retCategoria;
    
        RETURN;
    end;

    Espero que ajude.


    Assinatura: http://www.imoveisemexposicao.com.br

    sexta-feira, 21 de agosto de 2015 21:47
  • Boa noite,

    Experimente utilizar a clausula Output para inserir os dados atualizados na tabela de retorno da função. Ex:

    CREATE FUNCTION fn_alterarcategoria
    (
        @id int,
        @nome varchar(50)
    )
    RETURNS @retCategoria TABLE
    (
        id_categoria int,
        nome_categoria varchar(50)
    )
    AS
    begin
        UPDATE dbo.Categoria 
        SET nome_categoria = @nome 
        WHERE id_categoria = @id
        OUTPUT inserted.id_categoria, inserted.nome_categoria
        INTO @retCategoria;
    
        RETURN;
    end;

    Espero que ajude.


    Assinatura: http://www.imoveisemexposicao.com.br

    Gapimex, está dando o seguinte erro: Incorrect syntax near 'OUTPUT'.

    Não está aceitando o OUTPUT, preciso de mais alguma coisa?

    sexta-feira, 21 de agosto de 2015 21:56
  • Experimente deixar o Where no final:

        UPDATE dbo.Categoria 
        SET nome_categoria = @nome 
        OUTPUT inserted.id_categoria, inserted.nome_categoria
        INTO @retCategoria
        WHERE id_categoria = @id;
    

    Espero que ajude.


    Assinatura: http://www.imoveisemexposicao.com.br

    sexta-feira, 21 de agosto de 2015 23:27
  • Experimente deixar o Where no final:

        UPDATE dbo.Categoria 
        SET nome_categoria = @nome 
        OUTPUT inserted.id_categoria, inserted.nome_categoria
        INTO @retCategoria
        WHERE id_categoria = @id;

    Espero que ajude.


    Assinatura: http://www.imoveisemexposicao.com.br

    Gapimex, está dando agora o seguinte erro: Invalid use of a side-effecting operator 'UPDATE' within a function.

    Segue o código com as modificações:

    CREATE FUNCTION fn_alterarcategoria
    (
        @id int,
        @nome varchar(50)
    )
    RETURNS @retCategoria TABLE
    (
        id_categoria int,
        nome_categoria varchar(50)
    )
    AS
    BEGIN

    UPDATE dbo.Categoria 
    SET nome_categoria = @nome 
    OUTPUT inserted.id_categoria, inserted.nome_categoria
    INTO @retCategoria
    WHERE id_categoria = @id;

    RETURN;
    END;


    • Editado LWMN sábado, 22 de agosto de 2015 02:08
    sábado, 22 de agosto de 2015 02:08
  • WMN8,

    Verifique o seu código, se você esta utilizando uma Function Multi-Statament Table Value, ou seja, uma função que vai retornar uma tabela como valor, é necessário na linha do comando Return informar a instrução Select que será responsável em apresentar o dados em tela.

    A última instrução da sua function não pode ser o comando Update, tem que ser um Select!!!!


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    sábado, 22 de agosto de 2015 21:37
  • WMN8,

    Peço desculpas, mas acredito que não é possível executar esse Update dentro de uma função, pois o mesmo faz alterações no banco de dados, e pelo que vi isso não é permitido.

    Dependendo do caso, você pode conseguir o resultado desejado criando uma procedure em vez de uma função.

    Espero que ajude.


    Assinatura: http://www.imoveisemexposicao.com.br

    • Marcado como Resposta LWMN domingo, 23 de agosto de 2015 18:24
    sábado, 22 de agosto de 2015 23:07
  • Boa noite WMN8,

    Conforme consta na documentação da Microsoft, UDF não podem realizar ações que modifiquem o estado do banco de dados. "[...]User-defined functions cannot be used to perform actions that modify the database state" https://msdn.microsoft.com/en-us/library/ms186755.aspx. Porém é possível efetuar operações DML sobre a tabela de retorno antes de retornar os dados propriamente.

    Segue exemplo de function que altera dados antes de retornar o resultado.

    CREATE FUNCTION ufn_alteraCategoria (@id int, @taxa numeric(5,2) )
    RETURNS @CategoriaTab TABLE
       (
        id     int,
        valor  numeric(5,2),
        )
    AS
    BEGIN
       INSERT @CategoriaTab
            SELECT C.id, C.valor * @taxa
            FROM Categoria as C
            WHERE C.id = @id
       
       RETURN
    END

    Caso a necessidade seja alterar os dados permanentemente no DB, recomendo que utilize uma procedure.

    Espero ter ajudado.

    Abraços!

    domingo, 23 de agosto de 2015 02:04
  • WMN8,

    Verifique o seu código, se você esta utilizando uma Function Multi-Statament Table Value, ou seja, uma função que vai retornar uma tabela como valor, é necessário na linha do comando Return informar a instrução Select que será responsável em apresentar o dados em tela.

    A última instrução da sua function não pode ser o comando Update, tem que ser um Select!!!!


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    Oi Júnior, eu faço meus códigos direto na janela de código do SQL Server e ele identifica se é uma View, Trigger, Stored Procedure ou Function e lança direto no local adequado. Eu verifiquei também conforme você me orientou sobre a cláusula SELECT.
    • Editado LWMN domingo, 23 de agosto de 2015 04:39
    domingo, 23 de agosto de 2015 04:37
  • Boa noite WMN8,

    Conforme consta na documentação da Microsoft, UDF não podem realizar ações que modifiquem o estado do banco de dados. "[...]User-defined functions cannot be used to perform actions that modify the database state" https://msdn.microsoft.com/en-us/library/ms186755.aspx. Porém é possível efetuar operações DML sobre a tabela de retorno antes de retornar os dados propriamente.

    Segue exemplo de function que altera dados antes de retornar o resultado.

    CREATE FUNCTION ufn_alteraCategoria (@id int, @taxa numeric(5,2) )
    RETURNS @CategoriaTab TABLE
       (
        id     int,
        valor  numeric(5,2),
        )
    AS
    BEGIN
       INSERT @CategoriaTab
            SELECT C.id, C.valor * @taxa
            FROM Categoria as C
            WHERE C.id = @id
       
       RETURN
    END

    Caso a necessidade seja alterar os dados permanentemente no DB, recomendo que utilize uma procedure.

    Espero ter ajudado.

    Abraços!

    Thiago, o seu código de exemplo não está dando erros ele executa, mas não dá retorno. Será que o insert também não é aceito?
    domingo, 23 de agosto de 2015 04:41
  • Bom dia WMN8, a instrução de INSERT é realizada sobre o parâmetro de retorno, que é do tipo TABLE. Vocês está invocando a UDF com parâmetros válidos? Exemplo,, SELECT ufn_alteraCategoria(10, 1.53). Para ter um resultado deve existir um ID = 10 na tabela Categoria.
    domingo, 23 de agosto de 2015 12:23
  • Bom dia WMN8, a instrução de INSERT é realizada sobre o parâmetro de retorno, que é do tipo TABLE. Vocês está invocando a UDF com parâmetros válidos? Exemplo,, SELECT ufn_alteraCategoria(10, 1.53). Para ter um resultado deve existir um ID = 10 na tabela Categoria.
    Sim, é exatamente como eu fiz. No seu funcionou?
    domingo, 23 de agosto de 2015 17:39
  • Corrigindo, como o retorno da UDF é um parâmetro tabular, a chamada da função deve ser na cláusula FROM.

    Segue código;

    create table categoria
    (
     id int not null
      ,valor numeric(5,2)
     );
     
     insert into categoria
     values(1,5.0),(2,10.0);
     
    create function udf_alteraCategoria(@id int,@taxa numeric(5,2))
     RETURNS @CategoriaTab TABLE
       (
        id     int,
        valor  numeric(5,2)
        )
    AS
    BEGIN
       INSERT @CategoriaTab
            SELECT C.id, C.valor * @taxa
            FROM Categoria as C
            WHERE C.id = @id
       
       RETURN
    END
       
    select * from udf_alteraCategoria(1,1.3)

    Caso tenha te ajudado, favor marcar como resposta.

    Obrigado.

    • Marcado como Resposta LWMN domingo, 23 de agosto de 2015 18:22
    domingo, 23 de agosto de 2015 18:03
  • Corrigindo, como o retorno da UDF é um parâmetro tabular, a chamada da função deve ser na cláusula FROM.

    Segue código;

    create table categoria
    (
     id int not null
      ,valor numeric(5,2)
     );
     
     insert into categoria
     values(1,5.0),(2,10.0);
     
    create function udf_alteraCategoria(@id int,@taxa numeric(5,2))
     RETURNS @CategoriaTab TABLE
       (
        id     int,
        valor  numeric(5,2)
        )
    AS
    BEGIN
       INSERT @CategoriaTab
            SELECT C.id, C.valor * @taxa
            FROM Categoria as C
            WHERE C.id = @id
       
       RETURN
    END
       
    select * from udf_alteraCategoria(1,1.3)

    Caso tenha te ajudado, favor marcar como resposta.

    Obrigado.

    Thiago, funcionou perfeitamente agora.

    Obrigado pela ajuda e por exclarecer sobre as regras da function.

    Obrigado a todos do forum!

    domingo, 23 de agosto de 2015 18:22