Stored Procedure outputs

[Former Member]

We want to put our queries into stored procedures for several reasons, security among them.

We are able to execute and get data back from simple queries that return a single column, but we do not know how to return multiple columns as a result set.

We have searched help files and the web to no avail.  Is there some reference that pertains to Advantage Database Server 11 and outputting data from a stored procedure?

[Former Member · Accepted solution]

There are two different ways to achieve this.

The first is defining specific output columns and then filling those columns.  There is a great example in this KB http://devzone.advantagedatabase.com/dz/content.aspx?Key=17&RefNo=090728-2196

The second is using the CURSOR VARYING OUTPUT parameter (note from the help file the limitations - http://devzone.advantagedatabase.com/dz/WebHelp/Advantage11.1/index.html?master_create_procedure.htm)

Here is an example of using the varying output  (not really sure why I had to put two end statement in this, but it complained otherwise)

CREATE PROCEDURE returnSystemTables

   (

      tablename CHAR ( 255 ),

   CURSOR VARYING OUTPUT

   )

BEGIN

IF (UPPER(_tablename) = 'TABLES') THEN

   select * from system.tables;

ELSE IF (UPPER(_tablename) = 'INDEXES') THEN

   select * from system.indexes;

--further ELSE IF....

ELSE --Nothing matching - raise error

   raise Invalid_TableName(99, _tablename);

ENDIF;

END;

END;

[Former Member · Accepted solution]

Thank you Edgar!