SP for Get ID or Insert ID

I want to create a SP in SQL 2008. If a value is already present in database it will return me its Id otherwise it will insert the value and will return Id of the n开发者_如何学运维ewly inserted value. How to create this SP for a table

Title { Id(int), Name(nvarchar(50) }

Here's an example, assuming the id column is an identity column:

create table YourTable (id int identity, name nvarchar(50))
go
create procedure dbo.YourSp(
    @name varchar(50))
as
    declare @id int

    set transaction isolation level serializable
    begin transaction
    select @id = id from dbo.YourTable where name = @name
    if @id is null
        begin
        insert dbo.YourTable (name) values (@name)
        set @id = scope_identity()
        end
    commit transaction
    return @id
go

If you test this like:

declare @rc int
exec @rc = dbo.YourSp 'John'; select @rc
exec @rc = dbo.YourSp 'John'; select @rc
exec @rc = dbo.YourSp 'George'; select @rc

It will print 1, 1, 2. If consistency is not that important, you can omit the transaction related statements from the procedure.

CREATE PROCEDURE dbo.insertIfNew

    (
    @outputID int Output,
    @Id int,
    @Name nvarchar(50)
    )

AS
BEGIN TRANSACTION

SELECT Id FROM Title WHERE Id=@Id

IF @@ROWCOUNT=0

BEGIN

INSERT INTO Title (Name)
VALUES     (@Name)

SET @outputID=SCOPE_IDENTITY()
END

ELSE
SET @outputID=@Id   

COMMIT TRANSACTION  

    RETURN