将变量设置为IF语句中的SCOPE_IDENTITY

I am trying to write a stored procedure that will check for the existence of a record and if it does not exist do an insert returning the SCOPE_IDENTITY and if it does exist simply return the ID of the object. After I get this information in the procedure I need to do further processing with it and am unsure of how to accomplish getting the SCOPE_IDENTITY from within an IF...ELSE statement.

我正在尝试编写一个存储过程，它将检查记录是否存在，如果不存在，请执行一个插入操作，返回SCOPE_IDENTITY，如果存在，则返回对象的ID。在我获得了这个过程中的信息之后，我需要进一步处理它，并且不确定如何从一个IF中实现SCOPE_IDENTITY。ELSE语句。

My two tables:

我的两个表:

CREATE TABLE [dbo].[ActionTable] (
    [ActionID] [int] IDENTITY(1, 1) NOT NULL,
    [ObjectID] [int] NOT NULL,
    [ActionName] [nvarchar](255) NOT NULL
)

CREATE TABLE [dbo].[ObjectTable] (
    [ObjectID] [int] IDENTITY(1, 1) NOT NULL,
    [ObjectName] [nvarchar](255) NOT NULL
)

Stored Procedure:

存储过程:

CREATE PROCEDURE [dbo].[SetAction]
    @ActionName [nvarchar](255),
    @ObjectName [nvarchar](255)
AS
    DECLARE @ObjectID [int]

    --I have tried the following

    IF NOT EXISTS(SELECT ObjectID FROM ObjectTable WHERE ObjectName = @ObjectName)
        INSERT INTO ObjectTable(ObjectName)VALUES(@ObjectName)
        --Here I need to set @ObjectID = SELECT SCOPE_IDENTITY()
    ELSE
        SELECT @ObjectID = ObjectID FROM ObjectTable WHERE ObjectName = @ObjectName

This returns Incorrect syntax near the keyword 'ELSE'

在关键字“ELSE”附近返回不正确的语法

What would be the best method for accomplishing this lookup/insert? After I get the ID I will then do the following

完成这个查找/插入的最佳方法是什么?得到ID后，我将执行以下操作

INSERT INTO ActionTable(ObjectID, ActionName) VALUES(@ObjectID, @ActionName)

SELECT SCOPE_IDENTITY()

2 个解决方案

#1

If you have more than one thing to do you need a BEGIN END

如果你有不止一件事要做，你需要一个开始的结束

IF NOT EXISTS(SELECT ObjectID FROM ObjectTable WHERE ObjectName = @ObjectName)
BEGIN
        INSERT INTO ObjectTable(ObjectName)VALUES(@ObjectName)
        SET @ObjectID = SCOPE_IDENTITY()
END
ELSE
SELECT @ObjectID = ObjectID FROM ObjectTable WHERE ObjectName = @ObjectName

#2

You should always use BEGIN / END even for only one statement so that when you add statements later your code doesn't break.

即使只有一条语句，也应该始终使用BEGIN / END，以便以后添加语句时代码不会中断。

IF ...
BEGIN
    ... statements
END
ELSE
BEGIN
    ... statements
END

#1