Post reply

UniqueIdentifier Conondrum

  • July 12, 2004 at 10:49 am

    #108782

    Question:

    I have a table that has a uniqueidenfier as the, well unique identifier. I have a stored prop to update and insert. I want the prop to return the unqueidentifier.

    Once upon a time we would use the @@Identity for interger IDENTITY. Is there a similiar for uniqueidentifiers? Also take into account that you cannot use the max() function on uniqueidentifers.

    Please take into account that newid() is already being used by default in the table for the field. I don't want to assign the new id in the stored procedure so that other programs can import the data, not just ones that use my stored proc.

    Thanks.

  • July 12, 2004 at 2:03 pm

    #514306

    And thats Conundrum not Conondrum. Sorry.

  • July 12, 2004 at 9:23 pm

    #514321

    I don't see what's the problem with using the newid() function in your procedure. The real cool thing about the uniqueidentifier  column type is that you can know in the client side which is going to be BEFORE inserting so you don't need to return anything unless you want to use the newid() on the server side.

     


    * Noel

  • July 12, 2004 at 9:26 pm

    #514322

    I don't know if this is the BEST practice, but you could have an update/insert trigger on the table and write the newid value to another table and have your stored proc read that other table.  The other table would only have 1 field, the uniqueIdentifier, and it's value would be the last insert/update of that table.

  • July 13, 2004 at 4:58 am

    #514363

    What are the other Unique Indexes / Primary Key on the table ?

    Use that to retrieve the last newid() in the table.

    Using ONLY a Uniqueidentifier as Key in the table is very poor practice, since this does not enforce any kind of business integrity on the table, and duplicates can be easily added.

    If the Uniqueidentifier is thee only possible key in the table, which I doubt, then there is no nice way to retrieve the last inserted newid() in the table.

    No function like SCOPE_IDENTITY() / @@IDENTITY that you used before

    What is the problem with Updates?, there You have to provide a key to your procedure !?

    I don't want to leave you completely out in the wild so I propose a solution:

    Add an Identity column or a timestamp column

    And use either:

    SELECT MyUniqueId FROM table WHERE IdentityCol = SCOPE_IDENTITY()

    SELECT MyUniqueId FROM table WHERE TimestampCol = ( SELECT MAX(TimestampCol) FROM table ).

    To retrieve your unique identifier value...

    /rockmoose


    You must unlearn what You have learnt

  • July 13, 2004 at 7:36 am

    #514393

    The uniqueidentifier is the primary key. I used the newid() in the procedure and it worked (you had it noeld).

    There is no issue like with auto assigned integers that if you try to assign your own number you get an error. Learn something new everyday.

    Thanks All.

  • July 13, 2004 at 5:59 pm

    #514480

    Just to let people know, I usually use something like the following

    DECLARE @new_id AS UNIQUEIDENTIFIER

    SET @new_id = NEWID()

    INSERT INTO

    ([primary_key]) VALUES (@new_id)

    SELECT @new_id

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply