动态语句 sqlserver

语句 sqlserver"/>

动态语句 sqlserver

	EXEC sp_executesql @DynamicSQL, N'@FirstName NVARCHAR(50), @LastName NVARCHAR(50)', @FirstName, @LastName

在EXEC sp_executesql语句中，后面的参数需要按特定顺序传递。这些参数的顺序如下：

1.第一个参数是动态SQL语句本身，通常是一个NVARCHAR(MAX)类型的变量，包含要执行的SQL查询。

2.第二个参数是定义动态SQL语句中包含的参数的列表。这是一个NVARCHAR(MAX)类型的字符串，通常以N’@参数1 数据类型, @参数2 数据类型, …'的形式给出，其中参数1、参数2等是在动态SQL语句中引用的参数的名称，而数据类型是这些参数的数据类型。

3.接下来的参数是对应于上述参数列表的实际参数值，按照与参数列表中相同的顺序传递。

接下来是几个由易到难的例子：

Level1：简单的筛选

• Level1：简单的筛选

  假设有一个名为"Employees"的表，其中包含员工的信息，包括"FirstName"和"LastName"字段。你可以使用动态SQL来构建一个查询，根据用户输入的条件来筛选员工记录：

  DECLARE @DynamicSQL NVARCHAR(MAX)
  DECLARE @FirstName NVARCHAR(50)
  DECLARE @LastName NVARCHAR(50)-- 设置用户提供的搜索条件
  SET @FirstName = 'John'
  SET @LastName = 'Doe'-- 构建动态SQL语句
  SET @DynamicSQL = 'SELECT * FROM Employees WHERE 1=1' -- 1=1用于确保始终有一个条件，以便后续添加其他条件-- 根据用户输入添加条件
  IF @FirstName IS NOT NULLSET @DynamicSQL = @DynamicSQL + ' AND FirstName = @FirstName' --注意：条件加空格IF @LastName IS NOT NULLSET @DynamicSQL = @DynamicSQL + ' AND LastName = @LastName'-- 执行动态SQL语句
  EXEC sp_executesql @DynamicSQL, N'@FirstName NVARCHAR(50), @LastName NVARCHAR(50)', @FirstName, @LastName

  我们首先声明了一个变量@DynamicSQL，它将用于构建动态SQL语句。然后，我们根据用户提供的条件（FirstName和LastName）构建SQL查询。最后，我们使用sp_executesql存储过程执行动态SQL语句，并传递了用户提供的参数。

’ AND FirstName = @FirstName’ --注意：条件加空格 AND 前加空格！！！！！！！

Level 2:+ order by

• Level 2:+ order by
  假设你有一个名为"Products"的表，其中包含产品的信息，包括"ProductName"和"Price"字段。你可以使用动态SQL来根据用户选择的排序条件对产品进行排序。

  DECLARE @DynamicSQL NVARCHAR(MAX)
  DECLARE @SortColumn NVARCHAR(50)
  DECLARE @SortDirection NVARCHAR(4)-- 设置用户提供的排序条件
  SET @SortColumn = 'ProductName'
  SET @SortDirection = 'ASC' -- ASC表示升序，DESC表示降序-- 构建动态SQL语句
  SET @DynamicSQL = 'SELECT * FROM Products'-- 添加动态ORDER BY子句
  SET @DynamicSQL = @DynamicSQL + ' ORDER BY ' + @SortColumn + ' ' + @SortDirection --注意 这里有空格-- 执行动态SQL语句
  EXEC sp_executesql @DynamicSQL

  SET @DynamicSQL = @DynamicSQL + ’ ORDER BY ’ + @SortColumn + ’ ’ + @SortDirection --注意 这里有空格 ’ ‘ 且参数有加号！！！！！

Level 2.5 参数化查询

• Level 2.5 参数化查询

  Q：为什么有些参数直接嵌入查询，有些却需要使用加号 + 来构建动态SQL查询呢？
  A：主要原因是数据类型不同。

  1.参数值是文本（字符型数据）： 如果你的参数值是文本，如城市名称或客户名称，你可以直接在SQL语句中嵌入它，无需使用加号 + 连接。例如：

  DECLARE @City NVARCHAR(50)
  SET @City = 'New York'-- 直接嵌入文本参数
  SELECT CustomerName, PhoneNumber FROM Customers WHERE City = @City

  在这种情况下，文本参数可以直接放在查询语句中，因为它们与SQL语法兼容。

  2.参数值是标识符（列名等）： 如果你的参数值是标识符，如列名，你需要使用动态SQL来构建查询，因为直接嵌入标识符是不允许的。在这种情况下，你需要使用加号 + 来动态构建查询字符串，确保标识符被正确引用。例如：

  DECLARE @ColumnName NVARCHAR(50)
  SET @ColumnName = 'CustomerName'-- 构建动态SQL查询，引用列名
  DECLARE @SqlStatement NVARCHAR(MAX)
  SET @SqlStatement = 'SELECT ' + QUOTENAME(@ColumnName) + ' FROM Customers'-- 执行动态SQL查询
  EXEC sp_executesql @SqlStatement

  这是因为直接在SQL语句中放置列名是不允许的，你需要使用特定函数（如QUOTENAME）来确保列名被正确引用，避免语法错误。

  总之，如何构建参数化查询取决于参数的数据类型和用途。字符型参数可以直接嵌入查询，而标识符参数需要使用动态SQL构建查询。

Level 3:先order by +插入

• Level 3:先order by +插入
  假设你有一个名为"SourceTable"的表，它包含要排序并插入的数据，以及一个名为"TableA"的目标表，你要将排序后的数据插入到"TableA"中。

  DECLARE @DynamicSQL NVARCHAR(MAX)-- 构建动态SQL查询以排序数据
  SET @DynamicSQL = 'SELECT * FROM SourceTable ORDER BY ColumnToOrderBy'-- 执行动态SQL查询并插入到TableA
  INSERT INTO TableA
  EXEC sp_executesql @DynamicSQL

Level 4：生成前20个数据

• Level 4：生成前20个数据

  假设你有一个名为"SourceTable"的表，它包含要排序并插入的数据，以及一个名为"TableA"的目标表，你要将排序后的前20行数据插入到"TableA"中。

  DECLARE @DynamicSQL NVARCHAR(MAX)
  DECLARE @SortColumn NVARCHAR(50)-- 设置用户提供的排序列
  SET @SortColumn = 'ColumnNameToSortBy'-- 构建动态SQL查询以排序数据并选择前20行
  SET @DynamicSQL = 'INSERT INTO TableA (Column1, Column2, Column3) -- 列出要插入的目标表的列SELECT Column1, Column2, Column3 -- 列出要选择的列FROM (SELECT Column1, Column2, Column3, ROW_NUMBER() OVER (ORDER BY ' + @SortColumn + ') AS RowNumFROM SourceTable) AS RankedDataWHERE RowNum <= 20'-- 执行动态SQL查询
  EXEC sp_executesql @DynamicSQL

  我们首先声明一个变量@DynamicSQL，然后构建一个动态SQL查询。此查询使用ROW_NUMBER()窗口函数在"SourceTable"表中排序数据，按照"YourColumn"列的顺序，并为每行分配一个行号。然后，我们选择前20行的数据并将它们插入到"TableA"表中。

  Level 5：存储过程中 参数是变量

• Level 5：存储过程中 参数是变量
  1.可以直接用，但不推荐。

  CREATE PROCEDURE MyDynamicSQLProcedure@SortColumn NVARCHAR(50),@TableAName NVARCHAR(50)
  AS
  BEGINDECLARE @DynamicSQL NVARCHAR(MAX)-- 构建动态SQL查询以排序数据并选择前20行SET @DynamicSQL = 'INSERT INTO ' + @TableAName + ' (Column1, Column2, Column3) -- 列出要插入的目标表的列SELECT Column1, Column2, Column3 -- 列出要选择的列FROM (SELECT Column1, Column2, Column3, ROW_NUMBER() OVER (ORDER BY ' + @SortColumn + ') AS RowNumFROM SourceTable) AS RankedDataWHERE RowNum <= 20'-- 执行动态SQL查询EXEC sp_executesql @DynamicSQL
  END

  存储过程"MyDynamicSQLProcedure"定义了两个参数： @SortColumn和@TableAName。这些参数在存储过程内部用于构建动态SQL查询，而不需要在sp_executesql中重复定义参数的数据类型，（例如 EXEC MyDynamicSQLProcedure ‘YourColumn’, ‘TableA’，）但不推荐，因为会造成 SQL 注入，所以最好写全。

小tips：
SQL注入（SQL Injection）是一种常见的计算机安全漏洞，它允许攻击者向应用程序的数据库中插入恶意的SQL查询。这种漏洞通常出现在未正确验证用户输入或动态生成SQL查询的应用程序中。攻击者可以通过恶意构造的输入数据，利用SQL注入漏洞来执行未经授权的数据库操作或访问敏感信息。SQL注入可能会导致以下问题：
1.数据泄漏：攻击者可以通过SQL注入查询敏感数据，如用户凭证、信用卡信息、个人身份信息等。
2.数据篡改：攻击者可以修改数据库中的数据，例如删除、修改或添加记录。
3.拒绝服务：攻击者可以执行恶意SQL查询，导致数据库性能下降或崩溃，从而影响应用程序的可用性。

  	 SQL注入通常发生在应用程序未正确验证和处理用户输入的情况下。攻击者会在应用程序的输入字段中插入恶意的SQL代码，以尝试绕过应用程序的身份验证和访问数据库。为了防止SQL注入，开发人员应采取以下措施：1.使用参数化查询：使用参数化查询可以防止用户输入被解释为SQL代码。参数化查询将用户提供的输入视为数据值，而不是SQL代码的一部分。2.输入验证：在接受用户输入之前，进行验证和过滤，以确保输入数据符合预期格式和范围。不信任的输入应该被拒绝。3.最小权限原则：数据库用户和应用程序应该被分配最小必需的权限，以限制对数据库的不当访问。4.错误处理：避免将详细的错误消息暴露给用户，因为这可能会提供攻击者有关数据库结构的信息。5.安全开发实践：遵循安全开发实践，包括定期审查代码以查找潜在的SQL注入漏洞。6.通过采取这些预防措施，可以减少SQL注入的风险，提高应用程序和数据库的安全性。

2.参数写全的存储过程

CREATE PROC CSP_Top20@URID INT, @BYEAR SMALLDATETIME       , @CURRENCY INT              , @SortColumn NVARCHAR(500)  -- 排序字段 , @SortDirection NVARCHAR(4) -- 排序方向 ASC-升序|DESC-降序
AS 
begin     DECLARE @DynamicSQL NVARCHAR(MAX)DELETE FROM CTop20 WHERE URID = @URIDSET @DynamicSQL = 'SELECT TOP 20 @URID, ROW_NUMBER() OVER (ORDER BY ' + @SortColumn + ' ' + @SortDirection + ') AS RowNum,coloumn1,coloumn2,coloumn3FROM CVW_Top20WHERE 1=1'if @BYEAR IS NOT NULL SET @DynamicSQL = @DynamicSQL + ' AND DATEDIFF(YEAR, BYear, @BYEAR) = 0'if @CURRENCY IS NOT NULL SET @DynamicSQL = @DynamicSQL + ' AND Currency_ID = @CURRENCY'INSERT INTO CTop20 (URID, RowNum, coloumn1,coloumn2,coloumn3 )EXEC sp_executesql @DynamicSQL, N'@CURRENCY INT, @BYEAR SMALLDATETIME, @URID INT', @CURRENCY, @BYEAR, @URID--这里参数都写全了SELECT * FROM CTop20 WHERE URID = @URID end