|
Stored Functions 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 Function Editor offers the possibility to create, alter and drop stored functions. If you add or drop a function, the Stored Functions node in the Database Navigator tree-view will display the change as well by adding or deleting the function from the list.
Below is an example of the Stored Function Editor for Microsoft SQL Server:
A MS SQL Stored Function 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.
Testing the function
You can execute the function by using the green Run Stored Function button. You will have to commit/rollback the transaction after execution - this can be done by using the SQL Editor button on the Function Editor. This will open a simplified SQL Editor that uses the same transaction as the Function Editor. This also a convenient way to test your function results - use this SQL Editor to browse table data when testing.
If there are input parameters, a dialog with popup 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 function code or save the current function to a file. These files aren't straight DDL script files, but a custom format. Saving a function 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 function another time.
Data/Results tab
This tab shows the results of the function, this may be a resultset or output parameters.