SQL Server Set variable, if else exists, insert into table

Question

I am looking for a more efficient way to accomplish this task. I need to set a variable equal to ID if it exists, and if not insert it, and then set the variable in the inserted identification information. 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 that it is inefficient to run the same query twice (check if it exists and if it sets the variable)

Just look for suggestions on how best to complete 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

Try the following:

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

Then, if you need an identifier, you will need to set the @@ IDentity variable. I think this is most effective since you are not making the third request, but simply getting the last identifier.

Answer 3

I tested this fragment and executed it 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 

Answer 4

Here's a small modification to @Mithrandir. You can use TOP 1 , which will help you speed up the result if you do not compare it with a unique field. eg

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

Answer 5

Try this fun exception. Remember that there are no BEGIN and END, so the next statement after IF must 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