Why not use an IDENTITY column? You can use the SCOPE_IDENTITY() function to retrieve the assigned value after the insert occurs.

-Mike


>Hi
>
>I need to be able to create unique record ID's and return the new ID to the calling procedure; what's the easiest and most effective way of doing this? Ideally I'd like to do everything from a generic stored procedure with SQL.
>
>Here's what I do at the moment:
>
>I have a table called SystemTable with 2 main fields, SystemTable.Table_Name and SystemTable.Table_Counter. For every table in my application there's a corresponding record within SystemTable which holds the next available record ID for that table. I can pass in the table name, SystemUsers for example, and my procedure will return the next available ID that I can use for that table. I can then perform an SQL Insert and explicitly tell the command the new record ID that I wish to use.
>
>Is there an easier way to do this? As long as the procedure returns the unique record ID then I'm happy.
>
>Regards
>
>Derek