动态语句 sqlserver

阿崔学不会

已于 2023-10-20 15:57:40 修改

阅读量293

点赞数

文章标签： sqlserver 数据库网络

于 2023-10-20 15:48:59 首次发布

本文链接：https://blog.csdn.net/weixin_45042852/article/details/133946691

版权

	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 NULL


    SET @DynamicSQL = @DynamicSQL + ' AND FirstName = @FirstName' --注意：条件加空格

IF @LastName IS NOT NULL
    SET @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 RowNum
        FROM SourceTable
    ) AS RankedData
    WHERE 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
BEGIN
    DECLARE @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 RowNum
            FROM SourceTable
        ) AS RankedData
        WHERE 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 = @URID

	SET @DynamicSQL = '
    SELECT TOP 20 @URID, ROW_NUMBER() OVER (ORDER BY ' + @SortColumn + ' ' + @SortDirection + ') AS RowNum,
           coloumn1,coloumn2,coloumn3
        FROM CVW_Top20
        WHERE 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