CODEWITHSUNDEEP
sqlsql-server-2012
SteMMo
SteMMo

Reputation: 418

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 ?

Upvotes: 1

Views: 36

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460238

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

Upvotes: 4

Related Questions