How to create and use T-SQL code snippets

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:

  1. 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:

  2. 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.

Snippet code:

/****** 
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.

Snippet code:

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.

Snippet code:

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:

New trigger dialog

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.

Snippet code:

$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:

  1. 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:

  2. Select the snippet from the prompted drop-down list:

  3. Start typing a snippets name and click on the snippet in the hint–list:

March 4, 2015