Hi Derek,

Thre're a few way to do that:
1. You can use numeric IDENTITY field in your tables. It'll automaticly generate next # every time you insert new record. See BOL for details.

2. The uniqueidentifier data type which can store a globally unique identifier (GUID) generated by NEWID() function. See BOL for details.

3. Use scheme you describe. You may find following references helpful
- Re: Record ID generator question Message #611104
- How to return a value from dynamicaly executed T-SQL code FAQ #8130

>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
>
>