Finding last GUID inserted - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Finding last GUID inserted

Author

Topic

robc
Yak Posting Veteran

60 Posts

Posted - 2004-05-12 : 16:00:43

Hi,

I'd like to return the guid(userID) of a user who was just inserted into my database.

I've tried this;

CREATE PROCEDURE [c_user_insert]
(@firstname [varchar](50),
@lastname [varchar](50),
@Email [varchar](50),
@city [varchar](50),
@street [varchar](50),
@country [char](2),
@state [varchar](50),
@postalcode [varchar](50),
@code [char](10),
@status [char](1) = '0',
@datecreated [datetime] ,
@membershipstart [datetime],
@membershipend [datetime],
@id uniqueidentifier OUTPUT)

AS INSERT INTO [cUsers]
( [firstname],
[lastname],
[Email],
[city],
[street],
[state],
[country],
[postalcode],
[code],
[status],
[datecreated],
[membershipstart],
[membershipend])

VALUES
( @firstname,
@lastname,
@Email,
@city,
@street,
@state,
@country,
@postalcode,
@code,
@status,
@datecreated,
@membershipstart,
@membershipend)

select @id id from cUsers WHERE email = @email
GO

This doesn't return the GUID that was inserted. It inserts the row fine but when i reference the @id output param , it's nothing. :(

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-12 : 16:04:08

When you say GUID, do you mean the IDENTITY value?

If so:

select @id = SCOPE_IDENTITY()

I realize that GUID is a data type, but you've referenced a column called id. Is that set to IDENTITY?

Tara

robc
Yak Posting Veteran

60 Posts

Posted - 2004-05-12 : 16:21:29

My ID column is a UniqueIdentifier with rowguide set to true.

I've tried using @@identity, but it won't work with a uniqueidentifier.

Hope this helps?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-05-12 : 16:23:35

try:

select @id =id from cUsers WHERE email = @email

notice the equals sign between @id and id.

- Jeff

robc
Yak Posting Veteran

60 Posts

Posted - 2004-05-12 : 16:30:16

Awesome, that worked. Thank you very much. J and t.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources