Creating a Stored Procedure
Go Up to Using a Stored Procedure
Ordinarily, stored procedures are created when the application and its database is created, using tools supplied by InterBase. However, it is possible to create stored procedures at runtime. For more information, see “Creating procedures” in the InterBase Data Definition Guide.
A stored procedure can be created by an application at runtime using a SQL statement issued from a TIBQuery component, typically with a CREATE PROCEDURE statement. If parameters are used in the stored procedure, set the ParamCheck property of the TIBQuery to False. This prevents the TIBQuery from mistaking the parameter in the new stored procedure from a parameter for the TIBQuery itself.
You can also use the SQL Explorer to examine, edit, and create stored procedures on the server.
After the SQL property has been populated with the statement to create the stored procedure, execute it by invoking the ExecSQL method.
with IBQuery1 do begin
ParamCheck := False;
with SQL do begin
Clear;
Add(‘CREATE PROCEDURE GET_MAX_EMP_NAME’);
Add(‘RETURNS (Max_Name CHAR(15))’);
Add(‘AS’);
Add(‘BEGIN’);
Add(‘ SELECT MAX(LAST_NAME)’);
Add(‘ FROM EMPLOYEE’);
Add(‘ INTO :Max_Name;’);
Add(‘ SUSPEND;’);
Add(‘END’);
end;
ExecSQL;
end;