SQL Server Set variable if exists else insert into table

Question

I'm looking for a more efficient way of completing this task. I need to set a variable equal to an ID if it exists, and if not insert it and then set the variable to the inserted identity. I can accomplish this by doing the following:

@VariableName --sent through to stored procedure

DECLARE @VariableID [int]

IF EXISTS(SELECT VariableID FROM VariableTable WHERE VariableName = @VariableName)
    SET @VariableID = (SELECT VariableID FROM VariableTable WHERE VariableName = @VariableName)
ELSE 
    INSERT INTO VariableTable(VariableName) VALUES(@VariableName)
    SET @VariableID = SCOPE_IDENTITY();
END

However it seems inefficient to run the same query twice (check if exists and if it does set the variable)

Just looking for suggestions on a better way to accomplish this task.

Answer 1

Try :

DECLARE @VariableID [int]
SELECT @VariableID=VariableID FROM VariableTable WHERE VariableName = @VariableName

IF @VariableID IS NULL
BEGIN
    INSERT INTO VariableTable(VariableName) VALUES(@VariableName)
    SET @VariableID = SCOPE_IDENTITY();
END

Answer 2

I tested this snippet and it executes correctly:

DECLARE @VariableID [int]
SET @VariableID=(SELECT VariableID FROM VariableTable WHERE VariableName = @VariableName)
IF @VariableID IS NULL
BEGIN
  INSERT INTO VariableTable(VariableName) VALUES(@VariableName)
  SET @VariableID = SCOPE_IDENTITY();
END