With SQL Prompt, you can create a new stored procedure from any block of valid SQL. To encapsulate SQL as a new stored procedure:

1. Open the SQL script you want to encapsulate in a query window.

2. Select the section of the script you want to use for the new stored procedure.

   • If there are variables in the script, SQL Prompt will automatically create parameters from them. When you select the SQL to encapsulate, make sure you don't include the DECLARE statements.
   • If there are syntax errors in the SQL you select, SQL Prompt can't create a new stored procedure.
3. On the SQL Prompt menu, click Encapsulate As New Stored Procedure.
   The Encapsulate As New Stored Procedure wizard is displayed:
   
4. In the Owner box, type the name of the owner for the new stored procedure.

5. In the Name box, type a name for the new stored procedure.

   SQL Prompt doesn't 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.

6. 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.
7. Click Next.
   The second page of the wizard is displayed:
   
8. If you don't want the new stored procedure to replace the SQL you selected, under Source SQL, select Do not replace.
   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.
9. Click View Script.
   The SQL script to create the stored procedure is displayed in a new query window.
   If you selected Replace with reference to new stored procedure, SQL Prompt replaces the SQL you selected with a reference to the new stored procedure.
10. Edit the stored procedure creation script if required, and run it.
    The new stored procedure is created.