How to encapsulate code as a new stored procedure, a function, or a view

Applies to
ApexSQL Refactor

Summary
This article describes the Encapsulate code as feature in ApexSQL Refactor.

Description
Encapsulating T-SQL code as a database object makes that code easier to reuse. The Encapsulate code as feature offers creating a new stored procedure, a view, a scalar inline function, or a table inline function from any valid T-SQL code block.

Encapsulate code as a stored procedure

Encapsulating code as a stored procedure has many benefits such as:

• Reduced server/client network traffic: Because the commands in a procedure are executed as a single batch it reduces the server/client network traffic since only the call to execute the procedure is sent across the network.

• Security: Using procedures eliminates the need for granting permissions at the individual object level, preventing malicious users to see object names, insert T-SQL statements, or search for sensitive data.

• Code reusability: Any operation on a database that is repeatable qualifies as a candidate for encapsulation as a stored procedure to eliminate rewriting of the same code.

• Performance: A stored procedures execution plan is created when a procedure is executed for the first time and reused in all following executions. Eliminating the need to create a new plan on every execution process a stored procedure takes less time.

Encapsulate code as a scalar inline function

A scalar inline function doesn’t have a function body, the result of a single inline function is a value defined in the RETURNS clause.

Quick tip: The return data type in the RETURNS clause cannot be text, ntext, image, cursor, or timestamp

Encapsulate code as a table inline function

Table-valued user defined functions (UDFs) allow encapsulating T-SQL logic that returns a result set that can be used in any case where a real table is allowed.

Quick tip: An inline table-valued UDFs can include only one SELECT statement

Encapsulate code as a view

Encapsulating code to access data through views reduces the direct coupling to a table, and enables restricting access to the tables providing end users with read access but not update privileges. It also reduces SQL duplication for complex SQL queries that may have duplicated parts. In this case, encapsulating SQL code as a view, extracts out duplicated code.

There are two ways to encapsulate SQL code in ApexSQL Refactor:

1. Select the code section in the query window

   1. 1.1 Under the ApexSQL Refactor menu, select the Encapsulate code as and select a method from the list:

      

   2. 1.2 Use the Ctrl + Shift +Alt +Y shortcut for a new store procedure, the Ctrl + Shift +H shortcut for a new scalar inline function, the Ctrl + Shift +Alt +X for a new table inline function, and the Ctrl + Shift +Alt +V shortcut to encapsulate code selection as a view.

Click the Preview button to create the refactoring script.

Quick tip: When encapsulating code as a stored procedure or a table inline function ApexSQL Refactor will automatically determine from selected code whether a variable will be an input or an output parameter

Note: If the code selection is not valid or cannot be encapsulated ApexSQL Refactor will throw the warning:

Clicking the Create script button will open the generated script in the query editor. To execute the script click the Encapsulate button.

If the Modify source script to reference this new object option is checked it the selected code will be replaced with the new object reference after the script is executed. For example, selected code will be replaced with a reference to a new stored procedure:

EXECUTE dbo.usp_EmployeeInfo 
@RateChangeDate, 
@HireDate, 
@BusinessEntityID, 
@JobTitle, 
@PayFrequency, 
@CurrentFlag, 
@Rate;

Useful resources:
SQL Stored Procedures
Understanding User-defined Functions
SQL Views