创建查询含指定列的表详情存储过程时遇变量未声明错误求助

错误原因 #

• 动态SQL中使用了变量@columnName，但调用sp_executesql时未传递该参数，导致SQL引擎无法识别变量。
• 原代码通过INFORMATION_SCHEMA.COLUMNS循环拼接SQL的逻辑完全冗余，会生成重复查询语句。
• DATA_TYPE = c.name是错误写法，c.name是列名而非数据类型，需关联系统表获取正确类型。
• 缺少外键信息查询逻辑，不符合需求中显示外键的要求。

修正后的存储过程代码 #

CREATE OR ALTER PROCEDURE GetColumnDetails 
    (@columnName NVARCHAR(128)) -- 列名最长128字符，无需使用MAX
AS
BEGIN
    SET NOCOUNT ON; -- 屏蔽额外的计数返回信息

    DECLARE @sql NVARCHAR(MAX);

    SET @sql = N'
    SELECT 
        COLUMN_NAME = @colName,
        TABLE_NAME = t.name,
        DATA_TYPE = ty.name,
        IS_NULLABLE = CASE WHEN c.is_nullable = 1 THEN ''Yes'' ELSE ''No'' END,
        IS_PRIMARY_KEY = CASE WHEN ic.column_id IS NOT NULL THEN ''Yes'' ELSE ''No'' END,
        IS_FOREIGN_KEY = CASE WHEN fk.parent_column_id IS NOT NULL THEN ''Yes'' ELSE ''No'' END,
        REFERENCED_TABLE = OBJECT_NAME(fk.referenced_object_id),
        REFERENCED_COLUMN = col_ref.name
    FROM sys.tables t
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id AND c.user_type_id = ty.user_type_id
    LEFT JOIN sys.indexes i ON t.object_id = i.object_id AND i.is_primary_key = 1
    LEFT JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id AND c.column_id = ic.column_id
    LEFT JOIN sys.foreign_key_columns fk ON t.object_id = fk.parent_object_id AND c.column_id = fk.parent_column_id
    LEFT JOIN sys.columns col_ref ON fk.referenced_object_id = col_ref.object_id AND fk.referenced_column_id = col_ref.column_id
    WHERE c.name = @colName
    ORDER BY t.name;';

    -- 传递参数执行动态SQL，避免变量未声明错误与SQL注入风险
    EXEC sp_executesql @sql, 
        N'@colName NVARCHAR(128)', 
        @colName = @columnName;
END;
GO

-- 测试调用示例
EXEC GetColumnDetails 'order_id';

关键修改说明 #

• 参数传递优化：通过sp_executesql的参数传递机制传入变量，既解决未声明错误，又规避SQL注入风险。
• 移除冗余逻辑：直接构建单条查询语句，删除不必要的循环拼接，提升执行效率与可读性。
• 修正数据类型获取：关联sys.types表获取正确的数据类型名称。
• 补充外键信息：通过sys.foreign_key_columns关联查询外键关联的表与列信息，满足需求。
• 参数类型优化：将列名参数改为NVARCHAR(128)，匹配SQL Server列名的最大长度限制。

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