Passing Parameters to Procedures in Sybase SQL Anywhere

Handling Sybase Events with Triggers and Procedures Using SQL – Part 7

Forward: In this part of the series, we see how to pass parameters to procedures in Sybase SQL Anywhere.

By: Chrysanthus Date Published: 28 Aug 2012

Introduction

This is part 7 of my series, Handling Sybase Events with Triggers and Stored Procedures Using SQL. This series is the fifth division of my larger series, Database. You must have read all the different parts of the big series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, we see how to pass parameters to procedures in Sybase SQL Anywhere.

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.

Meaning of Parameter Passing
Imagine that you have a trigger that calls a procedure to carry out certain task for the trigger. It should be possible for the trigger to send data to the procedure in the calling statement, so that the procedure can use the data to carry out the task the trigger wants. This process is called Passing Parameters to the Procedure.

Consider the following procedure:

CREATE Procedure sampleProcedure
(
IN par1 INTEGER,
IN par2 INTEGER
)
BEGIN
-- SQL statements
END;

We this procedure, we say the parentheses hold the declarations of the parameters. IN means that the parameters represent values that would be received from outside the procedure, when it is called.

Compound Statement Variables
A compound statement such as that of a trigger can call a procedure, sending data to the procedure. The data passed can be the variables or some expression of the compound statement. The following code segment illustrates this.

BEGIN
DECLARE var1 INTEGER = 25;
DECLARE var2 INTEGER = 30;

CALL sampleProcedure (var1, var2);

-- SQL statements
END;

The CALL statement in the compound statement calls the procedure sending 25 and 30 with var1 and var2. The procedure will receive these numbers as par1 and par2. Note how the declaration has been done in the compound statement and note how the CALLING statement has been typed (the name of the procedure is included).

Note that the data type for the declaration in the compound statement must be the same as the corresponding data type for the procedure parameter.

Another way for the Calling statement to send data to the procedure is to place the values directly into the procedure parameters. So, the above Calling statement can be written as:

CALL sampleProcedure (par1=var1, par2=var2);

Default Values of Stored Procedure Parameters
It is not all the time that you would want to call a procedure and send data to the procedure. A procedure parameter may have a default value, which you would allow the procedure to use. Consider the following procedure:

CREATE Procedure sampleProcedure
(
    IN par1 INTEGER,
    IN par2 INTEGER DEFAULT 30
)
BEGIN
-- SQL statements
END;

This is the same procedure as the one before but its second parameter has a default value. The compound statement we saw above would now be:

BEGIN
    DECLARE var1 INTEGER = 25;

CALL sampleProcedure (var1);

-- SQL statements
END;

Here we need only one declaration. In the Calling statement, only the first expression (value) is passed with var1. There is no need to pass a second one since the procedure already has it.

That is it for this part of the series. Let us stop here and continue in the next part. I hope you are appreciating everything.

Chrys

Broad Network

Related Articles

Passing Parameters to Procedures in Sybase SQL Anywhere

Handling Sybase Events with Triggers and Procedures Using SQL – Part 7

Introduction

Related Courses

Comments