Stored Procedures are compiled pieces of database engine specific SQL code that can run on the server - thus without the network traffic involved in pulling data to a client and processing it there.
The Procedure Editor offers the possibility to create, alter and drop stored procedures. If you add or drop a procedure, the Stored Procedures node in the Database Navigator tree-view will display the change as well by adding or deleting the procedure from the list.
Below is an example of the Stored Procedure Editor for Firebird:
A Firebird Stored Procedure Editor
The "{..}" button will turn the current line or text selection in the source editor box into a comment, or remove the comment from the current line.
See also: Object Template Editor
Tip!
Dragging (a) column(s) from the Database Navigator or Describe Companion into the code editor will allow you to auto-create variables with the same datatype as the column(s) or insert the column name as simple text. The variables get names assigned according to the Name Template Code Variable.
Tip!
You can also drag/drop column(s) to the parameter grids and parameters with the same name and data type as the columns will be created.
Testing the procedure
You can execute the procedure by using the green Run Stored Procedure button. You will have to commit/rollback the transaction after execution - this can be done by using the SQL Editor button on the Procedure Editor. This will open a simplified SQL Editor that uses the same transaction as the Procedure Editor. This also a convenient way to test your procedure results - use this SQL Editor to browse table data when testing.
If there are input parameters, a dialog with pop up that allows you to enter values for these parameters.
After execution, the Data/Results tab will be shown with the output.
Load from/Save to file
The Load From File and Save To File buttons can be used to load previously saved procedure code or save the current procedure to a file. These files aren't straight DDL script files, but a custom format. Saving a procedure to a file can be useful if you cannot compile it yet, but need a way to store your code. You can continue working on the procedure another time.
Data/Results tab
This tab shows the results of the procedure, this may be a result set or output parameters.
Plan Analysis
Database Workbench will parse your procedure to find DML statements - each of these statements will be analyzed and shown in the Plan Analysis tab.
The Plan Analysis tab is only supported for InterBase and Firebird.
The Plan Analysis tab in the Procedure Editor
This tab can help you analyzing the performance of your procedure code.