Submitting forms on the support site are temporary unavailable for schedule maintenance. If you need immediate assistance please contact technical support. We apologize for the inconvenience.
In this article, the snippets feature in ApexSQL Complete will be described along with examples of using snippets macro variables.
ApexSQL Complete is an SSMS and VS code completion add-in that helps to speed up the process of coding using features such as auto-complete, SQL code snippets, auto-insert statements, auto–replacement and more.
ApexSQL Complete’s Snippets feature provides users to insert custom made T-SQL code snippets into SQL scripts. Snippets can be used to quickly insert often-used SQL statements as well as generate whole procedures and pages of code.
Creating a new snippet
There are two ways to create snippets in ApexSQL Complete – from the ApexSQL menu and from the SSMS query window:
Under the ApexSQL Complete menu, select the Options command to open the Options window:Under the Snippets tab, click the New button. This will open the Create a new snippet window:
Another way to create snippets in ApexSQL Complete is directly from a piece of code in the query window. For example, to make a snippet from a following code:
SELECT*FROM sys.sql_expression_dependencies
Type the code in a query window, select the code, right click, and choose the New snippet command from the context menu:
By choosing the Newsnippet command, the Create a new snippet window will open, and code will be automatically added in the Code section of the window:
To edit an existing snippet, in the window click the Edit button under the Snippets tab of the Options window:
This command will open the Edit snippet window:
Examples of using ApexSQL Complete snippet macro variables
Snippet managing window contains a list of macro variables to implement code snippets for the dynamic code generation:
%DATE&MM/dd/yyyy%, %TIME&hh:mm:ss%, %SERVER%, %LOGIN%, %DB%, %USER%, %OSUSER%, %MACHINE%, %OBJECT%, %SELECTION%, %CARET% and %PROMPT(name, text, default, title)% macro variables
Using %DATE&MM/dd/yyyy%, %TIME&hh:mm:ss%, %SERVER%, %LOGIN%, %DB%, %USER%, %OSUSER%, %MACHINE%, %OBJECT%, %SELECTION%, %CARET% and %PROMPT(name, text, default, title)% macro variables in the snippet code will return the current date, time, the connection server name, a connection login, a connection database, a connection user name, a local OS username, a local host username, a local machine name, current object, selection, cursor position, and interactive user prompt respectively.
For example, new snippets can be created in ApexSQL Complete, using those variables:
When called, the snippet will give the current time, the connection server name, a connection login, a connection database, a connection user name, a local OS username, and a local host username.
Snippet code:
Creating snippets in ApexSQL Complete with the %OBJECT% variable will replace the macro variable with an object selected from the hint-list. For example, creating an Alter table snippet as following:
When the AddColumn snippet is called it will prompt the hint-list of available tables in the current database:
When an object is selected from the hint list the %OBJECT% macro variable will be replaced with the name of an object selected from the hint-list.
Snippet code:
ALTERTABLE%OBJECT%ADD
Code in the query window after selecting an object from the hint-list:
USE AdventureWorks2014
ALTERTABLE BusinessEntity
ADD
The %SELECTION% macro variable
A snippet created using the %SELECTION% variable will replace the variable with the name of the highlighted selection from the query window. For example, having the following code snippet:
When the snippet is inserted it will wrap the selection from a query window with a snippet code and replace the %SELECTION% variable with a selection from the query window.
Snippet code:
CREATEPROCEDUREASBEGIN%SELECTION%END
Code selected in the query window:
CREATEPROCEDUREASBEGINIFyear( getdate()) ='2014'PRINT'Live long and prosper'END
The %PROMPT(name, text, default, title)% macro
The %PROMPT(name, text, default, title)% macro creates a window with a custom prompt where the name, text, default, and title stand for:
name – a variable or in which the result is saved
text – custom prompted text with instruction for a user
title – window title
For example, let's create a code snippet for a new trigger using the %PROMPT (...)% macro variable:
When this snippet is called, the prompt window will be shown to enter a name of a trigger:
When a name for a trigger is entered and OK button is clicked, the inserted variable name will be saved and used in the place in a snippet where it’s defined.
Snippet code:
%PROMPT(TriggerName,New trigger,Enter a new trigger name,New trigger window)%CREATETRIGGER%TriggerName%ON/* table name */AFTERINSERT,UPDATEASBEGIN/* body */SETNOCOUNTONEND
The text entered in the prompted window:
Code in the query window after inserting a variable in the prompted window:
CREATETRIGGER TriggerOnInsert
ON/* table name */AFTERINSERT,UPDATEASBEGIN/* body */SETNOCOUNTONEND
Using created snippets in ApexSQL Complete
ApexSQL Complete offers two ways to insert a created T-SQL code snippet:
While typing depending of the type of the snippet select the portion typed in the query window or just right-click and choose the Insert snippet command:
Select the snippet from the prompted drop-down list:
Start typing a snippets name and click on the snippet in the hint–list: