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 firstname.lastname@example.org
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!