Promoting Procedures to Custom Functions
You can use a procedure with scalar output (that is, a procedure that returns a single value; it can have zero, one, or multiple inputs) to directly manipulate data as part of a SELECT statement, or as a criterion in a condition. (See
Including a Function in a SELECT or WHERE Clause.) To use a procedure in this way, you promote it to a custom function.
Note: To promote a saved procedure is to add it to the drop-down list of custom functions in the Studio user interface, using the procedure described below.
An administrator can promote any SQL script or transformation using the Administration > Custom Functions menu option in Studio.
Note: A custom function cannot be rebound.
To promote a procedure to a custom function
1. Log in as the administrator, or have the administrator perform these steps for you.
2. Locate and open a SQL script. Make sure it has exactly one scalar output.
3. In Studio, use the Administration > Custom Functions menu.
If the number of candidate SQL scripts is large, it might take Studio a while to prepare the list.
4. If you want to narrow the list, type a string in the Find field and click Search.
To view the full list again after a search, clear the string from the Find field and click Search again.
5. Select the procedure to promote to a custom function.
6. Click OK.
This function will now be available for a view as a custom function.