能否通过变量向substring函数传递列名？SQL存储过程动态列名问题

T-SQL不支持直接用字符串变量作为列名引用，你当前写法中SUBSTRING(@ColumnName, 1, 2)实际是对@ColumnName变量存储的字符串本身做截取，而非读取表中对应列的行值，因此无法达到筛选效果。同时你原代码还存在事务名不统一的问题：开启事务用的名称是EmptyTable，回滚时用的是SvuotaTabella，执行报错时会触发回滚失败。

使用动态SQL拼接执行语句，同时增加列名校验避免SQL注入风险，修改后的存储过程如下：

ALTER PROCEDURE SP_SvuotaTabella
    @Year INT,
    @ColumnName VARCHAR(32),
    @Dimension INT
AS
BEGIN
    -- 先校验传入的列名是否真实存在于TestDelete表，防止SQL注入
    IF NOT EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = 'TestDelete' 
        AND TABLE_SCHEMA = 'dbo' 
        AND COLUMN_NAME = @ColumnName
    )
    BEGIN
        RAISERROR('传入的列名不存在', 16, 1)
        RETURN
    END

    DECLARE @Sql NVARCHAR(MAX)
    -- 拼接动态SQL，列名用QUOTENAME包裹转义，参数通过sp_executesql传递避免注入
    SET @Sql = N'
    BEGIN TRY
        BEGIN TRAN EmptyTable

        DECLARE @CurrDimension INT = @DimensionInput
        WHILE @CurrDimension <> 0
        BEGIN
            DELETE TOP (@CurrDimension) 
            FROM [dbo].[TestDelete]
            WHERE SUBSTRING(' + QUOTENAME(@ColumnName) + N', 1, 2) = CAST(@YearInput AS VARCHAR(2))

            SET @CurrDimension = @@rowcount 
        END

        COMMIT TRAN EmptyTable
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION EmptyTable;

        SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_STATE() AS ErrorState,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage;
    END CATCH
    '

    -- 执行动态SQL，传递参数
    EXEC sp_executesql @Sql,
        N'@YearInput INT, @DimensionInput INT',
        @YearInput = @Year,
        @DimensionInput = @Dimension
END

• 增加列名校验逻辑：先判断传入的@ColumnName是否是TestDelete表的真实列，避免恶意传入非法字符串导致SQL注入
• 列名拼接时用QUOTENAME()函数包裹，自动对特殊字符转义，进一步规避注入风险
• 动态SQL的参数通过sp_executesql的参数化接口传递，不直接拼接数值，安全性更高
• 统一了事务名称，同时增加@@TRANCOUNT判断，避免无事务可回滚的报错
• 新增CAST(@YearInput AS VARCHAR(2))做类型转换，避免数值和字符串比较时的隐式转换问题

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