Usuário com melhor resposta
Função com valor de tabela com UPDATE e SELECT

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;
);
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
-
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
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
-
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?
-
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
-
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
BEGINUPDATE 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
-
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]
- Editado Junior Galvão - MVPMVP sábado, 22 de agosto de 2015 21:40
-
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
-
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!
- Sugerido como Resposta Thiago H. Vieira 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]
- Editado LWMN domingo, 23 de agosto de 2015 04:39
-
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!
-
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.
-
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? -
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
-
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!