SQLSERVER：动态SQL

--SqlServer动态Sql
--动态SQL是指在运行时构造并执行的sql语句。这种技术在sqlserver中非常有用，尤其
--是在需要编写灵活且可适应不同情况的代码时。动态sql可以用来创建通用的存储过程，
--执行复杂的查询或者在运行时根据特定条件构建SQL语句。

--优势与风险：
--动态SQL的主要优势在于其灵活性，它允许开发者编写能够适应不同输入和条件的代码。
--然而，使用动态SQL也有风险，最主要的风险是SQL注入攻击，这是由于动态构造的SQL语句
--可能会无意中插入恶意的SQL代码

--安全实践
--为了安全地使用动态SQL,应始终：
--* 使用参数化查询，避免SQL注入
--对输入进行验证。
--最小化使用动态SQL,只在必要时使用。

--示例脚本

-- 创建Employees表
CREATE TABLE Employees (
EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Position VARCHAR(50),
DepartmentID INT
);

-- 插入Employees表数据
INSERT INTO Employees (FirstName, LastName, Position, DepartmentID)
VALUES ('Jane', 'Doe', 'Manager', 1),
('John', 'Smith', 'Developer', 2),
('Alice', 'Johnson', 'Developer', 2);

INSERT INTO Employees (FirstName, LastName, Position, DepartmentID)
VALUES ('Jane1', 'Doe1', 'Manager', 1),
('John1', 'Smith1', 'Developer', 2),
('Alice1', 'Johnson1', 'Developer', 2),
('Jane2', 'Doe2', 'Manager', 1),
('John2', 'Smith2', 'Developer', 2),
('Alice2', 'Johnson2', 'Developer', 2),
('Jane3', 'Doe3', 'Manager', 1),
('John3', 'Smith3', 'Developer', 2),
('Alice3', 'Johnson3', 'Developer', 2),
('Jane4', 'Doe4', 'Manager', 1),
('John4', 'Smith4', 'Developer', 2),
('Alice4', 'Johnson4', 'Developer', 2);

--示例1：基本的动态SQL执行
DECLARE @TableName NVARCHAR(128) ='Employees';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL='SELECT * FROM '+ QUOTENAME(@TableName);
Exec sp_executesql @SQL;

--示例2：使用参数的动态SQL

DECLARE @EmployeeID INT =1;
DECLARE @SQL1 NVARCHAR(MAX);

SET @SQL1 =N'SELECT * FROM Employees WHERE EmployeeID =@EmpID';
EXEC sp_executesql @SQL1,N'@EmpID INT',@EmpID=@EmployeeID;

--示例3：动态排序和分页

DECLARE @SortColumn NVARCHAR(128) ='FirstName';
DECLARE @SortOrder NVARCHAR(4) ='ASC';
DECLARE @PageSize INT =10;
DECLARE @PageNumber INT =1;
DECLARE @SQL2 NVARCHAR(MAX);

SET @SQL2 = 'SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY '+ QUOTENAME(@SortColumn)+' ' +@SortOrder + ') AS RowNum,
* FROM Employees
) AS MyDerivedTable
WHERE MyDerivedTable.RowNum BETWEEN '+ CAST((@PageNumber -1)* @PageSize +1 AS NVARCHAR)+
' AND ' + CAST(@PageNumber * @PageSize AS NVARCHAR);
EXEC sp_executesql @SQL2;

--示例4：动态创建和执行存储过程
DECLARE @ProcedureName NVARCHAR(128) ='usp_GetEmployeeDetails';
DECLARE @SQL3 NVARCHAR(MAX);

SET @SQL3 = N'CREATE PROCEDURE ' +QUOTENAME(@ProcedureName) +'
@EmployeeID INT
AS
BEGIN
SELECT * FROM Employees WHERE EmployeeID=@EmployeeID;
END';
EXEC(@SQL3);

--现在可以执行新创建的存储过程
EXEC usp_GetEmployeeDetails @EmployeeID=1;

--总结：动态SQL是SQL Server中一个强大的工具，它可以提高代码的灵活性和适应性。然而，
--使用动态sql需要谨慎，以避免潜在的安全风险，如SQL注入。通过使用参数化查询和对输入进行
--验证，可以确保使用动态SQL的安全性。以上示例提供了一些基本的动态SQL使用方法，但在实际应用中
--可能要根据特定的业务逻辑和需求进行调整。