The Encapsulate As New Stored Procedure refactoring generates a script to create a new stored procedure from selected SQL code. If required, the originating script is modified to replace the encapsulated code with a reference to the new stored procedure.

Parameters are created from any variables found in the selected code, therefore you should ensure that you declare any variables within the SQL code that you are encapsulating.

To encapsulate code as a new stored procedure:

1. Open the source script in a SQL Server Management Studio query editor.

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

   If there are syntax errors in the selected code, SQL Refactor cannot generate the script to create the stored procedure, and an error message will be displayed when you select the feature.

3. On the SQL Refactor menu, click Encapsulate As New Stored Procedure. The Encapsulate As New Stored Procedure dialog box is displayed. 
   
   
   
   

   Any variables that will be used to create parameters are listed in Parameters. SQL Refactor automatically determines whether each variable will be an input parameter or an output parameter. If a warning triangle is displayed next to a parameter, move your mouse pointer to the warning triangle to see details of the problem.The SQL code to create the stored procedure is displayed in Preview.

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 Refactor does not check for duplicate names. If a stored procedure with this owner and name already exists, the script will fail when you run it.

6. Reorder the Parameters as required using the and buttons.
7. Click Next.SQL Refactor displays the source code in the Preview box, and shows how it will be modified to replace your selected code with a reference to the new stored procedure. If required, you can change the Source script option so that the source code is not modified.
   
   
8. Click View Script.
   The Encapsulate As New Stored Procedure dialog box is closed, and the SQL script to create the stored procedure is displayed in a new query editor.In the source code, SQL Refactor removes the selected code and replaces it with a reference to the new stored procedure (unless you chose not to do this).
9. If necessary, edit the generated stored procedure creation script, and run the script to create the new stored procedure.
10. If you are happy with the changes to the source code, save the changes.