SQL Prompt 9

Using SSMS templates in SQL Prompt snippets

You can specify SQL Server Management Studio template parameters in SQL Prompt snippets.

Example: Creating and inserting a snippet with template parameters

In this example, we will create a snippet to select the following from a table:

  • the top N rows
  • a title displaying the total number of rows

To create the snippet:

  1. On the SQL Prompt menu, select Snippet Manager.
  2. Click New.
  3. In the Snippet box, type the text that will insert the snippet.
    In this example, we type stn:

  4. Type or paste SQL code that includes template parameters in the Code box.
    You specify template parameters using the format <parameter_name, data_type, value>. For more information, see Using SQL Server Management Studio Templates (Microsoft documentation).
    In this example, we use the following snippet code:

    SELECT 'Top <howmany,int,100> rows of ' + CONVERT(NVARCHAR(MAX), COUNT(*)) + ' in <table_name,sysname,MyTable>' AS Title 
    	FROM <table_name,sysname,MyTable>;
    SELECT TOP <howmany,int,100> * 
    	FROM <table_name,sysname,MyTable>;
  5. Click Save.

We can now insert the snippet into a query, and replace the template parameters with the values we want to use:

  1. In a query window, type stn, and then press Tab.
    The snippet is inserted into the query, and the Specify Values for Template Parameters dialog box opens:
  2. Specify the values you want to use, and click OK.

Didn't find what you were looking for?