Post reply

select and lock next key value

  • May 7, 2003 at 11:27 pm

    #81482

    I have a situation where I wish to get the next unique key value from a column without creating a record. I would like any subsequent query against this column to return the value following mine.

    I would therefore effectively be locking the key I select from being assigned to the next record created.

    Is this possible? If so, how?

    Thanks in advance.

  • May 8, 2003 at 12:19 am

    #456479

    create a parameter-table with a column "lastkeyvalue". Read that value for update and than its all up to you. Others cannot reach it until you release it.

    That is if you want to have a key sequence. You could also use uniqueidentifiers. Then you can just generate them at clientside. They are considered to be ms-worldwide unique.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • May 8, 2003 at 3:47 am

    #456502

    Or create the table as suggested and use an SP to get the next value and replace for the next caller to keep the sequence up.

    Ex.

    CREATE PROCEDURE ip_NextKeyVal

    @val int OUTPUT

    AS

    SET NOCOUNT ON

    UPDATE tbl_NextKeyVal SET @val = col_KeyVal = col_KeyVal + 1

    GO

    Handles locking and value quite nicely.

  • May 8, 2003 at 5:47 pm

    #456610

    Thankyou to each of you. I will be able to use a couple of those options.

Viewing 4 posts - 1 through 4 (of 4 total)

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