动态SQL查询中按日期范围返回数据的代码修正请求

我帮你梳理了代码里的几个核心问题，现在给出修正后的完整版本，同时解释关键修改点：

主要问题分析 #

1. 动态SQL变量作用域冲突：动态SQL运行在独立的执行上下文里，直接写@FromDate/@ToDate会被当成未声明的变量，必须通过参数传递或者安全拼接的方式引入变量值。
2. 嵌套查询语法错误：原动态SQL里的SELECT嵌套括号混乱，导致AS附近触发语法报错。
3. 变量被意外覆盖：游标FETCH INTO时，用表的创建日期覆盖了外部声明的日期范围变量@FromDate/@ToDate，逻辑完全混乱。
4. 日期筛选逻辑错误：t.create_date = @FromDate AND t.create_date = @ToDate几乎永远不会匹配到数据，应该改成范围判断。

修正后的完整代码 #

DECLARE @DatabaseName VARCHAR(100)
DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(100)
DECLARE @ColumnName VARCHAR(100)
DECLARE @FullyQualifiedTableName VARCHAR(500)
DECLARE @DataType VARCHAR(50)
DECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME
Declare @MySchemaName varchar(100) = 'MySystem%'

SET @FromDate = '16 May 2018'
SET @ToDate = '23 May 2018'

-- 注：该CTE目前未在后续逻辑中使用，若为遗留代码可考虑删除或补充使用逻辑
;WITH dateRange AS (
    SELECT [Date] = DATEADD(dd, 1, DATEADD(dd, -1,@FromDate))
    WHERE DATEADD(dd, 1, @FromDate) < DATEADD(dd, 1,@ToDate)
)
SELECT @ColumnName = COALESCE(@ColumnName, '[') + CONVERT(VARCHAR, [Date], 111) + '],['
FROM dateRange OPTION (maxrecursion 0)
SET @ColumnName = SUBSTRING(@ColumnName, 1, LEN(@ColumnName)-2)
SELECT @ColumnName

-- 创建临时表存储结果
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (
    DatabaseName VARCHAR(100)
    ,SchemaName VARCHAR(100)
    ,TableName VARCHAR(100)
    ,ColumnName VARCHAR(100)
    ,ColumnDataType VARCHAR(50)
    ,StartDate Datetime2(7)
    ,EndDate Datetime2(7)
    ,TotalRowCount int
    ,NullCount int
    ,InvalidCount int
    ,ValidityCheck VARCHAR(25)
)

---------------------------------------------------------DateOfBirth----------------------------------------------------------------
-- 新增变量存储表的创建日期，避免覆盖外部日期范围变量
DECLARE @TableCreateDate DATETIME

DECLARE Cur CURSOR FOR
SELECT 
    DB_Name() AS DatabaseName
    ,s.[name] AS SchemaName
    ,t.[name] AS TableName
    ,c.[name] AS ColumnName
    ,'[' + DB_Name() + '].[' + s.name + '].[' + T.NAME + ']' AS FullQualifiedTableName
    ,d.[name] AS DataType
    ,t.[create_date] AS TableCreateDate
FROM sys.schemas s
INNER JOIN sys.tables t ON s.schema_id = t.schema_id
INNER JOIN sys.columns c ON t.object_id = c.object_id
INNER JOIN sys.types d ON c.user_type_id = d.user_type_id
WHERE s.name like @MySchemaName 
AND (c.name LIKE '%dob%' or c.name like '%birth%')
-- 修正表创建日期的范围筛选逻辑
AND t.create_date BETWEEN @FromDate AND @ToDate
AND is_identity = 0

OPEN Cur
FETCH NEXT FROM Cur INTO 
    @DatabaseName
    ,@SchemaName
    ,@TableName
    ,@ColumnName
    ,@FullyQualifiedTableName
    ,@DataType
    ,@TableCreateDate

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 使用参数化动态SQL，解决作用域问题同时防止SQL注入
    DECLARE @SQL NVARCHAR(MAX) = N'
        SELECT 
            @DBName AS DatabaseName,
            @SchName AS SchemaName,
            @TblName AS TableName,
            @ColName AS ColumnName,
            @DataType AS ColumnDataType,
            @FromDt AS StartDate,
            @ToDt AS EndDate,
            (SELECT COUNT(*) FROM ' + @FullyQualifiedTableName + ') AS TotalRowCount,
            (SELECT CAST(SUM(CASE WHEN ' + QUOTENAME(@ColumnName) + ' IS NULL THEN 1 ELSE 0 END) AS INT) FROM ' + @FullyQualifiedTableName + ') AS NullCount,
            (SELECT SUM(CASE WHEN ' + QUOTENAME(@ColumnName) + ' IS NOT NULL AND (' + QUOTENAME(@ColumnName) + ' <= ''1900-01-01'' OR ' + QUOTENAME(@ColumnName) + ' > GETDATE()) THEN 1 ELSE 0 END) FROM ' + @FullyQualifiedTableName + ') AS InvalidCount,
            ''DateOfBirth'' AS ValidityCheck
    '

    -- 通过sp_executesql传递参数，确保变量值正确传入动态SQL
    INSERT INTO #Results
    EXEC sp_executesql 
        @SQL,
        N'@DBName VARCHAR(100), @SchName VARCHAR(100), @TblName VARCHAR(100), @ColName VARCHAR(100), @DataType VARCHAR(50), @FromDt DATETIME, @ToDt DATETIME',
        @DBName = @DatabaseName,
        @SchName = @SchemaName,
        @TblName = @TableName,
        @ColName = @ColumnName,
        @DataType = @DataType,
        @FromDt = @FromDate,
        @ToDt = @ToDate

    FETCH NEXT FROM Cur INTO 
        @DatabaseName
        ,@SchemaName
        ,@TableName
        ,@ColumnName
        ,@FullyQualifiedTableName
        ,@DataType
        ,@TableCreateDate
END

CLOSE Cur
DEALLOCATE Cur

SELECT * FROM #Results ORDER BY tableName DESC
--DROP TABLE #Results

关键修改说明 #

1. 避免变量覆盖：新增@TableCreateDate存储表的创建日期，不再复用外部的日期范围变量，彻底解决变量值被意外覆盖的问题。
2. 参数化动态SQL：使用sp_executesql替代直接字符串拼接，既解决了动态SQL的变量作用域问题，又能有效防止SQL注入，代码可读性和安全性大幅提升。
3. 修复语法错误：重构了动态SQL的查询结构，删除多余的嵌套SELECT括号，解决AS附近的语法报错。
4. 修正日期筛选逻辑：将t.create_date = @FromDate AND t.create_date = @ToDate改为t.create_date BETWEEN @FromDate AND @ToDate，符合日期范围筛选的实际需求。
5. 列名安全处理：对列名使用QUOTENAME函数，避免列名包含特殊字符时触发语法错误。

内容的提问来源于stack exchange，提问作者Mari