Error in running Function SQL Server

Question

I have a database proof today and I’m practicing functions with old exercises only 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.: primary and foreign keys were defined 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 year you have the following question:

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

then 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 executing with select:

select dbo.ex_f2(1)

here returns the error:

Msg 512, Level 16, State 1, Line 114 The sub-volume returned more than 1 value. This is not allowed when the subconsulta follows a =, != , <, <= , >, >= or when it is used as an expression.

PS2.: This is the Function that the teacher created when correcting the exercise (she 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

Answer 1

In the code of your function, replace the line

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

for

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