Calling stored procedures

This section describes how to create and call stored procedures, and how to process the results from an ODBC application.

For a full description of stored procedures and triggers, see the ASE Reference Manual.

There are two types of procedures: those that return result sets, and those that do not. You can use SQLNumResultCols to tell the difference: The number of result columns is zero if the procedure does not return a result set. If there is a result set, you can fetch the values using SQLFetch or SQLFetchScroll just like any other cursor.

Pass parameters to procedures using parameter markers (question marks). Use SQLBindParameter to assign a storage area for each parameter marker, whether it is an INPUT, OUTPUT, or INOUT parameter.

The advanced sample illustrates a stored procedure that returns an output parameter and a return value, and another stored procedure that returns multiple result sets. Error checking has been omitted to make the example easier to read.

/*Example 1: How to call a stored procedure and use input and output parameters*/
SQLBindParameter(stmt, 1, SQL_PARAM_OUTPUT, SQL_C_SLONG,
                 SQL_INTEGER , 0, 0, &retVal, 0,
                 SQL_NULL_HANDLE);
SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, 
                 SQL_CHAR , 4, 0, stor_id, sizeof(stor_id) ,
                 SQL_NULL_HANDLE);
SQLBindParameter(stmt, 3, SQL_PARAM_OUTPUT, SQL_C_CHAR, 
                 SQL_VARCHAR , 20, 0, ord_num, sizeof(ord_num) ,
                 &ordnumLen);
SQLBindParameter(stmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR,
                 SQL_VARCHAR , 40, 0, date, sizeof(date) ,
                 &dateLen);

SQLExecDirect( stmt, "{ ? = call sp_selectsales(?,?,?) }", SQL_NTS) ;

/*At this point retVal contains the return value as returned from 
the stored procedure and the ord_num contains the order number 
as returned from the stored procedure
*/


/*
Example 2: How to call stored procedures returning multiple result sets*/


SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
   SQL_CHAR , 4, 0, stor_id, sizeof(stor_id) ,
   SQL_NULL_HANDLE);

SQLExecDirect(stmt, "{ call sp_multipleresults(?) }", SQL_NTS);

SQLBindCol( stmt, 1, SQL_C_CHAR, dbValue, sizeof(dbValue),
   &dbValueLen);

SQLSMALLINT count = 1;

while(retcode == SQL_SUCCESS
   || retcode == SQL_SUCCESS_WITH_INFO)
{
   retcode = SQLFetch( stmt );
   if (retcode == SQL_NO_DATA)
   {
     /*
     -- End of first result set --
     */
     if(count == 1)
     {
        retcode = SQLMoreResults(stmt);
        count ++;
     }
     /*
      At this point dbValue contains the value in the current row of the             result
     */
   }
}