unique next no. generation problem

5 pts.
Tags:
SQL Server 2005
have to generate unique_number the condition is the user may or may not have system_no which is allocated by the system the user first time start the system/application and if the user format that system the same unique_no will be generated again.this no. comes to our server for user_id and server(sql server 2005) generate an unique_no against the unique_no comes from the user_end and it returns to user as a customer_id/login id for next refference. now the problem is how to generate an unique_Number that will return to the user. the solution we are using for it as given below..but sometime it generate the next number if the same system_no exists but we need if system_no exist it should give the same no. which is given the first time for that user_id But sometime generates new no. for the existing id & it happens some time not always(its a multi user app) Running Inside A Transaction At Application Level.... Create proc [dbo].[USP_Get_User_ID](@system_id bigint,@user_id_No varchar(6) out)as begin Declare @intNO integer if IsNull(@system_id,0)>0 Begin select @user_id_No=user_id_No from User_Id_Table p where system_id=@system_id End if IsNull(@user_id_No,'')='' --or IsNull(@logid,0)>0 Begin Select @intNO=IsNUll(Next_No,0) from Last_User_Id_Table Set @user_id_No='E'+right('0000'+Cast(IsNull(@intNO,0)+1 as varchar),5) Insert Into User_Id_Table(user_id_No) Values(IsNull(@intNO,0)+1) Update Last_User_Id_Table set Next_No=IsNull(@intNO,0)+1 End End please,suggest the correct way to manage Thanks sukhen.dass@mrmworldwide.com

Software/Hardware used:
Software
ASKED: December 31, 2013  12:28 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following