Error executing function SQL Server

0

I have a database test today and I'm practicing functions with old exercises only that I'm getting an error when I run the function.

tables

set language brazilian; 
CREATE TABLE pedido (  
nr_pedido numeric(5) NOT NULL,  
dt_pedido date,  
nr_mesa numeric(4)  
);  

CREATE TABLE itens_pedido (  
nr_pedido numeric(5) NOT NULL,  
nr_prod numeric(5) NOT NULL,  
qt_item smallint  
);  

CREATE TABLE produto (  
nr_prod numeric(5) NOT NULL,  
ds_produto varchar(60) NOT NULL,  
vl_venda numeric(9,2)  
); 

PS: the primary and foreign keys were set with alter table.

inserts

insert into produto values(1, 'Velho Barreiro', 5.00),
(2, 'Ypioca', 10.00),
(3, 'Casquinha de Caranguejo', 55.00),
(4, 'Calabresa', 15.00),
(5, 'Feijão Tropeiro', 29.50),
(6, 'Muqueca de camarão', 79.50),
(7, 'Coca-Cola', 9.90),
(8, 'Suco de Laranja', 2.50),
(9, 'Macaxeira', 12.30),
(10, 'Soda Limonada', 2.10),
(11, 'Coração de Galinha', 11.20),
(12, 'Vodka Skarloff', 13.70),
(13, 'Caninha 61', 9.90)

INSERT INTO pedido VALUES (1, GETDATE(), 527),
(2, '10/10/2009', 632),
(3, '21/05/2009', 606),
(4, '26/06/2009', 970),
(5, '05/05/2009', 606),
(6, '13/11/2009', 527),
(7, '01/03/2005', 181),
(8, '09/04/2010', 181);      

INSERT INTO itens_pedido VALUES (1, 11, 2),
(1, 4, 1),
(2, 3, 15),
(3, 2, 2),
(3, 8, 2),
(4, 1, 3),
(4, 7, 2),
(5, 5, 4),
(5, 6, 2),
(6, 6, 3),
(7, 10, 2),
(7, 9, 4),
(2, 2, 5);

In the exercise you have the following question:

  

2) Show the total value of the request passed as a parameter through a function.

Then I created the following function:

create function ex_f2(@ped numeric(5))
returns numeric(8,2)
as
begin
declare @total numeric(8,2)
set @total = (select pr.vl_venda * i.qt_item
from pedido p inner join itens_pedido i 
on p.nr_pedido = i.nr_pedido inner join produto pr 
on pr.nr_prod = i.nr_prod
where p.nr_pedido = @ped)
return (@total)
end

But when running with select:

  

select dbo.ex_f2 (1)

Here is the error:

  

Msg 512, Level 16, State 1, Line 114   The subquery returned more than 1   value. This is not allowed when the subquery follows a =,! =, & Lt ;, < =   , & gt ;, > = or when it is used as an expression.

PS2 .: This is the function that the teacher created at the time of correcting the exercise (it gives the same error):

create function ex_f2(@pedi numeric(5))
returns numeric(8,2)
as
begin
return (select i.qt_item * pr.vl_venda 'valor total'
from pedido p inner join itens_pedido i on p.nr_pedido = i.nr_pedido
inner join produto pr on pr.nr_prod = i.nr_prod
where p.nr_pedido = @pedi)
end
    
asked by anonymous 30.11.2017 / 19:39

1 answer

1

In your function code, replace the

set @total = (select pr.vl_venda * i.qt_item

by

set @total = (select sum(pr.vl_venda * i.qt_item)
    
30.11.2017 / 19:46