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 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.
/****** 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.
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(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.
%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:
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:
March 4, 2015