sql过滤代码段_如何创建和管理T-SQL代码段

sql过滤代码段

介绍 (Introduction)

Transact-SQL (T-SQL) snippets were first introduced in SQL Server 2012 Management Studio. T-SQL snippets are templates containing one or more T-SQL statements which you can easily use them when you develop T-SQL scripts. The main concept behind code snippets is code reuse. With code reuse you develop faster, easier and with less syntax errors. If you frequently use specific T-SQL statements, then you should consider creating snippets with these statements as they will help you a lot.

Transact-SQL（T-SQL）片段最初是在SQL Server 2012 Management Studio中引入的。 T-SQL代码段是包含一个或多个T-SQL语句的模板，您在开发T-SQL脚本时可以轻松地使用它们。 代码段背后的主要概念是代码重用 。 通过代码重用，您可以更快，更轻松地开发并且语法错误更少。 如果您经常使用特定的T-SQL语句，则应考虑使用这些语句创建代码片段，因为它们将对您有很大帮助。

There are two types of T-SQL snippets: (i) Expansion, and (ii) SurroundsWith.

T-SQL片段有两种类型：（i） 扩展和（ii） SurroundsWith 。

Expansion snippets are full T-SQL statements which can be added in your T-SQL scripts. SurroundsWith snippets is code which can surround other T-SQL statements. For example, you can have a snippet that has the “BEGIN…END” block, “IF” block, “WHILE” block, “TRY…CATCH” block, etc.

扩展代码段是完整的T-SQL语句，可以在您的T-SQL脚本中添加。 SurroundsWith片段是可以包围其他T-SQL语句的代码。 例如，您可以使用具有“ BEGIN…END”块，“ IF”块，“ WHILE”块，“ TRY…CATCH”块等的代码段。

Even though snippets were first introduced in SQL Server 2012 Management Studio, they existed in Visual Studio long before SQL Server 2012 so their benefits and usage are well known to the technical community.

尽管代码片段最初是在SQL Server 2012 Management Studio中引入的，但它们早在SQL Server 2012之前就已存在于Visual Studio中，因此它们的优点和用法为技术社区所熟知。

在SSMS中使用T-SQL代码段 (Using T-SQL snippets in SSMS)

Now let’s see a simple example on how we can use a built-in snippet in SQL Server Management Studio.

现在，让我们看一个简单的示例，说明如何在SQL Server Management Studio中使用内置代码段。

If you right click in SQL Server Management Studio’s (SSMS) Query Window, then among other, you will be presented with the following two options:

如果在SQL Server Management Studio（SSMS）的“查询”窗口中单击鼠标右键，则会为您提供以下两个选项：

• Insert Snippet…
  插入代码段…
• Surround With…
  环绕…

If you select the “Insert Snippet…” action, you are presented with the available snippet categories from which you can use the snippet to use.

如果选择“插入代码段…”操作，则会显示可用的代码段类别，您可以从中使用代码段来使用。

In this example I used the “Inline Table Function” snippet and here’s what I got in my query window:

在此示例中，我使用了“ Inline Table Function”代码段，这是在查询窗口中获得的内容：

If you select the “Surround With…” action, you are then presented with the below built-in snippets:

如果选择“环绕……”操作，则会显示以下内置片段：

So if you have a statement in your query window (i.e. “SELECT * FROM tbl1”) and select it, and then you use the SurroundWith snippet “If”, your T-SQL statement will be automatically surrounded by an IF block.

因此，如果您在查询窗口中有一条语句（即“ SELECT * FROM tbl1”）并选择它，然后使用SurroundWith片段“ If”，则T-SQL语句将自动被IF块包围。

创建自定义的T-SQL代码段–手动方式 (Creating custom T-SQL snippets – The manual way)

When I first checked out T-SQL snippets in SQL Server Management Studio 2012, I was excited as it was a long-awaited feature. However, right after the excitement, I wondered how it could be possible to create my own snippets. I found out that this was not very straightforward as in order to create a custom T-SQL snippet for SSMS you need to write XML code. Below you can see the XML template for creating a T-SQL snippet.

当我第一次在SQL Server Management Studio 2012中签出T-SQL代码片段时，我很兴奋，因为它是期待已久的功能。 但是，在兴奋之后，我想知道如何创建自己的摘录。 我发现这并不是很简单，因为要为SSMS创建自定义T-SQL代码段，您需要编写XML代码。 在下面，您可以看到用于创建T-SQL代码段的XML模板。

As you can see, even it’s not difficult, each time you want to create or modify a custom T-SQL snippet, you will need to write or modify XML code. For example, consider that we want to create the corresponding T-SQL snippet for the below T-SQL script:

如您所见，即使不难，每次您要创建或修改自定义T-SQL代码段时，都需要编写或修改XML代码。 例如，考虑我们要为以下T-SQL脚本创建相应的T-SQL代码段：

If we were about to create the snippet manually, we would have to write the below XML code:

如果要手动创建代码段，则必须编写以下XML代码：

Even though I enjoy writing code, because the purpose of snippets is to make our life easier, I would prefer an easier way as well to create and manage my custom T-SQL Snippets. For this reason, within the context of my initiative SQLArtBits, I have developed a free tool called “Snippets Generator” which can be used for this exact purpose.

尽管我喜欢编写代码，但由于代码片段的目的是使我们的生活更轻松，所以我也希望使用更简单的方法来创建和管理自定义T-SQL代码片段。 因此，在我的倡议SQLArtBits的背景下，我开发了一个免费的工具，称为“ 片段生成器 ”，可用于此确切目的。

使用片段生成器创建自定义的T-SQL片段 (Creating custom T-SQL snippets with Snippets Generator)

Snippets Generator is a free, lightweight program which makes it easy for anyone to create a T-SQL snippet for SSMS 2012 or later.

Snippets Generator是一个免费的轻量级程序，它使任何人都可以轻松地为SSMS 2012或更高版本创建T-SQL代码段。

As you can see in the above screenshot, Snippets Generator provides a convenient GUI environment in which you just need to complete 5 simple fields:

从上面的屏幕截图中可以看到，Snippets Generator提供了一个便捷的GUI环境，您只需完成5个简单的字段即可：

• Title: The title of your custom T-SQL snippet. 标题 ：您的自定义T-SQL代码段的标题。
• Description: Your custom T-SQL snippet’s description. Description ：您的自定义T-SQL代码段的描述。
• Author: The snippet’s author name. 作者 ：摘录的作者姓名。
• Snippet Type: The snippet’s type, meaning whether is an “Expansion” or “SurroundsWith” snippet.
• 代码段类型 ：代码段的类型，即是“扩展”代码段还是“ SurroundsWith”代码段。
• Snippet T-SQL Code: As the name implies, in this textbox you write the T-SQL statement that you want to be included in the snippet. 片段T-SQL代码：顾名思义，在此文本框中，您将编写要包含在片段中的T-SQL语句。

In order to further assist you in the process of creating a new T-SQL snippet, Snippets Generator provides different code formatting options as well as the below additional functionality:

为了进一步帮助您创建新的T-SQL代码段，Snippets Generator提供了不同的代码格式设置选项以及以下附加功能：

• Open existing SQL files in order to generate snippets based on the SQL files’ contents.
  打开现有SQL文件，以便根据SQL文件的内容生成摘要。
• The ability to set a “Default Author” name in order not to have to manually enter it each time.
  可以设置“默认作者”名称，而不必每次都手动输入名称。
• A “Snippet Templates” library. This library contains built-in snippet templates which you can use them to create your own T-SQL snippets.
  “代码段模板”库。 该库包含内置的片段模板，您可以使用它们来创建自己的T-SQL片段。
• The ability to save your T-SQL snippet as template and thus add it to the Snippet Templates library for future use.
  可以将您的T-SQL代码段另存为模板，然后将其添加到代码段模板库中以供将来使用。

Below you can see a screenshot of the Snippet Templates library.

您可以在下面看到Snippet Templates库的屏幕截图。

Now let’s create the snippet for the query of Listing 1 using Snippets Generator.

现在，使用片段生成器为清单1的查询创建片段。

If you compare the two methods for creating the snippet, I’m sure you will agree with me that using Snippets Generator is a much easier and faster method for creating the snippet.

如果您比较两种创建代码段的方法，我相信您会同意我的观点，即使用代码段生成器是一种更轻松，更快速的代码段创建方法。

Now let’s import the snippet into SSMS in order to verify its correctness.

现在，让我们将代码段导入SSMS，以验证其正确性。

We will import the snippet in the snippets category ‘My Code Snippets’:

我们将在“我的代码片段”片段中导入该片段：

Now that the snippet was imported in SSMS, let’s use it in a Query Window:

现在，该代码段已导入SSMS中，让我们在查询窗口中使用它：

结论 (Conclusion)

Code reuse in SQL Server Management Studio with the use of T-SQL snippets is a handy way to be more productive with less effort. Snippets Generator is a free, lightweight program which makes it easy for anyone to create a T-SQL snippet. With Snippet Generator’s assistance, you can easily create snippets for your everyday T-SQL scripting needs, so that whenever you need to run certain queries, to just call them via the snippets functionality in SSMS. Therefore, with the use of snippets, you will no longer need to remember complex T-SQL queries, as you will just be able to use them via your custom T-SQL snippets.

使用T-SQL代码段在SQL Server Management Studio中进行代码重用是一种便捷而又省力的方法。 Snippets Generator是一个免费的轻量级程序，它使任何人都可以轻松创建T-SQL代码段。 借助Snippet Generator的帮助，您可以轻松创建满足您日常T-SQL脚本需求的代码段，以便在需要运行某些查询时，只需通过SSMS中的代码段功能对其进行调用。 因此，使用代码片段，您将不再需要记住复杂的T-SQL查询，因为您将能够通过自定义的T-SQL代码片段使用它们。

翻译自: https://www.sqlshack.com/how-to-create-and-manage-t-sql-code-snippets/

sql过滤代码段