using Lock in Stored Procedure SQL Server 2005

What I am looking to avoid concurrency in my stored procedure

here is my script, I am trying SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

ALTER proc [dbo].[SP_GenerateNextReportID]
  @type nvarchar(255), @identity int output
   AS BEGIN
         SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    declare @id int;
    set @id =  IsNull((select LastUsedIdentity from   ReportProcessGenerator     开发者_开发技巧  where Type = @type), 0)
    if(@id =0)
      insert ReportProcessGenerator values(@type, @id +1)
     else
        update ReportProcessGenerator set LastUsedIdentity = @id +1 where Type = @type

    set @identity = @id +1
  END

Not sure is this a right way or not?

If you have a UNIQUE index or a PRIMARY KEY on ReportProcessGenerator.Type, then your stored procedures will not be able to modify the record for the same type concurrently.

Note that you should use SELECT FOR UPDATE or OUTPUT clause to avoid deadlocks, as @Martin points out. With SERIALIZABLE, concurrent SELECT queries won't lift the shared locks which the UPDATE queries will not later be able to upgrade.

However, why would you want to maintain separate per-type identities? Usually, one identity which is unique across types is as good as multiple ones unique within types, the former being much easier to maintain.