Set variable to SCOPE_IDENTITY inside of IF statement

Question

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.

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'

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

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

SELECT SCOPE_IDENTITY()

score 36 · Accepted Answer · edited Aug 17 '18 at 20:18

36

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
BEGIN
    SELECT @ObjectID = ObjectID FROM ObjectTable WHERE ObjectName = @ObjectName
END

edited Aug 17 '18 at 20:18

Brian Quirion

42
5

answered Sep 07 '11 at 20:46

Ben Robinson

21,601
5
62
79

score 4 · Answer 2 · answered Sep 07 '11 at 20:47

4

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

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

answered Sep 07 '11 at 20:47

Aaron Bertrand

272,866
37
466
490

Set variable to SCOPE_IDENTITY inside of IF statement

2 Answers2