How to do this in SQL Server Stored Procedure

Here is my problem:

I am designing Support System. I have a stored procedure for storing new Support Ticket. This stored procedure internally gets next ticket number and inserts Support Ticket

CREATE PROCEDURE [sp_SaveSupportTicket]
(
@.pid int,
@.uidGen int,
@.status VarChar (100),
@.probDes text,
@.probSol text,
@.guestName VarChar (100),
@.os VarChar (100),
@.roomNum VarChar (100)
)
AS
DECLARE @.ticNum int
SELECT @.ticNum = MAX(ticNum) + 1 FROM sup_TicDetails
INSERT INTO sup_TicDetails ( ticNum, pid, uidGen, status, probDes, probSol, guestName, os, roomNum,dateofsub)
VALUES (@.ticNum, @.pid, @.uidGen, @.status, @.probDes, @.probSol, @.guestName, @.os, @.roomNum, CONVERT(VARCHAR,GETDATE(),101))
GO

Now... before this happens, on my ASP.NET Page I have a label Ticket# . This label displays next ticket number

CREATE PROCEDURE [sp_GetNextTicketNumber] AS
SELECT max (ticNum) + 1
FROM sup_TicDetails
GO

Now.. how can I have only 1 stored Procedure so that I can obtain next ticket number and display it on ASP.NET page and when I hit "Submit Ticket" sp_SaveSupportTicket gets executed ??

I hope I have made my problem clear !! If not let me know......What you are doing will fail under load. BEtween the time you get the ticket number and insert, another thread could have inserted that same number. Why not use an IDENTITY column? Is it really essential to have the ticket number before you actually save it?|||I see what you are saying !!! no its not necessary to get ticket number before I save it.. it is just to display purpose.

There are problems with identity column ( I read in couple of aticles) so, I am not using Identity column..

Ok, if I don't display ticket number before saving it on my ASP.NET page.. will it still fail while load ??

How to handle thread part !!!!|||I have not heard of the problems you describe with Identity columns. They are certainly more reliable than what you have done here. Even if you do not display the number first, what you are doing will be prone to failure if multiple users are hitting the stored procedure at once. Trust me, Identity is the answer, and then use Scope_Identity() to get the new ID created right after an insert.|||Read this...

http://www.sqlteam.com/item.asp?ItemID=102

Note that if you delete row/s in a table and start adding row/s, the identity column will pick up where it left off.

If I delete a ticket, or try to rollback, I will in problem with Identity column...|||Here is link on MS Website describing Identity Column problem

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_3iex.asp

In our case, I am not sure, but we might have to delete some tickets and than I will be in trouble when I run Reporting Query !!!!!|||After talking to you I me and my friend tried ...

We both saw Ticket# 617 on our ASP.NET display page...

After entering data and hitting "submit", we went into Database to see what happend.

We saw 2 tickets Ticket# 617 created by me and 618 created by him.

So I guess concurrent access didn't gave us problem for insertion.. it displays wrong ticket number on screen ....

I guess I should remove Ticket# from screen than !!!!!!!!!!!

Any other idea ...|||The problem is that you may not be able to duplicate it at will, but the code you are using CAN fail if the two request come in at EXACTLY the same moment.|||Ok.. douglas

I gave you reason why I am not using Identity Column and I told you what happened when we submitted tickets.

I am understanding what you are saying.. .so what is the solution ? What are the chances that both tickets arriave to database at exactly same time ? What happens in that case ?

How can I handle this issue ? Everyone who is developing Web Applications might be running into same issues, how they are handling this or how are you handling such problem ?|||I use Identity columns as meaningless keys to link various rows in a database. It would not bother me if a row was deleted, because I would never rely on these numbers to know how many tickets were outstanding. I would use a SQL Query, not rely upon the ID number of the row. This matches what you would do in a system of paper tickets, because if a ticket is discarded, you would void the ticket and not reuse that number.

How often will folks have a problem with duplicate ticket numbers? Hard to say. You could use some sorts of locking inside of transactions. This is not something I would normally do, and it will serialize this section of code, meaning that only a single user at a time will be able to get to this section of code.|||hummm... Thats interesting !!!! If that is the case how will you set up primary key in your table ?

Here is structure of my table:

ticNum int 4 (primary key) (not auto generated, not identity)
pid int 4 (Property ID Referes to Property Table Primary Key)
uidGen int 4 (UserID Referes to User table Primary Key)
status varchar100
probDes text 16
probSol text 16
guestName varchar100
os varchar100
roomNum varchar100
dateofsub smalldatetime4|||Here is what I found out.. I have total 614 tickets, but when I do SELECT COUNT(*) from sup_TicDetails I see 614. I scanned my records and found that Tic# 100 and 472 are missing.

So I guess my previous developer might be using Identity. I removed Identity column after reding 2 articles i mentioned.

if I try convert primary key into Identity column now, will I be having any problem ?

How can I reset my Identity column every once in a while to fill out gaps or is it Ok to Have Column as identity & primary key and have gap in it ??|||It causes NO PROBLEM AT ALL to have gaps in the numbers.|||So, how you do testing of your code ?Everytime I Add/delete I am going to create gaps and when I transfer that on my production system, I am going to have gap tooo.....

How do I handle database on local system and on production system ?|||This is never something that has been an issue for me...