Creating and Using SQL Code Snippets (Quick Ref)
Published 29 October 2019
What's the difference between SQL Prompt snippets and SSMS templates and snippets?
SQL Prompt snippets remove many of the limitations of standard SSMS templates and snippets and add versatility and flexibility. Each snippet offers various replacement points, or placeholders, within the code.
- Custom placeholders – make it simple to include multiple replacement points for the same parameter, and you can provide a default value for each one.
- Built-in placeholders – when a snippet is invoked, SQL Prompt will automatically inject into the placeholder the appropriate value or text. For example, the currently highlighted SQL text ($SELECTEDTEXT$ placeholder), or values such as the date ($DATE$) and logged-in developer ($USER$), or database-wide values such as the database name ($DBNAME$).
You can also use SSMS template parameters (<parameter_name, data_type, default_value>) in snippets. SQL Prompt will call the Specify Values for Template Parameters function in SSMS, and when you invoke the snippet, you're presented with a SSMS form for specifying the template parameter values.
- From scratch – use the SQL Prompt - Options menu, or SQL Prompt - Snippet Manager, and choose Snippets > New… (or Edit…)
- From existing SQL Text – highlight the text in the query pane and select Create Snippet from the Action menu or from the right-click context menu
Invoke snippets from the query pane…
- Use the suggestion box – type Ctrl-space then from the All Suggestions drop down select Snippets
- Start typing the snippet's name – by convention this will be the initial letters of its description, so citf for "create inline table function".
- Select Insert Snippet… from the right-click context menu – to use any of the pre-defined snippets that create one of the common types of database objects (tables, views, indexes and so on).
- With any text highlighted, select a snippet from the Action menu – only those snippets that have the $SELECTEDTEXT$ placeholder will be visible. SQL Prompt will insert the snippet code into the query pane, replacing any $SELECTEDTEXT$ placeholders within that code with your highlighted text.