Função com valor de tabela com UPDATE e SELECT

Question

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

Answer 1

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

Answer 2

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?

Answer 3

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

Answer 4

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;

Answer 5

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]

Answer 6

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

Answer 7

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!

Answer 8

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.

Answer 9

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?

Answer 10

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.

Answer 11

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?

Answer 12

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.

Answer 13

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!