Runs a system procedure or a user-defined stored procedure.
exec sql [at connection_name] exec [[:status_var =]status_value] procedure_name [([[@parameter_name =]param_value [out[put]]],...)] [into :hostvar_1 [:indicator_1] [, hostvar_n [indicator_n,…]]] [with recompile];
Do not confuse the exec statement
with the Embedded SQL execute statement;
they are not related. The Embedded SQL exec statement is, however,
the equivalent of the Transact-SQL execute statement.
A host variable to receive the return status of the stored procedure.
The value of the stored procedure return status variable status_var.
The name of the stored procedure to be executed.
The name(s) of the stored procedure’s parameter(s).
A host variable or literal value.
Indicates that the stored procedure returns a parameter value. The matching parameter in the stored procedure must also have been created using the output keyword.
Causes row data returned from the stored procedure to be stored in the specified host variables (hostvar_1 through hostvar_n). Each host variable can have an indicator variable.
Causes Adaptive Server Enterprise to create a new query plan for this stored procedure each time the procedure executes.
exec sql begin declare section;
char titleid[10];
int total_discounts;
short retstat;
exec sql end declare section exec;
exec sql create procedure get_sum_discounts
(@titleid tid, @discount int output) as
begin
select @discount = sum( qty * discount)
from salesdetail
where title_id = @titleid
end;
printf(“title id: “);
gets(titleid);
exec sql exec
:retstat = get_sum_discount :titleid,
:total_discounts out;
printf("total discounts for title_id %s were
%s\n", titleid, total_discounts);
exec sql begin declare section;
CS_INT status;
CS_CHAR city(30);
CS_INT result;
exec sql end declare section;
LONG SQLCODE;
input "City", city ;
exec sql exec countcity :city, :result out;
if (SQLCODE = 0)
print city + " occurs " + result + "
times." ;
EXEC SQL BEGIN DECLARE SECTION;
/* storage for login name and password */
CS_CHAR username[30], password[30];
CS_CHAR pub_id[4][5], pub_name[4][40], stmt[100] ;
CS_CHAR city[4][15], state[4][3];
CS_INT ret_status;
EXEC SQL END DECLARE SECTION ;
...
EXEC SQL set chained off;
strcpy(stmt,"create proc get_publishers as select * from publishers
return ");
EXEC SQL EXECUTE IMMEDIATE :stmt;
EXEC SQL EXEC :ret_status = get_publishers INTO
:pub_id,
:pub_name,
:city,
:state;
printf("Pub Id Publisher Name City State \n");
printf("\n----- ----------------------- ------------ -----\n");
for ( i = 0 ; i < sqlca.sqlerrd[2] ; i++ )
{
printf("%-8s", pub_id[i]) ;
printf("%-25s", pub_name[i]) ;
printf("%-12s", city[i]) ;
printf("%-6s\n", state[i]) ;
}
printf("\n(%d rows affected, return status = %d)\n", sqlca.sqlerrd[2],
ret_status);
...
}
Only one select statement can return rows to the client application.
If the stored procedure contains select statements that can return row data, you must use one of two methods to store the data. You can either use the into clause of the exec statement or declare a cursor for the procedure. If you use the into clause, the stored procedure must not return more than one row of data, unless the host variables that you specify are arrays.
The value param_value can be a host variable or literal value. If you use the output keyword, param_value must be a host variable.
You can specify the output keyword for parameter_name only if that keyword was also used for the corresponding parameter of the create procedure statement that created procedure_name.
The Embedded SQL exec statement works much like the Transact-SQL execute statement.
declare cursor (stored procedure), select
