'0'附近的语法不正确。（Microsoft SQL Server）

debugcn 发表于 Dev

肯佐莱克

当我在下面执行此过程时，它会返回我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;

圭多

我看到2个问题。

您在插入命令中定义了12个字段，并且只有11个值
update语句没有列名（第一个除外）

应该是这样的：

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。