UNIQUEIDENTIFIER and @@IDENTITY

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 2005 Forums

Transact-SQL (2005)

UNIQUEIDENTIFIER and @@IDENTITY

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-04-01 : 12:00:10

Usually when I need the identity or a new inserted row returned, I usually do something like this:

RETURN @@IDENTITY

or

SET @myParm = @@IDENTITY

I have another table that uses a UNIQUEIDENTIFIER. This field is not an identity field so the above will not work.

Is there another way to retrieve this or I need to do a select using some of the parms that were sent into the stored procedure?

Thanks,

Zath

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-04-01 : 12:48:41

I normally like to use a output query.


declare @MyTable table (ui uniqueIdentifier default NEWID() ,myval int)
Declare @UI uniqueIdentifier
DECLARE @InsertedRows TABLE (UI uniqueIdentifier)


insert into @MyTable(MyVal)
output inserted.ui
Into @InsertedRows(UI)
select 1

Select * from @InsertedRows

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-01 : 13:17:08

if its before sql 2005, then you need to use trigger to capture the values as inserted table was only accessible from trigger code.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-01 : 14:08:02

Another alternative is to use your front end to generate a guid and use that instead of newid(). Then you don't need to retrieve it after the insert.

Be One with the Optimizer
TG

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-01 : 14:10:39

Also, @@IDENTITY = evil.

SCOPE_IDENTITY() = good.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-04-02 : 11:28:15

Also the easiest method as long as you are only inserting 1 record at a time is to create the UI Prior.

Declare @UI UniqueIDentifier

set @UI = NEWID()

Insert into @Mytable(UI)
Select @UI

select @UI

--SO basically you have the @UI variable the whole time.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2009-04-02 : 13:52:25

I like that idea. Let the sp create the UI and there you have it!

Zath

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources