What's The Best Way To Tackle This?

Hi,

I'm doing a project at work. We're using SQL Server 2000 right now but will probably migrate to 2005 later. Here's my situation. My database stores feature request tickets. So I have columns for id, feature_request_number, summary, description, etc.

id is auto-increment (primary key). feature_request_number is a 6 char alpa-numeric. The first three are numbers, and the last three are FRQ...(ex. 034FRQ). What's the best way to implement the incrementation of that field? Since it's alpha-numeric I can't just make it auto-increment. I can drop the FRQ, but that would cause some complications with migrating the older database to the new one.

This is what my plan was:

- Set the feature_request_number as an int. (Drop the FRQ)
- When a record is being added, get the value of the last feature_request_number.
- Add 1 to it and specify that value in the INSERT statement.

So it's like a manual increment. Would I be able to do this all in SQL? Is there a way to form a SQL statement to get the value of a column and add 1 to it and save it to a variable. I want to do it in SQL to keep data integrity. I can have a stored procedure to do this and it would make life a lot simpler. The front-end of my app is a ASP.NET website, so it would be best to keep as much logic out of the ASP pages as much as possible.

Thanks in advance for your help.