Get last inserted UNIQUEIDENTIFIER in SQL Server 2000

Question

The OUTPUT clause is compatible with SQL Server 2005 but not SQL Server 2000.

How do I convert this command to work in SQL Server 2000?

CREATE TABLE sample
(
 ID uniqueidentifier NOT NULL DEFAULT newid(),
 Title varchar(30) NOT NULL
)

INSERT INTO sample (Title)
OUTPUT INSERTED.ID
VALUES ('Test1')

I need the command to retrieve the ID since the INSERT command needs to be called from a stored procedure.

Thanks for any help!

Reductive

Answer 1

DECLARE @uid uniqueidentifier 
SET @uid  = newid()

INSERT INTO sample (ID, Title)
VALUES (@uid,'Test1')

SELECT @uid AS ID

Dede

This is a generic INSERT command that works on different tables (some with int IDENTITY(1,1) and some with uniqueidentifier).I need to use the DEFAULT newid(). – Reductive

@ShayN - This is the only way of doing it in SQL Server 2000. There is no equivalent of OUTPUT or SCOPE_IDENTITY() for uniqueidentifier columns. Although I suppose you could simulate the OUTPUT clause by adding an INSERT trigger to the table that does SELECT inserted.id but that would apply to all inserts on the table. – Dede

I already saw the trigger solution on other website and I wouldn't be able to use it. I needed to confirm that there is no equivalent. Thank you! – Reductive