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.

Mithrandir · Accepted Answer · 2012-01-31 18:57:52Z

16

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

answered Jan 31, 2012 at 18:57

Mithrandir

25.5k6 gold badges53 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ron tornambe · Accepted Answer · 2012-01-31 19:13:09Z

1

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

answered Jan 31, 2012 at 19:13

ron tornambe

10.8k8 gold badges38 silver badges61 bronze badges

Comments

Jaider · Accepted Answer · 2014-08-05 16:56:58Z

1

Here a lite modification to @Mithrandir answer. You can use TOP 1 that help you to speed up the result when you are not comparing against unique field. e.g.

DECLARE @EXISTS AS BIT
SET @EXISTS = 0
SELECT TOP 1 @EXISTS = 1 FROM MyTable WHERE MyYear = @Year

answered Aug 5, 2014 at 16:56

Jaider

15.1k5 gold badges83 silver badges88 bronze badges

Comments

HaveNoDisplayName · Accepted Answer · 2015-06-11 23:20:46Z

1

Try this:

INSERT INTO VariableTable (VariableID )
 SELECT SCOPE_IDENTITY()
FROM VariableTable
WHERE not exists
(
 SELECT VariableID FROM VariableTable WHERE VariableName = @VariableName
)

Then if you need the id you would have to set the variable the @@IDentity. I think this is most efficient as you aren't making a third query, but just just getting the last ID inserted.

edited Jun 11, 2015 at 23:20

HaveNoDisplayName

8,537106 gold badges41 silver badges50 bronze badges

answered Jan 31, 2012 at 19:01

Eric

8,10819 gold badges102 silver badges131 bronze badges

Comments

Simon Hughes · Accepted Answer · 2014-09-10 07:29:56Z

0

Try this amusing exception. Remember there's no BEGIN and END, so the next statement after the IF should be conditional. Now ask yourself why the first variable exists:

declare @check binary
declare @predeclared varchar(100)
select @check = 0

if @check = 1
   declare @conditionaldeclare nvarchar(4000)
   select @conditionaldeclare = 'conditionaldeclare'
   print @conditionaldeclare


if @check = 1
   select @predeclared = 'predeclared'
   print @predeclared

answered Sep 10, 2014 at 7:29

Simon Hughes

1

Collectives™ on Stack Overflow

SQL Server Set variable if exists else insert into table

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related