SQL Server中的动态SQL

最新推荐文章于 2024-08-03 10:26:30 发布

culuo4781

最新推荐文章于 2024-08-03 10:26:30 发布

阅读量1.9k

点赞数

文章标签： python java mysql 数据库 sql

原文链接：https://www.sqlshack.com/dynamic-sql-in-sql-server/

版权

In this article, we will review how to construct and execute dynamic SQL statements in SQL Server with different examples.

在本文中，我们将通过不同的示例回顾如何在SQL Server中构造和执行动态SQL语句。

Dynamic SQL is the SQL statement that is constructed and executed at runtime based on input parameters passed. Let us go through some examples using the EXEC command and sp_executesql extended stored procedure.

动态SQL是根据传递的输入参数在运行时构造和执行SQL语句。让我们来看一些使用EXEC命令和sp_executesql扩展存储过程的示例。

使用EXEC / EXECUTE命令执行动态SQL (Executing dynamic SQL using EXEC/ EXECUTE command)

EXEC command executes a stored procedure or string passed to it. Please refer to EXEC SQL overview and examples for more details and examples on the EXEC command.

EXEC命令执行存储过程或传递给它的字符串。请参考EXEC SQL概述和示例 有关EXEC命令的更多详细信息和示例。

The following example demonstrates constructing the SQL statement using the input variable and executing the SQL statement using the EXEC command.

下面的示例演示使用输入变量构造SQL语句，并使用EXEC命令执行SQL语句。

-- Simple dynamic SQL statement
DECLARE @SQL nvarchar(1000)
 
declare @Pid varchar(50)
set @pid = '680'
 
 
SET @SQL = 'SELECT ProductID,Name,ProductNumber FROM SalesLT.Product where ProductID = '+ @Pid
 
EXEC (@SQL)

There is a possibility of SQL injection when you construct the SQL statement by concatenating strings from user input values. I hope to cover the SQL injection and some methods to prevent SQL Injection in my future articles.

通过连接用户输入值中的字符串构造SQL语句时，可能会发生SQL注入。我希望在以后的文章中介绍SQL注入以及一些防止SQL注入的方法。

We should take care of null values when concatenating strings from parameters using ‘+’ operator. In the below example, I commented out the statement that sets a value to variable “@pid”.

使用'+'运算符将字符串与参数连接时，应注意空值。在下面的示例中，我注释掉了为变量“ @pid ”设置值的语句。

By default, the variable “@pid” is NULL as we did not set any value. The final statement constructed after concatenation is blank as ‘+’ operator does not handle null values. Please refer to the below image that shows the final value of “@SQL” variable is blank.

默认情况下，变量“ @pid ”为NULL，因为我们未设置任何值。串联后构造的最终语句为空，因为'+'运算符不处理空值。请参考下图，显示“ @SQL”变量的最终值为空白。

In this case, use the ISNULL function to construct a proper SQL statement while concatenating strings using ‘+’ operator.

在这种情况下，使用ISNULL函数构造一个正确SQL语句，同时使用'+'运算符连接字符串。

EXEC command does not re-use the compiled plan stored in the plan cache. Execute the following query and check for the cached plans.

EXEC命令不会重新使用计划缓存中存储的已编译计划。执行以下查询并检查缓存的计划。

DECLARE @SQL nvarchar(1000)
 
declare @Pid varchar(50)
set @pid = '689'
 
 
SET @SQL = 'SELECT ProductID,Name,ProductNumber FROM SalesLT.Product where ProductID = '+ @Pid
 
EXEC (@SQL)
 
GO
 
DECLARE @SQL nvarchar(1000)
 
declare @Pid varchar(50)
set @pid = '681'
 
 
SET @SQL = 'SELECT ProductID,Name,ProductNumber FROM SalesLT.Product where ProductID = '+ @Pid
 
EXEC (@SQL)

Please refer to the below image that shows two separate plans created when the above query is executed for two different parameters.

请参考下图，该图显示了针对两个不同参数执行上述查询时创建的两个单独的计划。

使用sp_executesql执行动态SQL (Executing dynamic SQL using sp_executesql)

sp_executesql is an extended stored procedure that can be used to execute dynamic SQL statements in SQL Server. we need to pass the SQL statement and definition of the parameters used in the SQL statement and finally set the values to the parameters used in the query.

sp_executesql是一个扩展的存储过程，可用于在SQL Server中执行动态SQL语句。我们需要传递SQL语句和SQL语句中使用的参数的定义，最后将值设置为查询中使用的参数。

Following is the syntax of executing dynamic SQL statements using sp_executesql extended stored procedure.

以下是使用sp_executesql扩展存储过程执行动态SQL语句的语法。

sp_executesql @stmt, N'@parameter_name data_type' , @param1 = 'value1'

Below example demonstrates executing dynamic SQL statement by passing parameters to sp_executesql extended stored procedure.

下面的示例演示如何通过将参数传递给sp_executesql扩展存储过程来执行动态SQL语句。

EXECUTE sp_executesql   
          N'SELECT ProductID,Name,ProductNumber  
          FROM SalesLT.Product where ProductID = @Pid and ProductNumber=@PNumber',  
          N'@Pid varchar(50),@PNumber varchar(50)',  
          @pid = '680',@PNumber='FR-R92B-58';

dynamic SQL using sp_executesql extended stored procedure

sp_executesql reuses the compiled plan when the statement is executed for different parameters. Execute the following query and check for the cached plan.

当对不同的参数执行该语句时，sp_executesql将重用已编译的计划。执行以下查询并检查缓存的计划。

EXECUTE sp_executesql   
          N'SELECT ProductID,Name,ProductNumber  
          FROM SalesLT.Product where ProductID = @Pid and ProductNumber=@PNumber',  
          N'@Pid varchar(50),@PNumber varchar(50)',  
          @pid = '680',@PNumber='FR-R92B-58';  
 
EXECUTE sp_executesql   
          N'SELECT ProductID,Name,ProductNumber  
          FROM SalesLT.Product where ProductID = @Pid and ProductNumber=@PNumber',  
          N'@Pid varchar(50),@PNumber varchar(50)',  
          @pid = '681',@PNumber='FR-R92B-58';  
 
EXECUTE sp_executesql   
          N'SELECT ProductID,Name,ProductNumber  
          FROM SalesLT.Product where ProductID = @Pid and ProductNumber=@PNumber',  
          N'@Pid varchar(50),@PNumber varchar(50)',  
          @pid = '682',@PNumber='FR-R92B-58';

Please refer to the below image that shows the same plan is being used when the statement is executed with different parameters.

请参考下图，该图显示了使用不同参数执行该语句时正在使用的相同计划。

在存储过程中使用动态SQL (Using dynamic SQL inside stored procedures)

Following is the example of using dynamic SQL inside a stored procedure. For demo purpose, I used the Product table from the AdventureWorksLT database. This stored procedure is used to search for products based on different columns like name, color, productid, and the product number. The dynamic SQL statement is constructed based on the input parameters passed to the stored procedure and is executed by the EXEC command.

以下是在存储过程中使用动态SQL的示例。出于演示目的，我使用了AdventureWorksLT数据库中的Product表。此存储过程用于根据不同的列（例如名称，颜色，产品编号和产品编号）搜索产品。动态SQL语句是根据传递给存储过程的输入参数构造的，并由EXEC命令执行。

CREATE PROCEDURE [dbo].[usp_SearchProducts]  
(
	  @ProductID			NVARCHAR(50) = NULL	
	 ,@Name					NVARCHAR(100) = NULL	
	 ,@ProductNumber        NVARCHAR(100) = NULL	
	 ,@Color				NVARCHAR(100) = NULL	
	 
	
)
AS          
BEGIN      
	SET NOCOUNT ON;  
 
	DECLARE @SQL							VARCHAR(MAX)
	DECLARE @ProductIDFilter				VARCHAR(MAX)
	DECLARE @NameFilter						VARCHAR(MAX)
	DECLARE @ProductNumberFilter			VARCHAR(MAX)
	DECLARE @ColorFilter					VARCHAR(MAX)
	DECLARE @all                            VARCHAR(2)   = '-1'
	
 
	SET @ProductIDFilter = CASE WHEN @ProductID IS NULL OR @ProductID = 0 
	THEN '''' + @all + ''' = ''' + @all + '''' 
	ELSE 'ProductID = ''' +  @ProductID + '''' 
	END
 
	SET @NameFilter = CASE WHEN @Name IS NULL OR @Name = ''
	THEN '''' + @all + ''' = ''' + @all + '''' 
	ELSE 'Name like ''%' + @Name + '%''' 
	END
 
	SET @ProductNumberFilter = CASE WHEN @ProductNumber IS NULL OR @ProductNumber = ''
	THEN '''' + @all + ''' = ''' + @all + '''' 
	ELSE 'ProductNumber like ''%' + @ProductNumber + '%''' 
	END
 
	SET @ColorFilter = CASE WHEN @Color IS NULL OR @Color = ''
	THEN '''' + @all + ''' = ''' + @all + '''' 
	ELSE 'Color like ''' + @Color + '''' 
	END
 
	
 
		  SET @SQL = 'SELECT ProductID
						,Name
						,ProductNumber
						,Color
						,StandardCost
						,Size
						,Weight
					FROM SalesLT.Product
			WHERE ' + @ProductIDFilter
			+ ' AND ' + @NameFilter + ''
			+ ' AND ' + @ProductNumberFilter + ''
			+ ' AND ' + @ColorFilter + ''
			
 
			PRINT (@sql)
			EXEC(@sql)
			
 
END

When we execute the stored procedure with input parameter productid only, the SQL statement is constructed as shown in the below image.

当我们仅使用输入参数productid执行存储过程时，将如下图所示构造SQL语句。

Please refer to the below image that shows a different SQL statement constructed when productid and product number are passed as input parameters to the stored procedure.

请参考下图，该图显示了将productid和产品编号作为输入参数传递到存储过程时构造的另一条SQL语句。

Let us re-write the stored procedure to form dynamic SQL and execute it using sp_executesql extended stored procedure. Please refer to the below sample script.

让我们重新编写存储过程以形成动态SQL并使用sp_executesql扩展存储过程执行它。请参考以下示例脚本。

CREATE PROCEDURE [dbo].[usp_SearchProducts2]  
(
	  @ProductID			NVARCHAR(50) = NULL	
	 ,@Name					NVARCHAR(100) = NULL	
	 ,@ProductNumber        NVARCHAR(100) = NULL	
	 ,@Color				NVARCHAR(100) = NULL	
	 
	
)
AS          
BEGIN      
	SET NOCOUNT ON;  
 
	DECLARE @SQL							NVARCHAR(MAX)
	DECLARE @ParameterDef					NVARCHAR(500)
 
    SET @ParameterDef =      '@ProductID			NVARCHAR(50),
							@Name					NVARCHAR(100),
							@ProductNumber			NVARCHAR(100),
							@Color					NVARCHAR(100)'
 
 
 
    SET @SQL = 'SELECT ProductID
						,Name
						,ProductNumber
						,Color
						,StandardCost
						,Size
						,Weight
					FROM SalesLT.Product WHERE -1=-1 ' 
 
IF @ProductID IS NOT NULL AND @ProductID <> 0 
SET @SQL = @SQL+ ' AND ProductID = @ProductID'
 
IF @Name IS NOT NULL AND @Name <> ''
 
SET @SQL = @SQL+ ' AND Name like ''%'' + @Name + ''%'''
 
 
IF @ProductNumber IS NOT NULL AND @ProductNumber <>''
SET @SQL = @SQL+ ' AND ProductNumber like ''%'' + @ProductNumber + ''%'''
 
IF @Color IS NOT NULL AND @Color <>''
SET @SQL = @SQL+  ' AND Color like ''%'' + @Color + ''%'''
 
   EXEC sp_Executesql     @SQL,  @ParameterDef, @ProductID=@ProductID,@Name=@Name,@ProductNumber=@ProductNumber,@Color=@Color
               
                
 
END
 
GO

Let us execute below sample thread that will retrieve all the products that are red.

让我们在示例线程下面执行，它将检索所有红色的产品。

[usp_SearchProducts2] @ProductID=0,@Name='',@ProductNumber='',@Color = 'red'

sp_executesql中的OUTPUT参数 (OUTPUT parameter in sp_executesql)

sp_executesql extended stored procedure supports the output parameter to store the value returned by the select query and use the output variable in another statement.

sp_executesql扩展存储过程支持输出参数来存储选择查询返回的值，并在另一个语句中使用输出变量。

Following is the example script which shows the usage of the output variable in sp_executesql.

以下是示例脚本，该脚本显示了sp_executesql中输出变量的用法。

DECLARE @ProdNumber nvarchar(50)
 
EXECUTE sp_executesql N' 
		  SELECT  @ProdNumberOUT= ProductNumber
          FROM SalesLT.Product where ProductID = @Pid'
	,N'@Pid varchar(50) ,@ProdNumberOUT nvarchar(25) OUTPUT'
	,@pid = '680'
	, @ProdNumberOUT = @ProdNumber OUTPUT 
 
	select @ProdNumber as ProductNumber

动态SQL中的临时表 (Temp tables in dynamic SQL)

The local temp table created by executing dynamic SQL cannot be accessed outside the execution of dynamic SQL. It throws invalid object error as shown in the below image.

通过执行动态SQL创建的本地临时表不能在执行动态SQL之外访问。它将引发无效的对象错误，如下图所示。

A workaround for this is to create the local temp table outside and use it in the dynamic SQL. Following is the example that demonstrates this scenario.

一种解决方法是在外部创建本地临时表，然后在动态SQL中使用它。以下是演示此方案的示例。

CREATE TABLE #temptable (
	ProductID VARCHAR(50)
	,Name VARCHAR(100)
	,ProductNumber VARCHAR(100)
	)
 
EXECUTE sp_executesql N' INSERT INTO #temptable
		  SELECT ProductID,Name,ProductNumber
          FROM SalesLT.Product where ProductID = @Pid and ProductNumber=@PNumber'
	,N'@Pid varchar(50),@PNumber varchar(50)'
	,@pid = '680'
	,@PNumber = 'FR-R92B-58';
 
SELECT *
FROM #temptable

Please refer to the below image. we can see that the data is inserted in the temp table and can be accessed again.

请参考下图。我们可以看到数据已插入到临时表中，并且可以再次访问。

EXEC命令和sp_executesql扩展存储过程的比较 (Comparison of EXEC command and sp_executesql extended stored procedure)

sp_executesql	EXEC Command
Reuses the cached plan	Generates multiple plans when executed with different parameters
Less prone to SQL Injection	Prone to SQL injection
Supports parameterization	Does not support parameterization
Supports output variable	Output variable is not supported

sp_executesql	EXEC命令
重用缓存的计划	使用不同的参数执行时生成多个计划
不易发生SQL注入	容易进行SQL注入
支持参数化	不支持参数化
支持输出变量	不支持输出变量

结论 (Conclusion)

In this article, we explored how to construct and execute dynamic SQL in SQL Server using the EXEC command and sp_executesql extended stored procedure with different examples. In case you have any questions, please feel free to ask in the comment section below.

在本文中，我们通过不同的示例探讨了如何使用EXEC命令和sp_executesql扩展存储过程在SQL Server中构造和执行动态SQL。如果您有任何疑问，请随时在下面的评论部分中提问。