In this article, we will describe the snippets feature in ApexSQL Complete and give examples of using snippets macro variables.
ApexSQL Complete is an SSMS and VS code completion add-in that helps you 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 you to insert custom made T-SQL code snippets into your SQL scripts. You can use snippets 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 ApexSQL Complete Options dialog:
Under the Snippets tab, click the Add button. This will open the Create a new snippet dialog:
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, right click, and choose the New snippet command from the context menu:
By choosing the New snippet command, the Create a new snippet dialog will open, and code will be automatically added in the Code section of the dialog:
If you want to edit an existing snippet, in the ApexSQL Complete Options dialog click the Edit button:
This command will open the Edit snippet dialog:
Examples of using ApexSQL Complete snippet macro variables
Snippet managing dialogs contain a list of macro variables to implement code snippets for the dynamic code generation:
$DATE$, $TIME$, $SERVER$, $LOGIN$, $DB$, $USER$, $OSUSER$, $MACHINE$, $OBJECT$, $SELECTION$, $CARET$ and $PROMPT$ macro variables
Using $DATE$, $TIME$, $SERVER$, $LOGIN$, $DB$, $USER$, $OSUSER$, $MACHINE$, $OBJECT$, $SELECTION$, $CARET$, $PROMPT$ 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, we can create new snippets 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.
/****** Object: StoredProcedure [dbo].[SQLSnippet] Script Date: $DATE$ Script Time: $TIME$ Server Name: $SERVER$ Login: $LOGIN$ Database Name: $DB$ Database User: $USER$ User: $OSUSER$ Host: $MACHINE$ ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[SQLSnippet] AS IF year( getdate()) = '2014' PRINT 'Live long and prosper' GO
Code in the query window after inserting the snippet:
/****** Object: StoredProcedure [dbo].[SQLSnippet] Script Date: 24-Feb-14 Script Time: 10:07:35 PM Server Name: MCA\LENOVO Login: MCA\MB Database Name: AdventureWorks2012 Database User: Spock User: MB Host: MCA ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[SQLSnippet] AS IF year( getdate()) = '2014' PRINT 'Live long and prosper' GO
The $OBJECT$ macro variable
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.
ALTER TABLE $OBJECT$ ADD
Code in the query window after selecting an object from the hint-list:
USE [AdventureWorks2014] ALTER TABLE 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.
CREATE PROCEDURE AS BEGIN $SELECTION$ END
Code selected in the query window:
CREATE PROCEDURE AS BEGIN IF year( getdate()) = '2014' PRINT 'Live long and prosper' END
The $PROMPT(varName, text, default, title)$ macro
The $PROMPT(varName, text, default, title)$ macro creates a dialog with a custom prompt where the varName, text, defaut, and title stand for:
varName – a variable or in which the result is saved
text – custom prompted text with instruction for a user
title – dialog title
For example, we will create a code snippet for a new trigger using the $PROMPT(…)$ macro variable:
When this snippet is called, the user will be prompted with a dialog to enter a name of a trigger:
When the user enters a name for a trigger and clicks the Ok button the inserted variable name will be saved and used in the place in a snippet where it’s defined.
$PROMPT(TriggerName, New trigger, Enter a new trigger name, New trigger dialog)$ CREATE TRIGGER $TriggerName$ ON /* table name */ AFTER INSERT, UPDATE AS BEGIN /* body */ SET NOCOUNT ON END
The text entered in the prompted dialog:
Code in the query window after inserting a variable in the prompted dialog:
CREATE TRIGGER TriggerOnInsert ON /* table name */ AFTER INSERT, UPDATE AS BEGIN /* body */ SET NOCOUNT ON END
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:
Start typing a snippets name and click on the snippet in the hint–list:
Select the snippet from the prompted drop-down list:
March 4, 2015