none
return the unique ID value while check exists of record

    Question

  • hi,

    is it possible to return the value of ID column to a variable when we check the exists of record like below?

    if exists (select id from tbl1 where col1=@val1)

    thanks!

    Tuesday, July 02, 2013 3:42 AM

Answers

All replies

  • declare @id int

    select @id = id from tbl1 where col1 = @val1

    if @id IS NULL -- there is no such id

       insert ...

    select @Id as Id;-- return @Id back


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 02, 2013 3:50 AM
    Moderator
  • hi naomi,

    am i right to say that it will work if the select col is unique and not nullable column?

    and for the select @id as id is it join together with insert command or after that? and it will return the newly record id?

    Tuesday, July 02, 2013 4:00 AM
  • It will work if the column is unique and non-nullable. If your ID is identity column, then use OUTPUT clause of INSERT command to return it after insert, e.g.

    declare @Output table (Id int)

    insert into myTable (...)

    OUTPUT Inserted.Id into @Output (Id)

    VALUES (...)

    SELECT Id from @Output 

    ---------

    The above will be the code for Insert case.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, July 02, 2013 4:05 AM
    Moderator