Wednesday, April 8, 2009

Getting Input Variables with SQL Stored Procedures

Advantage uses two system tables to handle the input and output variables. Each input variable is stored as a field in the __input table which can be accessed within your SQL script.

I prefer opening the __input table as a cursor and then using dot notation to access the various fields. If you need to manipulate the input values within your stored procedure then you may want to assign the input values to local variables. The code below demonstrates my preferred method which is using a cursor for the input table.

DECLARE @cInput CURSOR as SELECT * FROM __input; 
DECLARE @sCustName STRING;
 
// Open the __input table
OPEN @cInput;
// Fetch the first row, the __input table only contains a single  row
FETCH @cInput; 
 
// Values can be used via dot notation
@sCustName = SELECT LastName + ", " + FirstName FROM Customer
             WHERE CustID = @cInput.ID;
 
// Close the input table
CLOSE @cInput;

There are two ways to assign the input values to local variables. The first is to use a cursor like the example above and the second is to use an SQL statement on the __input table. The example below demonstrates these two methods.

DECLARE @var STRING;
 
// Using the cursor as declared above
@var = @cInput.field;
 
// Selecting the value from the __input table directly
// Note: the SET keyword is optional
SET @var = SELECT field FROM __input;

The performance of each of these methods is about the same so it is more a matter of personal preference. More information about using stored procedures with Advantage is available in the help file.

No comments: