Retrieve GUID on/after insert?

  • Thread starter Thread starter Patrick Bates
  • Start date Start date
P

Patrick Bates

I need to write a row into a SQL Server table, and then immediately retrieve
the GUID which is generated for that row (it is the primary key). I can't
seem to find a way to do this with DataSets and DataAdapters, so I'm sure
I'm overlooking something simple.

Could someone direct me to a code sample of how to accomplish this in
VB.Net? Thanks.
 
Patrick said:
I need to write a row into a SQL Server table, and then immediately retrieve
the GUID which is generated for that row (it is the primary key). I can't
seem to find a way to do this with DataSets and DataAdapters, so I'm sure
I'm overlooking something simple.

Could someone direct me to a code sample of how to accomplish this in
VB.Net? Thanks.
Click to expand...

I don't think you can. But with GUIDs you always have a luxury
of generating it outside the database.
 
Sericinus said:
I don't think you can. But with GUIDs you always have a luxury
of generating it outside the database.
Click to expand...

You *can* do it, the same as you would for an IDENTITY column. But Sericinus
is correct, just generate it on the client side and then insert the value to
the database. Then you don't have to worry about retrieving it.
 
Matt said:
You *can* do it, the same as you would for an IDENTITY column. But Sericinus
Click to expand...

Would you briefly show how? Not that I think it can be of much use,
just out of curiosity.
 
Sericinus said:
Would you briefly show how? Not that I think it can be of much use,
just out of curiosity.
Click to expand...

Now that you mention it, it's not that easy to accomplish... :-/

There's no @@IDENTITY or SCOPE_IDENTITY to retrieve, so unless you have some
alternate way of uniquely identifying the newly-inserted row (such as an
alternate key), then you can't return the new GUID from a stored proc. So
client-side generation of GUIDs is the way to go.

I stand corrected.
 
Matt said:
Now that you mention it, it's not that easy to accomplish... :-/

There's no @@IDENTITY or SCOPE_IDENTITY to retrieve, so unless you
have some alternate way of uniquely identifying the newly-inserted
row (such as an alternate key), then you can't return the new GUID
from a stored proc. So client-side generation of GUIDs is the way to
go.
I stand corrected.
Click to expand...

Well, I thought of a way... :)

Your stored procedure would have to look something like this:
(psuedo-code, not tested)

CREATE PROCEDURE sp_InsertEmployee
@EmployeeID uniqueidentifier OUTPUT,
etc.
AS

SET @EmployeeID = newguid()

INSERT INTO Employees
(EmployeeID, etc.)
VALUES
(@EmployeeID, etc.)


That should do the trick.
 
Matt said:
Well, I thought of a way... :)

Your stored procedure would have to look something like this:
(psuedo-code, not tested)

CREATE PROCEDURE sp_InsertEmployee
@EmployeeID uniqueidentifier OUTPUT,
etc.
Click to expand...

Right, but this is essentially the same thing: you generate it
by hand and then insert. The code is just in a different layer now.
I have another idea. Let it get generated automatically. In
insert trigger retrieve it and put in a special dedicated table,
which may just have one column and one row. Then the latest
generated guid is always available.
 
Sericinus said:
Right, but this is essentially the same thing: you generate it
by hand and then insert. The code is just in a different layer now.
Click to expand...

I said you could do it that way, not that you should. It depends on your
application's needs.

I have another idea. Let it get generated automatically. In
insert trigger retrieve it and put in a special dedicated table,
which may just have one column and one row. Then the latest
generated guid is always available.
Click to expand...

Which only works if you have one user at a time inserting records. That's
why you're not supposed to use @@IDENTITY to get the last autonumber value
inserted, because it may not return the correct result.
 
Matt said:
I said you could do it that way, not that you should. It depends on your
application's needs.
Click to expand...

I don't take it that serious, just brain exercise.
Which only works if you have one user at a time inserting records. That's
why you're not supposed to use @@IDENTITY to get the last autonumber value
inserted, because it may not return the correct result.
Click to expand...

Well, better to say "unexpected" result. SCOPE_IDENTITY() is what is
needed in such a case.
 
So, I have to create the GUID client-side then?

What about the Unique requirements on this column in the database? I know
that the chances of a GUID duplication are slim, but how do you handle this
possibility if you go client-side?
 
Patrick said:
So, I have to create the GUID client-side then?

What about the Unique requirements on this column in the database? I know
that the chances of a GUID duplication are slim, but how do you handle this
possibility if you go client-side?
Click to expand...

I don't think that the chances of the duplication depend on where
it is generated. In case this happens, catch the error, regenerate it
and try again.
 
Patrick,

AFAIK should the chance that there is a duplication of a Guid be so small
that as the full population of the world was connected to your database it
should not even give a problem. However there can of course forever be a
problem, but normally you fix that in your normal error procedures.

Cor
 
Patrick said:
So, I have to create the GUID client-side then?
Click to expand...

Have to? No, you don't have to, but it's easier if you do it that way. IIRC
your question involved using the keys in a foreign key relationship without
inserting the parent into the database first. Generating a GUID on the
client side is the only way to accomplish that.
What about the Unique requirements on this column in the database? I
know that the chances of a GUID duplication are slim, but how do you
handle this possibility if you go client-side?
Click to expand...

I forget the math, but the chances of a collision are so remote that you may
never, ever see it. But you can certainly handle the situation in your
exception handler.
 
I need to write a row into a SQL Server table, and then immediately retrieve
the GUID which is generated for that row (it is the primary key). I can't
seem to find a way to do this with DataSets and DataAdapters, so I'm sure
I'm overlooking something simple.

Could someone direct me to a code sample of how to accomplish this in
VB.Net? Thanks.
Click to expand...

If the application only needs to work with SQL Server 2005 you could use
INSERT INTO ... OUTPUT ... VALUES ...

E.g.
create table guid_test (
id uniqueidentifier not null,
title varchar(20) not null
)

insert into guid_test (id, title)
output INSERTED.id, INSERTED.title
values (newid(), 'Testing')

Then on the client side you'd have to use a data reader to read the
result from the sql command. With VB.Net it's probaly easier just to
generate the guid client side, but the output parameter would be good if
you were using some kind of server side function to generate an ID of
some form.
 
Back
Top