SteMMo SteMMo - 20 days ago 8
SQL Question

SqlServer: how to retrive an existing id or the last id

I need to insert a record in a table only if it is not present in it.

In any case I'd like to return the id column (if the record is found) or the last used id column (if the record is new).

if exists (select id from DataElement where bytepos=0 and bitpos=0 and byteorder=0 )
select id from DataElement where bytepos=0 and bitpos=0 and byteorder=0
else
begin
insert into DataElement values ('SID','',0,0,0,8,129);
select scope_identity() as id
end


This script is ok but I'd like to avoid to run twice the SELECT operation.

If i return only

select id


I receive an error "Invalid column 'id'".

If there a way to store the return record of the first select and return it, if the case ?

Answer

Use a variable:

DECLARE @id int 
SELECT TOP 1 @id = id from DataElement where bytepos=0 and bitpos=0 and byteorder=0 
IF @id IS NULL 
begin
    insert into DataElement values ('SID','',0,0,0,8,129);
    select @id = scope_identity() 
end