ApexSQL Complete – Using macro variables to create SQL snippets

This video shows how to use a macro variable to add/replace values to be automatically added to the T-SQL snippet code that represents more detailed values.

Transcript

Hello, and welcome to this video presentation in which we’ll explain ApexSQL Complete snippet macro variables.

The snippet dialog contains a list of macro variables like date, time and database name:


 

Using these macro variables, in the snippet code, will return a variety of results including the current date, time, connection server name, connection login, connection database, a connection user name, a local OS username, a local host username and more.

Let’s create a new snippet using those variables.

Type the name of the macro in the Name box and in the code section paste some code:

Click the OK button.

Let’s call this snippet and see the results:

As you can see, 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:

Now let’s create another snippet, but this time we will use to the Object macro variable.

The Object variable will replace the macro variable with an object selected from the hint-list. For example, let’s create an alter table snippet.

Type the name of the snippet and in the code section type alter statement:

Now, let’s call this snippet:

After choosing the AlterTable snippet the hint-list with all objects will be shown:

https://solutioncenter.apexsql.com/wp-content/uploads/2015/03/word-image118bu1.png

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.

Another interesting macro is the Selection macro.

A snippet created using the Selection variable will replace the variable with the name of the highlighted selection from the query window.

Let’s create a snippet with the Selection macro included:

Let’s paste some code and select it. In the query context menu, choose the Insert snippet option and from the list, choose the EncapsulateProcedure 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:

The Prompt macro creates a dialog with a custom prompt. Let’s create a snippet with Prompt macro and demonstrate what it can do:

Let’s replace default values with ours.

The name stands for the name of a variable. The text stands for the title of the text box. The default stands for the text that appears in the text box and title stands for the dialog title:

Let’s use this snippet in the query window.

When this snippet is called, the dialog will be shown with all parameters that we previously set:

New trigger dialog

Enter the name of the trigger and press OK button:

https://solutioncenter.apexsql.com/wp-content/uploads/2015/03/word-image122au1.png

As you can see, the inserted name will be saved and used in the place in a snippet where it’s defined.

Thanks for watching. For more information, please visit www.apexsql.com