Applies to
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:
The Snippets tab will show the list of snippets including SQL code for each snippet:
Create a new snippet
New snippets can be added by clicking the New button, which will initiate the 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):
Selected part will be automatically inserted in the code section of the 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:
The hint–list with all available snippets will be shown:
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:
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 cursor will be 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:
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:
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:
Example option two:
/*******************************************
* %CARET%
*******************************************/
After inserting the snippet:
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 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 hint-list with the available tables will be prompted. Select 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:
Date
When called, this built-in snippet replaces the %DATE% variable and give the current time.
Example:
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:
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:
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:
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:
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.
