Returning Results as Procedure Parameters in Sybase SQL Anywhere
Handling Sybase Events with Triggers and Procedures Using SQL – Part 8
Forward: In this part of the series, we see how procedures can return results as parameters in Sybase SQL Anywhere 12.
By: Chrysanthus Date Published: 28 Aug 2012
Introduction
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Why Return Values
Imagine that you have a trigger that calls a procedure to carry out certain task for the trigger. When the procedure finish carrying out the task, the trigger may need to know the results. In other words, the trigger may need to know the values the procedure arrived at. The procedure is written in such a way that it will place its resulting values in its parameters that have the OUT parameter-mode. The trigger or calling environment will collect the result from there. The values placed by the procedure in its OUT parameters are called returned values.
This is the opposite of what happens with procedure parameters of the IN parameter-mode. The trigger or calling environment places values in the procedure’s IN parameters indirectly or directly. From there the statements in the procedure can use (read) the IN parameters. We saw this in the previous part of the series.
Placing Values in the OUT Parameters
There is a SQL statement called the SET statement. You can use it to place a value in the OUT parameter inside the procedure. You can also use the SQL SELECT statement to do this as I explain below.
The following illustrates how a procedure can place value in the OUT parameter using the SET statement.
CREATE PROCEDURE sampleProcedure
(
OUT parA INTEGER,
IN par1 INTEGER DEFAULT 26
)
BEGIN
- - other SQL statements
SET parA = par1;
END;
In the SET statement, the value of par1 is assigned to parA and so the value of par1 is automatically copied to parA. In the SET statement, you type the OUT parameter first before you type the IN parameter, with the assignment operator between them. In this case parA is an OUT parameter. Note: when working with parameters inside the procedure compound statement you do not use the IN and OUT reserved words. The SET statement is a SQL statement, so it ends with a semicolon.
Now that value has been placed in an OUT parameter, the Calling environment can read it; I explain how this is done below.
In one of the previous parts of the big series, I gave a simplified syntax of the SELECT statement as:
SELECT select-list
FROM from-expression
JOIN table-columns with ON operators
WHERE search-condition
GROUP BY group-by-expression
HAVING search condition
ORDER BY expression
There is an INTO clause in the complete syntax. The INTO clause fits as follows:
SELECT select-list
[ INTO { hostvar-list | variable-list | table-name } ]
FROM from-expression
JOIN table-columns with ON operators
WHERE search-condition
GROUP BY group-by-expression
HAVING search condition
ORDER BY expression
What interest us here from the INTO clause, is the variable-list. For simplicity we shall work in this tutorial with only one variable. The number of items in the variable-list should be equal to the number of items in the select-list (of the SELECT clause). Since we are dealing with only one variable, then there should be only one item in the select-list. The following procedure illustrates the use of the SELECT statement for the OUT parameter:
CREATE PROCEDURE sampleProcedure
(
OUT parA INTEGER,
IN par1 INTEGER DEFAULT 26
)
BEGIN
- - other SQL statements
SELECT column-name
INTO parA
FROM table-name
WHERE columnID = value;
END;
parA is declared as an OUT integer in the parameter section of the procedure. parA is used in the INTO clause of the SELECT statement. It is understood that the SELECT statement will retrieve one integer value from the table. That is all you have to do for the returned value to be in the OUT parameter.
Consider the following compound statement of a trigger:
BEGIN
DECLARE var1 INTEGER = 25;
-- other statements
SELECT var1;
END;
There is a statement that declares an integer variable in the compound statement. The SELECT statement below it selects the declared variable, and not some column values from some table. Whenever the SELECT statement selects something, it displays the result. In the Interactive SQL window, it displays the result in the Results pane. This variable is declared with the integer, 25 assigned to it. So the SELECT statement will display the value, 25. That is how you display values of variables.
Reading the OUT Procedure Parameter Value
How do you read the OUT Procedure parameter value from outside the procedure? There has to be a declared variable (unassigned) outside the procedure. The type of the variable declared has to be the same as the type of the OUT parameter in the procedure.
A procedure is normally stored and you have to call it. In calling the procedure you have to use the variable declared outside the procedure as an expression (parameter) in the Calling statement.
Under that condition, the declared variable outside assumes the value of the OUT parameter in the procedure. Then you can display the value from the variable declared outside the procedure, using the SELECT statement. The following compound statement displays the value of the OUT parameter, parA of the last procedure above:
BEGIN
DECLARE var1 INTEGER;
CALL sampleProcedure (var1);
SELECT var1;
END;
Note: INTEGER as a data type can be abbreviated to INT.
Remember, whenever you create a procedure, that procedure is saved. You can then call the procedure without seeing the code of the procedure.
This is it for this part of the series. We take a break and continue in the next part.
Chrys
Related Courses
C++ CourseRelational Database and Sybase
Windows User Interface
Computer Programmer – A Jack of all Trade – Poem
NEXT