How to create and use T-SQL code snippets

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:

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

   

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, select the code, right click, and choose the New snippet command from the context menu:

   

   By choosing the New snippet 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:

/****** 
Object:  StoredProcedure [dbo].[SQLSnippet]    
Script Date: %DATE&MM/dd/yyyy%
Script Time: %TIME&hh:mm:ss%
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: 09/26/2017
Script Time: 02:07:34
Server Name: WIN10\SQLEXPRESS
Login: WIN10\Zivko
Database Name: AdventureWorks2014
Database User: Zivko
User: Zivko
Host: WIN10
******/
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(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)%
CREATE TRIGGER %TriggerName%
 ON /* table name */
 AFTER INSERT, UPDATE
 AS
 BEGIN
/* body */
 SET NOCOUNT ON
 END

The text entered in the prompted window:

Code in the query window after inserting a variable in the prompted window:

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:

   

   Select the snippet from the prompted drop-down list:

   

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