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

2022-08-31 14:38:26 浏览数 (1)

大家好，又见面了，我是你们的朋友全栈君。

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 ：

代码语言：javascript复制
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表中添加一些虚拟记录：

代码语言：javascript复制
USE BookStore
 
INSERT INTO Books
    
VALUES
(1, 'Book1', 'Cat1', 1800),
(2, 'Book2', 'Cat2', 1500),
(3, 'Book3', 'Cat3', 2000),
(4, 'Book4', 'Cat4', 1300),
(5, 'Book5', 'Cat5', 1500),
(6, 'Book6', 'Cat6', 5000),
(7, 'Book7', 'Cat7', 8000),
(8, 'Book8', 'Cat8', 5000),
(9, 'Book9', 'Cat9', 5400),
(10, 'Book10', 'Cat10', 3200)

The above script adds 10 dummy records in the Books table.

上面的脚本在Books表中添加了10条虚拟记录。

使用sp_executesql存储过程 (Working with the sp_executesql stored procedure)

As I mentioned earlier, the sp_executesql stored procedure is used to execute dynamic SQL queries that are in the form of a string. Let’s see this in action.

如前所述，sp_executesql存储过程用于执行字符串形式的动态SQL查询。 让我们看看实际情况。

Run the following script:

运行以下脚本：

代码语言：javascript复制
DECLARE @SQL_QUERY NVARCHAR(128)
SET @SQL_QUERY = N'SELECT id, name, price FROM Books WHERE price > 4000 '
EXECUTE sp_executesql @SQL_QUERY

In the script above, we declare a variable @SQL_QUERY and initialize it with a string query that returns the id, name, and price from the Books table where the price is greater than 4,000.

在上面的脚本中，我们声明一个变量@SQL_QUERY并使用字符串查询对其进行初始化，该字符串查询从Books表中返回价格大于4,000的id，名称和价格。

Next, we execute the sp_executesql stored procedure via the EXECUTE command. To execute a dynamic SQL query that is in the string format, you simply have to pass the string containing the query to the sp_executesql query.

接下来，我们通过EXECUTE命令执行sp_executesql存储过程。 若要执行字符串格式的动态SQL查询，只需要将包含查询的字符串传递给sp_executesql查询。

It is important to mention that the string should be in the Unicode format before the sp_executesql stored procedure executes it. This is the reason we put ‘N’ at the beginning of the string containing the @SQL_QUERY variable. The ‘N’ converts the query string into the Unicode string format. Here is the output of the above script:

值得一提的是，在sp_executesql存储过程执行该字符串之前，该字符串应为Unicode格式。 这就是我们将’N’放在包含@SQL_QUERY变量的字符串开头的原因。 “ N”将查询字符串转换为Unicode字符串格式。 这是上面脚本的输出：

In real life database queries, the filter or condition is passed by the users. For instance, a user may search books within a specific search limit. In that case, the SELECT query remains the same, only the WHERE condition is changed. It is convenient to store the WHERE clause in a separate string variable and then concatenate the SELECT condition with the WHERE clause to create the final query. This is shown in the following example:

在现实生活中的数据库查询中，过滤器或条件由用户传递。 例如，用户可以在特定搜索限制内搜索书籍。 在这种情况下，SELECT查询保持不变，只改变WHERE条件。 将WHERE子句存储在单独的字符串变量中，然后将SELECT条件与WHERE子句连接起来以创建最终查询是很方便的。 在下面的示例中显示：

代码语言：javascript复制
DECLARE @CONDITION NVARCHAR(128)
DECLARE @SQL_QUERY NVARCHAR (MAX)
SET @CONDITION = 'WHERE price > 5000'
SET @SQL_QUERY =N'SELECT id, name, price FROM Books '  @CONDITION
EXECUTE sp_executesql @SQL_QUERY

Here in the script above, we declare two variables: @CONDITION and @SQL_QUERY. The @CONDITION variable contains the WHERE clause in string format whereas the @SQL_QUERY contains the SELECT query. Next, these two variables are concatenated and passed to the sp_executesql stored procedure. Here is the output:

在上面的脚本中，我们声明两个变量：@CONDITION和@SQL_QUERY。 @CONDITION变量包含字符串格式的WHERE子句，而@SQL_QUERY包含SELECT查询。 接下来，将这两个变量连接起来并传递给sp_executesql存储过程。 这是输出：

The output shows all the books where the price is greater than 5,000.

输出显示价格大于5,000的所有书籍。

将参数传递给sp_executesql存储过程 (Passing parameters to sp_executesql stored procedure)

You can also pass parameters to the sp_executesql stored procedure. This is particularly handy when you don’t know the values used to filter records before runtime. To execute a sp_executesql stored procedure with parameters, you need to perform the following steps:

您还可以将参数传递给sp_executesql存储过程。 当您在运行时不知道用于过滤记录的值时，这特别方便。 若要执行带有参数的sp_executesql存储过程，您需要执行以下步骤：

  1. First, you need to create a variable that is going to store the list of parameters 首先，您需要创建一个变量，该变量将存储参数列表
  2. Next, in the query string, you need to pass the names of the parameters 接下来，在查询字符串中，您需要传递参数名称
  3. Finally, you need to pass the query, the variable that contains a list of parameters and the actual parameters along with their values to the sp_executesql stored procedure 最后，您需要将查询，包含参数列表和实际参数及其值的变量传递给sp_executesql存储过程。

Look at the following example:

看下面的例子：

代码语言：javascript复制
DECLARE @CONDITION NVARCHAR(128)
DECLARE @SQL_QUERY NVARCHAR (MAX)
DECLARE @PARAMS NVARCHAR (1000)
SET @CONDITION = 'WHERE price > @LowerPrice AND price < @HigherPrice'
SET @SQL_QUERY = N'SELECT id, name, price FROM Books '  @CONDITION
SET @PARAMS = '@LowerPrice INT, @HigherPrice INT'
EXECUTE sp_executesql @SQL_QUERY ,@PARAMS, @LowerPrice = 3000, @HigherPrice = 6000

In the script above, we create three variables: @CONDITION, @SQL_QUERY, and @PARAMS. The @PARAMS variable is a variable that stores the list of parameters that we will use in the string query format.

在上面的脚本中，我们创建三个变量：@ CONDITION，@ SQL_QUERY和@PARAMS。 @PARAMS变量是一个变量，它存储将在字符串查询格式中使用的参数列表。

If you look at the value of the @CONDITION variable, it contains a WHERE clause with two parameters: @LowerPrice and @HigherPrice. To specify a parameter inside a string query, you simply have to prefix with the ‘@’ operator before the name of the parameter. Here the @LowerPrice parameter is used to set the lower bound for the price of books whereas the @HigherPrice sets the higher bound for the value in the price column of the BookStore table.

如果您查看@CONDITION变量的值，则该变量包含带有两个参数的WHERE子句：@LowerPrice和@HigherPrice。 要在字符串查询中指定参数，您只需在参数名称前加上“ @”运算符即可。 在这里，@LowerPrice参数用于设置书籍价格的下限，而@HigherPrice设置BookStore表的price列中的值的上限。

Next, while executing the sp_executesql stored procedure, the @SQL_QUERY variable which contains the string query is passed along with the @PARAMS variable which contains the parameter list. The parameter names i.e. @LowerPrice and @HigherPrice are also passed to the sp_executesql stored procedure along with the values 3,000 and 6,000 respectively. In the output, you will see the records where the price is between 3,000 and 6,000 as shown below:

接下来，在执行sp_executesql存储过程时，将包含字符串查询的@SQL_QUERY变量与包含参数列表的@PARAMS变量一起传递。 参数名称（即@LowerPrice和@HigherPrice）也分别与值3,000和6,000一起传递到sp_executesql存储过程。 在输出中，您将看到价格在3,000到6,000之间的记录，如下所示：

结论 (Conclusion)

This article explains the functionality of sp_executesql stored procedure which is used to execute dynamic SQL queries. The article shows how to execute the SELECT query in the form of a string via sp_executesql stored procedure. You also saw how to pass parameters to the sp_executesql stored procedure in order to execute queries where values are passed at runtime.

本文介绍了用于执行动态SQL查询的sp_executesql存储过程的功能。 本文介绍如何通过sp_executesql存储过程以字符串形式执行SELECT查询。 您还看到了如何将参数传递给sp_executesql存储过程，以便执行在运行时传递值的查询。

翻译自: https://www.sqlshack.com/using-sp_executesql-stored-procedure-for-executing-dynamic-sql-queries/

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/143296.html原文链接：https://javaforall.cn

sql sqlserver 数据库 存储 linux

0 人点赞