Getting a NEWSEQUENTIALID()

I ran into a problem yesterday where I needed to get the ID that was created after an insert.  Typically this is something you use @@identity for or Scope_Identity().  I had to use a  Guid Though so I couldn’t use those features.  I posted the question on twitter and received a quick response a BIG Thank you to.  @robboek , @venzann and @mladenPrajdic.  For the suggestion on using the Output Clause

Here’s a Standard use of @@identity. 

Declare @identity int

Create table #temp
    (Id int identity(1,1) not null,
    ProductName varchar(255) null)
Insert into #temp
    (Productname) Values ('Bacon Cheeseburger')
Select @identity = @@IDENTITY

Select @identity

This doesn’t work if your using a Guid and a NewSequentialID  but here is the fix. 

Create table #temp
    (Id uniqueidentifier Default newsequentialid(),
    Productname varchar(255) null,
    Cost money not null)
Declare @RowNumber Table (rowid uniqueidentifier)
Declare @rowid uniqueidentifier

    Insert into #temp
        (Productname,Cost)
        Output inserted.Id
            Into @RowNumber
    Values
        ('Bacon Cheeseburger','2.99')
    Select @rowid = rowid from @RowNumber
    Select @rowid

This solution should work on Sql 2005 or Sql 2008

 

Pat