CODEWITHSUNDEEP
mysqlfunctionjoin
Charlie
Charlie

Reputation: 31

Trouble completing a function in mySQL

When I work on functions I like to test a stand-alone select statement first. I did that and it worked out. My problem is when I try to convert it into a function.

This is the select statement and it works

select sum(item.retailPrice * orderItem.quantity)
from orderItem
join orders 
on orders.orderId = orderItem.orderId
join item
on item.itemId = orderItem.itemId
where orders.orderId = 4

This is my function and it gives errors. 

CREATE FUNCTION getTotalPrice(oId int) 
RETURNS int
READS SQL DATA
BEGIN
declare total int;
select sum(item.retailPrice * orderItem.quantity) into total
from orderItem
join orders 
on orders.orderId = orderItem.orderId
join item
on item.itemId = orderItem.itemId
where orders.orderId = oId
return total;
END$$

The function should allow the user to input their orderId.

Upvotes: 0

Views: 20

Answers (1)

Mark Madej
Mark Madej

Reputation: 1922

If you're using SELECT INTO to select into a variable, you need to use the @ sign to preface those variables. Unfortunately SQLFiddle isn't responding right now so I can validate this suggestion but I recommend trying changing these two lines:

select sum(item.retailPrice * orderItem.quantity) into total
...
return total;

To this:

select sum(item.retailPrice * orderItem.quantity) into @total
...
return @total;

Good luck!

Upvotes: 1

Related Questions