0

I need help creating this particular stored function and call it by using the single select statement. Below are the questions with my answer. I think I got the first part right but I'm not sure. Any suggestions/advice? For the second question (part b), I'm calling the function incorrectly and can't get it to appear as specified in question/part b. Any advice? I would really appreciate the assistance.

Part A) Create a stored function called get_customer_balance which will return a customer’s balance from the membership table by passing in a membership number.

My Answer: 

DELIMITER $$
CREATE FUNCTION get_customer_balance (membershipNo INT) 
RETURNS dec 
DETERMINISTIC
BEGIN
DECLARE CustBal dec;
SET CustBal = 0;
SELECT balance INTO CustBal  
FROM membership
WHERE membership_id = membershipNo;
RETURN CustBal;
END$$
DELIMITER ;

Part B question

Membership Table. This is the original table of the problem (for reference guide)

Batman101
  • 13
  • 4
  • answer is in this thread [link](http://stackoverflow.com/questions/14506871/how-to-execute-a-stored-procedure-inside-a-select-query) – nowRails Dec 04 '15 at 21:06

1 Answers1

0
create table membership
(   membership_id int primary key,
    balance decimal(10,2) not null
);

insert membership(membership_id,balance) values (1,1),(102,11),(103,109.25);

select membership_id,format(get_customer_balance(membership_id),2) as theBalance 
from membership 
where membership_id=102;

+---------------+------------+
| membership_id | theBalance |
+---------------+------------+
|           102 | 11.00      |
+---------------+------------+

Mysql Manual page on Create Proc and Functions

Calling your user defined function (UDF) would be like calling any built-in function. Even if it involved joins on tables with aliases (which the above does not show).

A much better example would be one in which there are two tables. A membership table, and a transaction table that needs summed. But that wasn't your schema.

Would you like to see such a thing?

Drew
  • 24,851
  • 10
  • 43
  • 78
  • Thanks Drew! But I notice your first example is creating a table. I need to create a function. My table is already in the database. – Batman101 Dec 04 '15 at 21:24
  • I am using the function you created, and showed the data you did not have scripted. So I did not feel the need to re-show your function. I was focusing on the part of `how to call it` ... which was your question afterall, no ? – Drew Dec 04 '15 at 21:27
  • I don't get paid enough for this :P – Drew Dec 04 '15 at 21:28
  • when you are happy with Answers, give them a Green check mark signifying they are answered. Even if that means waiting a week or two – Drew Dec 04 '15 at 21:33
  • Will do. But there's an issue. The number 11 doesn't appear as 11.00 (with zeros) like displayed in the part b problem. Any idea why that is so? Is it because I might've done something wrong with my statement in part a? – Batman101 Dec 04 '15 at 21:43