SQL code snippet is a template which contains the basic structure of a Transact-SQL statement or block, so that you can quickly write queries without any syntax errors.
It was released in SQL Server 2012 version.
It can be used in case, if you forget any T- SQL syntax such as creating an index, UDF functions, Stored Procedures, Triggers and so on.. or may be struggling with writing a complex T-SQL syntax then you can quickly use SQL code Snippets feature which provides a structure of code for you just on click also it helps you in writing a T-SQL code in faster way that can save your time.
Lets see the tasks that you can perform using code snippets.
Inserting Snippets
Insert Snippet menu allows you to choose a snippet from a categorized list of snippets.
Lets see how to use it step by step as shown below.
In your Query editor window, put the cursor where you want to insert the SQL snippet.
Next Press CTRL K + X.
Once you press CTRL K +X, you will see a insert snippets appears. Which allows you to choose snippets that you want as shown below.
Now, to generate a snippet for Stored Procedure, just double click on Stored Procedure snippet.
[Also Read: SQL Server Template Explorer]
After clicking on Stored Procedure snippets, it gives you more suggestion on what type of Stored Procedure you want.
Now you can choose as per your requirement, Lets double click on Create Procedure Basic Template as shown below.
After clicking on that, you will see it generates a code for Stored procedure as shown below.
Now you just need to give a name to stored procedures and modify the parameters and logic.
Apart that using a Keyboard shortcut CTRL K +X , you can also launch an Insert Snippets either by Context menu or Edit menu in SSMS as following:
1. Just Right click on Query Editor Window, and select the Insert Snippet.. from the context menu.
2. In SSMS Edit Menu, Point to IntelliSense then Point to Insert Snippets.. and double click.
Inserting Surround-with Snippets
A surround-with snippet provides a template that you can use as a starting point when enclosing a set of T-SQL statements in a BEGIN, IF, or WHILE block.
Lets see how to use surround-with snippet step by step as shown below.
In your Query editor window, and select the set of statements to be included in the block as shown below.
After selecting a statements then Press CTRL K + S.
Once you press CTRL K+ S, you will see a Surround with snippets appears.
Lets say, you want to put that set of statements inside the while loop then you can simply double click on While code snippet that generates a code snippet for while loop.
Once you double click on While, you can set all the set of statements that you selected is included in while loop as shown below.
Now you can, modify the condition in while loop as per your requirement.
Same like Insert Snippets, a Surround snippets can also be accessed using other different way which are as follows.
1. Just Right click on Query Editor Window, and select the Surround With…Â from the context menu as shown below.
2. In SSMS Edit Menu, Point to IntelliSense then Point to Surround With… and double click.
Manage and Customize Code Snippets
You can also manage and customize code snippets, for go to Tool menu in SSMS and Point to Code Snippets Manager.. and click on that.
Once you click on that, a Code Snippets Manger dialog box opens.
Here you can see all the code Snippets with an option of adding and removing code snippets. You can also create a new code snippet and import it under the respective group.
Upon selection of any code snippets, you can see the store path of selected code snippet in the location field.
Lets select the Stored Procedure folder that contains all the snippets for stored procedure, and copy the location and paste it in window directory.
C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\SQL\Snippets\1033\Stored Procedure
Once you paste that path in window directory and hit enter key, you will see all the snippets related to stored procedure.
Customize the Code SnippetsÂ
To customizing the code snippet files, first you need to open the file.
Right click on that file, now you can open it with visual studio or Notepad++ or any other editor which support XML code.
You can see, the code is written in XML format. Now you can add your customized code within that file.
Implementing a customized Snippets
To customized any snippet, a better way of doing that is just take a copy of snippet that you want to customized, and modify that copied file for your requirement.
Lets create your own Code Snippets for stored procedure.
Lets take a copy of Create stored Procedure.snippet, just copy and paste (Ctrl C, and Ctrl V)Â in directory folder as shown below.
Now open the copied snippets, just right click and open either in visual studio or Notepad++, also can be opened with any editor that support the XML code.
Once you open the snippet file, lets change the Title first.
Change the Title from Create Procedure Basic Template to Create Procedure Basic Customized Template.
Lets add one more parameter to the stored procedure, named it param3 of data type varchar(100).
Just scroll down the snippet code slowly and add new parameter just right after in same pattern with little modification as the last parameter was specified that is param2.
Now save the modified snippets, press CTRL + S and close.
After that rename the snippets, so that you can easily identify a customized snippet while accessing the snippets for stored procedures.
Renamed it as Create Stored Procedure – Customized.snippet.
Lets check the implementation of customized snippets. Go back to your SQL Server Management Studio and new Query editor window.
Press CTRL K + X, then Select Stored Procedure.
Once you click on Stored Procedure folder.
You will see that a customized snippets that you created appears in suggestion list.
Just double click on that Create Procedure Basic Customized Template.
Once you double click on that, you will see it generate a stored procedure code that contains three parameters, and the data type of third parameter is varchar(100) as you specified.
Also Read..
Enable a Dark Theme in SQL Server Management Studio
