动态SQL如何实现复杂查询

动态SQL是一种在运行时构建和执行SQL语句的技术，它允许根据不同的条件或参数生成不同的SQL查询。这在处理复杂查询时非常有用，因为它可以根据用户输入或其他数据源动态地调整查询逻辑。以下是使用动态SQL实现复杂查询的一些常见方法：

1. 使用字符串拼接

最简单的方法是将SQL语句的各个部分拼接成一个完整的字符串。

DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM Users WHERE ';

IF @condition1 = 'active'
    SET @sql += 'Status = ''Active'' AND ';
IF @condition2 IS NOT NULL
    SET @sql += 'Age > ' + CAST(@condition2 AS NVARCHAR) + ' AND ';
IF @condition3 = 'true'
    SET @sql += 'IsActive = 1';

-- 移除最后一个 'AND '
SET @sql = LEFT(@sql, LEN(@sql) - 4);

EXEC sp_executesql @sql;

2. 使用参数化查询

为了防止SQL注入攻击，建议使用参数化查询。

DECLARE @sql NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);

SET @sql = 'SELECT * FROM Users WHERE ';
SET @params = '';

IF @condition1 = 'active'
BEGIN
    SET @sql += 'Status = @status1 AND ';
    SET @params += '@status1 NVARCHAR(50), ';
END
IF @condition2 IS NOT NULL
BEGIN
    SET @sql += 'Age > @age2 AND ';
    SET @params += '@age2 INT, ';
END
IF @condition3 = 'true'
BEGIN
    SET @sql += 'IsActive = @isActive AND ';
    SET @params += '@isActive BIT, ';
END

-- 移除最后一个 'AND '
SET @sql = LEFT(@sql, LEN(@sql) - 4);

-- 添加参数定义
SET @sql = @sql + ' ORDER BY UserName';

-- 执行查询
EXEC sp_executesql @sql, @params, @status1 = 'Active', @age2 = 30, @isActive = 1;

3. 使用存储过程

存储过程可以包含动态SQL，并且可以通过参数传递条件。

CREATE PROCEDURE GetUsers
    @condition1 NVARCHAR(50) = NULL,
    @condition2 INT = NULL,
    @condition3 BIT = NULL
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = 'SELECT * FROM Users WHERE ';

    IF @condition1 IS NOT NULL
        SET @sql += 'Status = @status1 AND ';
    IF @condition2 IS NOT NULL
        SET @sql += 'Age > @age2 AND ';
    IF @condition3 IS NOT NULL
        SET @sql += 'IsActive = @isActive AND ';

    -- 移除最后一个 'AND '
    SET @sql = LEFT(@sql, LEN(@sql) - 4);

    -- 添加参数定义
    SET @sql = @sql + ' ORDER BY UserName';

    -- 执行查询
    EXEC sp_executesql @sql, N'@status1 NVARCHAR(50), @age2 INT, @isActive BIT', @status1, @condition2, @condition3;
END

4. 使用ORM工具

许多ORM（对象关系映射）工具支持动态查询构建，例如Entity Framework、Dapper等。

using (var context = new MyDbContext())
{
    var query = context.Users.AsQueryable();

    if (!string.IsNullOrEmpty(condition1))
        query = query.Where(u => u.Status == condition1);
    if (condition2.HasValue)
        query = query.Where(u => u.Age > condition2.Value);
    if (condition3)
        query = query.Where(u => u.IsActive);

    var results = query.ToList();
}

注意事项

1. SQL注入：始终使用参数化查询来防止SQL注入攻击。
2. 性能：动态SQL可能会导致查询计划缓存失效，影响性能。尽量减少动态SQL的使用。
3. 可读性：复杂的动态SQL可能会降低代码的可读性和维护性。尽量保持代码简洁明了。

通过以上方法，你可以灵活地使用动态SQL来实现复杂的查询逻辑。