This article describes the Encapsulate code as feature in ApexSQL Refactor.
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
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.
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:
- Select the code section in the query window
1.1 Under the ApexSQL Refactor menu, select the Encapsulate code as and select a method from the list:
- 1.2 Use the “Ctrl + Shift +Alt +Y” shortcut for a new store procedure, the “Ctrl + Shift +Alt +L” 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.
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 Open 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: