时间戳

首页 > 解决方案 > SQL Server 存储过程，语法无效

sql-server - SQL Server 存储过程，语法无效

问题描述

我不是一个真正的 SQL 人，但我必须更改一个存储过程，我必须在存储过程中添加一个 column_name, CrossbarRouter。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [StateMachine].[UpdateSnapshots] 
    @SystemName[NVARCHAR](128), 
    @IntrestingEvents VARCHAR(128), 
    @StateMachine_JSON [NVARCHAR](MAX),
    @StateMachine_Object [NVARCHAR](MAX),
    @CrossbarRouter VARCHAR(128)
AS
    IF EXISTS (SELECT * FROM [DVMT30_EVENT_PROCESSOR].[StateMachine].[Snapshots] 
               WHERE SystemName = @SystemName)
        UPDATE [DVMT30_EVENT_PROCESSOR].[StateMachine].[Snapshots]
        SET IntrestingEvents = @IntrestingEvents, 
            StateMachine_JSON = @StateMachine_JSON, 
            StateMachine_Object = @StateMachine_Object, 
            CrossbarRouter = @CrossbarRouter
        WHERE SystemName = @SystemName;
    ELSE
        INSERT INTO [DVMT30_EVENT_PROCESSOR].[StateMachine].[Snapshots] (SystemName, IntrestingEvents, StateMachine_JSON, StateMachine_Object, CrossbarRouter)
        VALUES (@SystemName, @IntrestingEvents, @StateMachine_JSON, @StateMachine_Object, @CrossbarRouter);

GO

错误

12:40:04 PMStarted executing query at Line 1
Commands completed successfully.
12:40:04 PMStarted executing query at Line 2
Commands completed successfully.
12:40:04 PMStarted executing query at Line 4

消息 207，级别 16，状态 1，过程 UpdateSnapshots，第 9 行
无效的列名称“CrossbarRouter”。

在此之前，我确实对表格进行了更改，就像这样。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [StateMachine].[Snapshots]
(
    [ID] [INT] IDENTITY(1,1) NOT NULL,
    [SystemName] [NVARCHAR](128) NOT NULL,
    [IntrestingEvents] [VARCHAR](128) NOT NULL,
    [StateMachine_JSON] [NVARCHAR](MAX) NOT NULL,
    [StateMachine_Object] [NVARCHAR](MAX) NOT NULL,
    [CrossbarRouter] [VARCHAR](1) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

标签： sql-server

解决方案

您没有使用 create table 语句更改表。您可以使用 alter table add column 语句或通过删除表然后创建表来将列添加到现有表。对现有表的创建语句会导致错误。

推荐阅读