SQL snippets

Applies to
ApexSQL Complete

Summary
This article describes the use of built-in snippets in ApexSQL Complete as well as creating and using new snippets.


Description

Built-in snippet library
The Built-in snippets can be accessed from the snippets library. To access the snippets library, from the add-in menu, click the Manage snippets option:

Manage snippets option

This will open the Snippets tab, in the add-in options, showing the list of snippets including SQL code for each snippet:

The Snippets tab

Create a new snippet

New snippets can be added by clicking the New button, which will initiate the New snippet form:

Create a new snippet dialog

Another way to create a snippet is to pick a snippet code directly from the query window. Right clicking the query widow, and choosing the New snippet option will pick up the content of the query (in case the part of code is selected, that specific part will be used as a new snippet):

The New snippet option

Selected part will be automatically inserted in the code section of the New snippet form:

The Code section of the Create a new snippet form

Using the built in snippets

To insert a snippet, right-click while typing in the query and choose the Insert snippet command:

Figure illustrating choosing the ApexSQL Complete Insert Snippet command

The hint–list with all available snippets will be shown:

The hint–list dialog shown after clicking on the Insert snippet command in ApexSQL Complete

Another method for inserting snippets is to start typing the snippet name in the query window and choose a built-in snippet from the hint-list:

Dialog showing how to choose a built-in snippet from the hint-list after beginning to type the snippets name

The following are some examples of built-in snippets containing macro variables:

BEGIN/END

The BEGIN/END snippet inserts the BEGIN/END statement and places the cursor at the location of the $CARET$ variable.

Example

BEGIN
        $CARET$
END

Start typing the snippet name and choose the begin snippet from the hint list:

Choosing the begin snippet from the hint list after beginning to type the snippets name

The cursor will be placed between BEGIN and END:

Cursor is placed between BEGIN and END

TRY/CATCH

The TRY/CATCH built-in snippet inserts code for the TRY/CATCH statement, including the ERROR_SEVERITY(),the ERROR_STATE(), the ERROR_PROCEDURE(),the ERROR_LINE(), and the ERROR_MESSAGE() functions:

BEGIN TRY
        $CARET$
END TRY
BEGIN CATCH
        /* 
	SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage
	*/
END CATCH

When inserted, the snippet will place the cursor in the place of the $CARET$ macro variable:

Dialog showing that the snippet will place the cursor in the place of the $CARET$ macro variable

CASE

The CASE snippet inserts the CASE statement structure:

CASE $CARET$
    WHEN  THEN 
    WHEN  THEN 
    ELSE
END

The cursor is placed at the position of the $CARET$ macro variable:

The cursor is placed at the position of the $CARET$ macro variable

COMMENT

ApexSQL Complete has three options for comment snippets. The first and the second option insert a block comment in the query window and places the cursor at the position of the $CARET$ macro variable:

Example option one:

/*
 * $CARET$
 */

After inserting the snippet:

Comment snippets option 1 - placing the cursor at the position of the $CARET$ macro variable

Example option two:

/*******************************************
 * $CARET$
 *******************************************/

After inserting the snippet:

Comment snippets option 2 - placing the cursor at the position of the $CARET$ macro variable

The third option uses the $SELECTION$ macro variable:

/*$SELECTION$*/

Select typed comments, code, or an entity in the query window and insert the CommentSelection snippet:

Selecting typed comments, code, or an entity in the query window and inserting the CommentSelection snippet

The selected portion will be wrapped with the block comment:

CREATE TABLE [dbo].[Business](
        [ID] [int] NULL,
	[Category] [int] NULL,
        /*[BussinessName] [varchar](20) NULL*/
) ON [PRIMARY]
GO

CURSOR

The CURSOR snippet inserts the DECLARE CURSOR statement in the query window. It contains the $CARET$ and the $OBJECT$ variables. The variable will be replaced with a name of an object selected from the hint-list.

DECLARE $CARET$ CURSOR
FOR SELECT
FROM $OBJECT$
WHERE

Example:

When inserting the CURSOR snippet, select the snippet from the hint-list:

Selecting the snippet from the hint-list when inserting the CURSOR snippet

The hint-list with the available tables will be prompted. Select the table from the hint-list:

Selecting the table from the hint-list

The name of the selected table will be inserted at the place of the $OBJECT$ macro variable and the cursor will be placed at the position of the $CARET$ variable:

The cursor will be placed at the position of the $CARET$ variable

DATE

When called, this built-in snippet replaces the $DATE$ variable and give the current time.

Example:

DATE snippet replaces the $DATE$ variable and give the current time

Code in the query window after inserting the DATE snippet:

CASE WHEN DateID = 12/13/2014

DELETE

Inserts the DELETE/FROM/WHERE statement in the query window and replaces the $OBJECT$ macro variable with a name of a table selected from the hint-list

DELETE FROM $OBJECT$
WHERE

Example:

Delete snippet inserts the DELETE/FROM/WHERE statement in the query window and replaces the $OBJECT$ macro variable with a name of a table selected from the hint-list

Code in the query window after inserting the DELETE snippet:

DELETE FROM Person.Address
WHERE

FETCH

The fetch snippet inserts the FETCH NEXT statement in the query window. The FETCH statement retrieves a specific row from a Transact-SQL server cursor. It contains the $CARET$ variable

FETCH NEXT FROM $CARET$ INTO 

After inserting the fetch snippet the variable will be replaced with a cursor:

The variable will be replaced with a cursor after inserting the fetch snippet

FUNCTION

The function built-in snippet inserts the CREATE FUNCTION statement in the query window with a check if the function already exists. The snippet also contains the $DATE$ and the $OSUSER$ macro variables and gives the current time and a local OS username in a function header, and the $CARET$ variable that places the cursor in the query window:

IF object_id('$CARET$') IS NOT NULL
BEGIN 
        PRINT 'Dropping function'
        DROP FUNCTION 
        IF @@ERROR = 0 PRINT 'Function dropped'
END
GO

CREATE FUNCTION 
/***********************************************************
* Function description:
* Date:   $DATE$  
* Author: $OSUSER$
*
* Changes
* Date		Modified By			Comments
************************************************************
*
************************************************************/
(
        <parameters>
) 
RETURNS <data type>
BEGIN
        DECLARE
    	@v_ret <data type>

        SET @v_ret = ...

        RETURN @v_ret
END

Code in the query window after inserting the function snippet:

Code in the query window after inserting the function snippet

Q: Can I transfer snippets from one machine and share it with others?

A: Yes, each snippet is a separate .xml file and using the Import/Export options in the Manage snippets form, all snippets can be saved as a compressed file and used across the team:

Import Export options

Q: Can I edit or delete a built-in snippet?

A: Yes, there is no difference in manipulation with built-in and newly created snippets, any of them can be edited or deleted from the library.

Q: When creating a snippet from a query, will the snippet name be generated automatically?

A: No, when creating a snippet from the query, only SQL code will be used to populate the Code section. The name of the snippet must be specified manually.

Q: Where are the snippets located by default?

A: When ApexSQL Complete is installed, snippets folder will be created in the following path:

C:\Users\<current_user>\AppData\Local\ApexSQL\ApexSQLComplete\Snippets

Q: Will the snippets be removed automatically when I uninstall ApexSQL Complete?

A: No, Snippets folder remains undeleted in the default location.

Q: Will the installation of new version overwrite the snippets from an older one?

A: Yes, installation of a new version will replace all snippets from the Snippets folder in:

C:\Users\<current user>\AppData\Local\ApexSQL\ApexSQLComplete\Snippets

If you want to keep the customized snippet list, create a backup, or simply rename the Snippets folder, so it won’t be affected.