How to retrieve auto-generated uniqueidentifier primary key after an insert?

I have a table like this

CREATE TABLE [GuidTable](
    [GuidKey] [uniqueidentifier] DEFAULT (newid()),
    [Logged] [datetime] DEFAULT (getdate()),
    [UserID] [int]

Field GuidKey is my primary key and it's a uniqueidentifier data type. When inserting a new row, all I want to supply is the UserID and let SQL Server auto generates the GuidKey and Logged fields. And I would like to be able to retrieve the GuidKey's value at the end of the call.

If the primary key is of identity data type then it would be easy. In this case, my primary key is a uniqueidentifier data type. Is there anyway to achieve this without creating a custom stored proc.

Thanks.

I haven't tried this, but in NonNull situations, I don't believe it's possible currently.  We do have access to the default value, and in the new version were looking at using a DefaultValueAttribute on your properties when a default value on a column was defined.

Would it not be possible to use a Guid.NewGuid() to create your id?

Yes it is possible, but our dba wants the sql server to auto generate these 2 columns rather than the business or data access layer. I look forward to the new version. Thanks for replying and the great works that  you and your team have been doing.

In SQL Server, your dba could set the default value for the field to the NEWID function. That way, SQL Server will do the work for you, and Nettiers should return the created GUID.

Hi

Just a suggestion - rather than create a custom proc, why not just alter the relevant Insert proc that NetTiers generates for you and override the Insert methods in the generated code so that it doesn't take the GUID as an input param - should only need to do it in a couple of places. 

If you change the genned proc to something like the following (untested - I'm not at my SQL machine but I'm sure you get the gist):

CREATE PROCEDURE dbo.GuidTable_Insert

(

DECLARE @MyGUID uniqueidentifier OUTPUT,

      @Logged datetime,

      @UserID int 

)

AS

SET MyGUID = NewID()  --You could also use SET MyGUID=DEFAULT here

 

                        INSERT INTO dbo.[GuidTable]

                              (

                              [GuidKey], [Logged], [UserID]

                              )

                        VALUES

                              (

                              @MyGUID, @Logged, @UserID

                              )

                       

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

Hope that helps

Martin

Faulcon

Looking at the create table script in the post it looks like the table's already setup with a default value - the problem is that the NetTiers generated procs won't be able to return the GUID that's generated on insert (as they use SCOPE_IDENTITY() to get the last inserted ID which doesn't work with GUIDs - ontop of that, as the GUID isn't an identity field I think NetTiers code will expect you to supply it as one of the input params so you'd end up having to create it client side anyway).

Cheers

Martin

pritcham wrote:

Faulcon

Looking at the create table script in the post it looks like the table's already setup with a default value - the problem is that the NetTiers generated procs won't be able to return the GUID that's generated on insert (as they use SCOPE_IDENTITY() to get the last inserted ID which doesn't work with GUIDs - ontop of that, as the GUID isn't an identity field I think NetTiers code will expect you to supply it as one of the input params so you'd end up having to create it client side anyway).

Cheers

Martin

Thanks for replying Faulcon and Martin.

You sum it up much better than I could have Martin. And indeed that's the challenge I am facing Faulcon.

Martin, it is an excellent suggestion. But since our schema is still evolving, we regenerate regularly, and I am worry that we would overwrite the modified stored proc every time.

Cheers,
Andrew.

Hi, 

I also need the new GUID of a new column added, I can do that (as mentionend above) to create the new guid with Guid.NewGuid() but I would like to ensure in the Business(Service-)Layer that this Guid is always added ....

Where is the best place in the Service-Layer to add this??

 Greetings

Stefan

So, is there no one using the UNIQUEIDENTIFIER as the PK of their tables and generating with .netTiers?

 If anyone has the solution to this problem, please post it!

Thanks!

 I wont say too much other than this is a touchy subject. SQL 05 supports something called the OUTPUT clause which would work perfectly for all computed, default, and identity columns, but would require changing the data layer codebase. It would be such a large change that it's really something we all would have to jump on, but even in my suggestions for it, I got pushback from a whole bunch of people.

Such is life.