KB Article 16038
2019/03/07

Close
X
Contact Tech Support

 Email Tech Support
 (250) 655-1766
 [7:30 - 5:00 PST]
Your Meeting ID will be Provided by a Rep.
Tech Support
Tech Support Home
Software Updates
Knowledge Base Search
Recent KB Articles
Product FAQs
Code Samples
Documentation
Tech Blog
System Requirements
Installation Instructions
Support Testimonials
Contact us to...
Create a Ticket
Request a Free Demo
Suggest a New Feature
Submit Feedback
Upload a Large File
Other Services
Software Training
Professional Services

Calling SQL stored procedures with output parameters in Presto

Product: Presto Type: How to

Sometimes we need to call a stored procedure using SQL. While Presto supports this it doesn't support output parameters. The good news is that we can use these by writing a stored function that acts as a wrapper for the stored procedure and returns the value of the output parameter.

For this example, we're going to use the following stored procedure:

CREATE OR REPLACE PROCEDURE MYLIB/TESTPROC (IN TESTVAR INT, OUT NEWVAL INT)
LANGUAGE SQL MODIFIES SQL DATA
SET NEWVAL = TESTVAR + 1

The second paramater NEWVAL is an output parameter so Presto won't be able to directly call it.  To call this we can create the following wrapper function:

CREATE OR REPLACE FUNCTION MYLIB/TESTFUNC (TESTVAR INT)
RETURNS INT
LANGUAGE SQL MODIFIES SQL DATA
BEGIN
    DECLARE NEWVAL INT;
    CALL MYLIB/TESTPROC(TESTVAR, NEWVAL);
    RETURN NEWVAL;
END

Now we can use the wrapper function to call the stored procedure and get the value of the output parameter NEWVAL with the following SQL statement:

SELECT MYLIB/TESTFUNC(1) from SYSIBM.SYSDUMMY1

Rate This Article

Did this example help you to achieve your goal?
 Yes  No  Don't Know

Enter additional comments below.   If you want to hear back from us, include your contact information.

Email Address:
Comments:
Please enable JavaScript in order to rate this page.