CODEWITHSUNDEEP
sqlsql-server
Phil Scholtes
Phil Scholtes

Reputation: 419

Return NEWSEQUENTIALID() as an output parameter

Imagine a table that looks like this:

CREATE TABLE [dbo].[test](
     [id] [uniqueidentifier] NULL,
     [name] [varchar](50) NULL
)

GO

ALTER TABLE [dbo].[test] ADD  CONSTRAINT [DF_test_id]  DEFAULT (newsequentialid()) FOR [id]
GO

With an INSERT stored procedure that looks like this:

CREATE PROCEDURE [Insert_test]
    @name as varchar(50),
    @id as uniqueidentifier OUTPUT
AS
BEGIN
    INSERT INTO test(
        name
    )
    VALUES(
        @name
    )
END

What is the best way to get the GUID that was just inserted and return it as an output parameter?

Upvotes: 12

Views: 3605

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135789

Use the Output clause of the Insert statement.

CREATE PROCEDURE [Insert_test]
    @name as varchar(50),
    @id as uniqueidentifier OUTPUT
AS
BEGIN
    declare @returnid table (id uniqueidentifier)

    INSERT INTO test(
        name
    )
    output inserted.id into @returnid
    VALUES(
        @name
    )

    select @id = r.id from @returnid r
END
GO

/* Test the Procedure */
declare @myid uniqueidentifier
exec insert_test 'dummy', @myid output
select @myid

Upvotes: 13

devio
devio

Reputation: 37205

Try

SELECT @ID = ID FROM Test WHERE Name = @Name

(if Name has a Unique constraint)

Upvotes: 0

Related Questions