SQL插入 - 关键字“AS”附近的语法不正确

I was having trouble with creating a trigger to copy data from update/inserted row, so I thought I could do the same thing by adding extra code to a stored procedure.

我在创建从更新/插入行复制数据的触发器时遇到了问题,所以我想我可以通过向存储过程添加额外的代码来做同样的事情。

I keep getting below errors while trying to save it:

在尝试保存时我一直遇到错误:

Msg 156, Level 15, State 1, Procedure usp_updateNexStep, Line 33 [Batch Start Line 7]
Incorrect syntax near the keyword 'AS'.it

消息156,级别15,状态1,过程usp_updateNexStep,第33行[批处理开始第7行]关键字'AS'附近的语法不正确。

Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [updateNexStep]
    @p1 DATETIME,           --follow up date
    @p2 CHAR(2),            -- Next step code
    @p3 VARCHAR(255),       -- comments
    @p4 CHAR(2),            -- last EMCODE
    @p5 SMALLINT,           -- user id of who made the change
    @p6 INT                 -- Record to be updated
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE table1
    SET dFollowUp       = @p1, 
        cNextStepCode   = @p2,
        cComments       = @p3, 
        cEmCode         = @p4, 
        nUserId         = @p5, 
        dUpDated        = GETDATE()
    WHERE nIDNo = @p6;
END

IF (@@ROWCOUNT > 0 ) 
BEGIN
    INSERT INTO table2 AS d
        (d.cTicketNo, d.cSystem, d.cNextStepCode, d.dFollowUp, d.cComments, d.cEmCode, d.nUserId, d.dUpDated)
        (SELECT s.cTicketNo ,s.cSystem ,s.cNextStepCode, s.dFollowUp, s.cComments, s.cEmCode, s.nUserId, s.dUpDated 
         FROM table3 AS s
         WHERE s.nIDNo = @p6);
END

1 个解决方案

#1

Generally, INSERT statment not required AS keyword,

通常,INSERT语句不需要AS关键字,

Remove the alias as bellow:

删除下面的别名:

USE [Reports]
GO
/****** Object:  StoredProcedure [dbo].[usp_updateNexStep]    Script Date: 3/26/2018 1:08:09 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [updateNexStep]
    -- Add the parameters for the stored procedure here
    @p1 DATETIME,           --follow up date
    @p2 CHAR(2),            -- Next step code
    @p3 VARCHAR(255),       -- comments
    @p4 CHAR(2),            -- last EMCODE
    @p5 SMALLINT,           -- user id of who made the change
    @p6 INT                 -- Record to be updated
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    UPDATE table1
    SET dFollowUp       = @p1, 
        cNextStepCode   = @p2,
        cComments       = @p3, 
        cEmCode         = @p4, 
        nUserId         = @p5, 
        dUpDated        = GETDATE()
    WHERE nIDNo = @p6;
END
IF (@@ROWCOUNT > 0 ) 
    BEGIN
        INSERT INTO table2 
        (dcTicketNo ,cSystem, cNextStepCode, dFollowUp, cComments, cEmCode, nUserId, dUpDated)
        (SELECT s.cTicketNo ,s.cSystem ,s.cNextStepCode, s.dFollowUp, s.cComments, s.cEmCode, s.nUserId, s.dUpDated 
            FROM table3 AS s
            WHERE  s.nIDNo = @p6);
    END

#1