sql-server - '0' 附近的语法不正确。 (微软 SQL 服务器)

Question

当我执行下面的这个过程时，它返回给我 Incorrect syntax near '0'。我不知道为什么。

错误定位于 Msg 102，Level 15，State 1，Line 43。你能告诉我哪里出了问题吗？感谢您的提前。

CREATE PROCEDURE [dbo].[PV_SaveCategory] 
(
    @AccountID              BIGINT,
    @LocationID             BIGINT,
    @CategoryName           NVARCHAR(100),
    @ParentID               BIGINT,
    @ExternalCategoryID     BIGINT
)
AS
BEGIN
DECLARE @ExecSQL NVARCHAR(MAX)

SET @ExecSQL = N'
DECLARE @CategoryID INT, @Level INT
SELECT @CategoryID = Id FROM [DB].[dbo].[Category_' + CONVERT(NVARCHAR, @AccountID, 0) + '] WHERE @ExternalCategoryID = ExternalID
SELECT @Level = Level+1 FROM [DB].[dbo].[Category_' + CONVERT(NVARCHAR, @AccountID, 0) + '] WHERE @ParentID = Id

IF @CategoryID IS NULL
BEGIN
INSERT
    INTO [DB].[dbo].[Category_' + CONVERT(NVARCHAR, @AccountID, 0) + ']
            (
                 Name
                ,LocationID
                ,IsDeleted
                ,Level
                ,ParentCategoryID
                ,Main
                ,SubCategory1
                ,SubCategory2
                ,SubCategory3
                ,SubCategory4
                ,CategoryPath
                ,ExternalID
            )
            VALUES
            (
                @CategoryName,
                @LocationID,
                0,
                CASE WHEN @ParentID IS NULL THEN 0 ELSE @Level END,
                @ParentID,
                0,
                1,
                2,
                4,
                ''test'',
                @ExternalCategoryID
            )
    END
ELSE
    BEGIN
        UPDATE [DB].[dbo].[Category_' + CONVERT(NVARCHAR, @AccountID, 0) + '] SET
                Level = CASE WHEN @ParentID IS NULL THEN 0 ELSE @Level END,
                0,
                1,
                2,
                3,
                4,
                ''test2'',
            Where @CategoryID = Id AND @ParentID = ParentCategoryID
    END'

    print @ExecSQL

    EXECUTE sp_executeSQl @ExecSQL, N'@AccountID BIGINT, @LocationID BIGINT, @CategoryName NVARCHAR(100), @ParentID BIGINT, @ExternalCategoryID BIGINT',
             @AccountID = @AccountID, @LocationID = @LocationID, @CategoryName = @CategoryName, @ParentID = @ParentID, @ExternalCategoryID = @ExternalCategoryID

END;

Answer 1

我看到 2 个问题。

1. 您在插入命令中定义了 12 个字段，但只有 11 个值
2. 更新语句没有列名(第一个除外)

应该是这样的:

UPDATE [DB].[dbo].[Category_' + CONVERT(NVARCHAR, @AccountID, 0) + '] 
SET  Level = CASE WHEN @ParentID IS NULL THEN 0 ELSE @Level END,
     field1 = 0,
     field2 = 1,
     field3 = 2,
     field4 = 3,
     field5 = 4,
     field6 = 'test2'
 Where @CategoryID = Id 
 AND   @ParentID = ParentCategoryID