Encapsulating SQL as a new stored procedure - SQL Prompt

SQL Prompt allows you to create a new stored procedure from any block of valid SQL.

Note: Encapsulating SQL as a new stored procedure is available only in SQL Prompt Pro Edition.

To encapsulate SQL as a new stored procedure:

Open the SQL script you want to encapsulate in a query window.
Select the section of the script that you want to use for the new stored procedure.
Parameters are created from variables in the script, therefore it is recommended that you declare any variables in the SQL you select.

Note that if there are syntax errors in the SQL you select, SQL Prompt cannot create a new stored procedure.
On the SQL Prompt menu, click Encapsulate As New Stored Procedure.
The Encapsulate As New Stored Procedure wizard is displayed:
In the Owner box, type the name of the owner for the new stored procedure.
In the Name box, type a name for the new stored procedure.
Note that SQL Prompt does not check for duplicate names. If a stored procedure with this owner and name already exists, the SQL script to create the new stored procedure will fail when you run it.

Variables that will be used to create parameters are listed in the Parameters box. SQL Prompt automatically determines whether each variable will be an input parameter or an output parameter.
In the Parameters box, reorder the parameters as required using the and buttons.
If a warning triangle is displayed by a parameter, move your mouse over the warning triangle to see details of the warning.

The SQL script to create the new stored procedure is displayed in the Preview box.
Click Next.
The second page of the wizard is displayed:
If you do not want the new stored procedure to replace the SQL you selected, under Source script, select Do not modify.
The Preview box now displays the SQL you selected, and shows how it will be modified if you chose to replace it with a reference to the new stored procedure.
Click View Script.
The SQL script to create the stored procedure is displayed in a new query window.

If you selected Modify to reference the new stored procedure, SQL Prompt replaces the SQL you selected with a reference to the new stored procedure.
Edit the stored procedure creation script if required, and run it.
The new stored procedure is created.