使用sp_executesql存储过程执行动态SQL查询

The sp_executesql stored procedure is used to execute dynamic SQL queries in SQL Server. A dynamic SQL query is a query in string format. There are several scenarios where you have an SQL query in the form of a string.

sp_executesql存储过程用于在SQL Server中执行动态SQL查询。 动态SQL查询是字符串格式的查询。 在几种情况下，您都可以使用字符串形式SQL查询。

For example, if a user wants to search for a product by name, he will enter the name of the product in a search box on the website. The product name, which is in the form of a string will be concatenated with a SELECT query to form another string. These types of queries need to be executed dynamically because different users will search for different product names and so a query will need to be generated dynamically depending on the product name.

例如，如果用户要按名称搜索产品，则将在网站上的搜索框中输入产品名称。 字符串形式的产品名称将与SELECT查询连接在一起以形成另一个字符串。 这些类型的查询需要动态执行，因为不同的用户将搜索不同的产品名称，因此将需要根据产品名称动态生成查询。

Now that you understand what dynamic SQL is, let’s see how the sp_executesql stored procedure can be used to execute dynamic SQL queries.

现在您了解了什么是动态SQL，让我们看看如何使用sp_executesql存储过程执行动态SQL查询。

Let’s first create some dummy data that we can use to execute the examples in this article.

首先，让我们创建一些虚拟数据，以用于执行本文中的示例。

创建虚拟数据 (Creating dummy data)

The following script creates a dummy database named BookStore with one table i.e. Books. The Books table has four columns: id, name, category, and price:

以下脚本使用一个表（即Books）创建一个名为BookStore的虚拟数据库。 Books表包含四列： id ， name ， category和price ：

CREATE Database BookStore;
GO
USE BookStore;
CREATE TABLE Books
(
id INT,
name VARCHAR(50) NOT NULL,
category VARCHAR(50) NOT NULL,
price INT NOT NULL
)

Let’s now add some dummy records in the Books table:

现在让我们在Books表中添加一些虚拟记录：

USE BookStore
 
INSERT INTO Books
    
VALUES