How to convert the following stored procedure to a prepared statement?

Question

The following stored procedure only accepts parameters where a real integer is explicitly written out. E.g.: CALL test11(46).

I’d like it re-written so it can accept something similar to the following:

SELECT *, CALL test11(vendor_id) FROM vendors;

Or

CALL test11(SELECT vendor_id FROM vendors WHERE vendor_name = 'Micro Center');

I understand the first/former is pointless, since an IF statement can be implemented without having to resort to a stored procedure. But I find the latter very crucial to know.

Stored procedure syntax:

DELIMITER //
CREATE PROCEDURE test11(IN vendor_id_var INT)
BEGIN
    DECLARE sum_balance_due_var DECIMAL(9, 2);

    SELECT SUM(invoice_total - payment_total - credit_total)
    INTO sum_balance_due_var 
    FROM invoices i
    JOIN vendors v ON v.vendor_id = i.vendor_id
    WHERE i.vendor_id = vendor_id_var
    GROUP BY i.vendor_id, vendor_name;

    IF sum_balance_due_var > 0 THEN
        SELECT CONCAT('Balance due: $', sum_balance_due_var) AS message;
    ELSE
        SELECT 'Balance paid in full' AS message;
    END IF;
END//
DELIMITER ;

---

Vendors table:

CREATE TABLE vendors (
    vendor_id INT(11) NOT NULL AUTO_INCREMENT,
    vendor_name VARCHAR(50) NOT NULL,
   -- other columns
)

Invoices table:

CREATE TABLE invoices (
    invoice_id INT(11) NOT NULL AUTO_INCREMENT,
    vendor_id INT(11) NOT NULL,
    invoice_total DECIMAL(9,2) NOT NULL,
    payment_total DECIMAL(9,2) NOT NULL DEFAULT '0.00',
    credit_total DECIMAL(9,2) NOT NULL DEFAULT '0.00',
    -- other columns
)

Some sample data:

INSERT INTO vendors (vendor_id, vendor_name) VALUES (34, 'IBM');
INSERT INTO vendors (vendor_id, vendor_name) VALUES (37, 'Blue Cross');
INSERT INTO invoices (invoice_id, vendor_id, invoice_total, payment_total, credit_total) VALUES (19, 34, 116.54, 116.54, 0.00);
INSERT INTO invoices (invoice_id, vendor_id, invoice_total, payment_total, credit_total) VALUES (52, 34,1083.58,1083.58, 0.00);
INSERT INTO invoices (invoice_id, vendor_id, invoice_total, payment_total, credit_total) VALUES (46, 37, 224.00, 224.00, 0.00);
INSERT INTO invoices (invoice_id, vendor_id, invoice_total, payment_total, credit_total) VALUES (50, 37, 116.00, 116.00, 0.00);
INSERT INTO invoices (invoice_id, vendor_id, invoice_total, payment_total, credit_total) VALUES (113, 37,224.00,   0.00, 0.00);

Answer 1

Define it as a FUNCTION:

CREATE FUNCTION test11(IN vendor_id_var INT)
RETURNS text -- or whatever type you want returned
BEGIN
    -- your existing code

    RETURN <something>
END

and remove the word CALL:

SELECT *, test11(vendor_id)
FROM vendors

It's a UDF, just use as if it were a built in.