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 Options command and choose the Snippets tab:

ApexSQL Complete main menu

The Snippets tab will show the list of snippets including SQL code for each snippet:

Snippet tab

Create a new snippet

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

Create snippet window

Another way to create a snippet is to pick a snippet code directly from the query window. Right-click the query widow and choose the New snippet option and it 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):

New snippet command from context menu

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

New snippet window

Using the built-in snippets

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

Insert snippet from context menu

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

The snippets from hint-list

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:

built-in snippet from the hint-list

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 BeginEnd snippet from the hint list:

The BeginEnd snippet from the hint list

The cursor will be placed between Begin and End:

cursor between Begin and End operators

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:

The Try/Catch built-in snippet

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

the %CARET% macro variable: in comments

Example option two:

/*******************************************
 * %CARET%
*******************************************/

After inserting the snippet:

the %CARET% macro variable in block comment

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:

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:

The Cursor snippet

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

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 %OBJECT% macro variable

Date

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

Example:

The %DATE% variable

Code in the query window after inserting the Date snippet:

CASE WHEN DateID = 12 / 13 / 2017

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:

The %OBJECT% macro variable with a name of a table

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

The function built-in snippet

FAQs

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 window, all snippets can be saved as a compressed file and used across the team:

The Export/Import snippets

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

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