SQL Server存储过程执行时出现无效列错误求助

核心错误原因：动态SQL拼接时，字符串类型的@tuwi未添加单引号，数据库将Z303400识别为列名而非字符串参数，触发「无效列名」错误。

修复步骤 #

• 修正动态SQL参数传递方式：避免直接拼接字符串参数，改用sp_executesql参数化传递，既解决语法问题又防范SQL注入
• 修正拼写错误：将Canada分支的wellbehaviour_Canda改为wellbehaviour_Canada，避免找不到对应表
• 移除无效代码：删除未使用且存在类型不匹配错误的@number变量
• 增加参数校验：添加@SchemaName非空判断，防止传入未定义的国家参数生成无效SQL
• 规范表名拼接：使用QUOTENAME函数包裹表名，避免表名含特殊字符时出现语法错误

修复后的存储过程代码 #

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_wellbehavior]
(
    @tuwi nvarchar(50),
    @country varchar(50)
)
AS
BEGIN
    SET NOCOUNT ON
    declare @query as nvarchar(1800)
    declare @SchemaName as varchar(100)
    declare @well_name as nvarchar(100)

    set @SchemaName = case @country
        when 'US' Then  'wellbehaviour_US'
        when 'Canada' Then  'wellbehaviour_Canada'
        when 'Australia' Then  'wellbehaviour_Australia'
        when 'Japan' Then 'wellbehaviour_Japan'
        when 'China' Then 'wellbehaviour_China'    
    END

    IF @SchemaName IS NULL
    BEGIN
        RAISERROR('不支持的国家参数', 16, 1)
        RETURN
    END
    
    set @well_name = (select well_name from well.well_mapping where index_id = @tuwi)
    set @query = N'SELECT [date], [asset], [index_id], [well_name], [open], [gas], [oil], [water], [whp], [wht], [bhp], 
    [bht], [cluster], [flag], [display_cluster] FROM datascience.' + QUOTENAME(@SchemaName) + N' where well_view_id = @tuwi'
    
    Execute sp_executesql @query, N'@tuwi nvarchar(50)', @tuwi = @tuwi
END

验证执行 #

使用原调用语句即可正常运行：

DECLARE @RC int
DECLARE @tuwi nvarchar(50)
DECLARE @country varchar(50)

EXECUTE @RC = [dbo].[sp_wellbehavior]
@tuwi = 'Z303400', @country ='Japan'

GO

内容的提问来源于stack exchange，提问作者Anil Kumar Ulchala