Generate a Unique Number in asp.net / sql server 2005

hi... i need to generate a unique number.

I have used Date in Sql Server.

But Problem., its getting conficts while using more than one system.


Create PROCEDURE [dbo].[SP_FetchRequisitionID]
@ActionType varchar(30)
as
DECLARE	@Date Varchar(8),
			@MaxID VARCHAR(12),
			@Return varchar(12)
 
	IF(@ActionType='ReqID')
	BEGIN
		IF(DATEPART(DAY, GetDate()) < 10)
			SET @Date = '0' + CAST(DATEPART(DAY, GetDate()) AS VARCHAR) 
		ELSE
			SET @Date = CAST(DATEPART(DAY, GetDate()) AS VARCHAR) 
 
		IF(DATEPART(MONTH, GetDate()) < 10)
			SET @Date = @Date + '0' + CAST(DATEPART(MONTH, GetDate()) AS VARCHAR) 
		ELSE
			SET @Date = @Date + CAST(DATEPART(MONTH, GetDate()) AS VARCHAR)
 
		SET @Date = @Date + CAST(DATEPART(YEAR,  GetDate()) AS VARCHAR)
 
		SET @MaxID= '0'
		
		SELECT @MaxID= RequisitionID from RequisitionDetails where substring(RequisitionID,2,8)=@Date
										
		IF(@MaxID = '0')
			BEGIN
				SET @Return = 'R'+ @Date + '001'
			END
		ELSE
			BEGIN
				IF(DATEPART(DAY, GetDate()) < 10)
					SET @Return = 'R0' + CAST(CAST(RIGHT(@MaxID, 11) AS NUMERIC(12, 0)) + 1 AS VARCHAR)
				ELSE
					SET @Return = 'R' + CAST(CAST(RIGHT(@MaxID, 11) AS NUMERIC(12, 0)) + 1 AS VARCHAR)
		END 
 
		Select @Return as RequisitionID
	END

Open in new window

LVL 2
Rajeshk_cgmAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
RiteshShahConnect With a Mentor Commented:
why don't you use GUID.

in .NET System.GUID.NewGuID()

and in SQL Server

Select newID()

that is guarantee that you will have unique number all the time.
0
 
RiteshShahCommented:
moreover, it will save your time which you are consuming in generating that unique number from date.
0
 
Rajeshk_cgmAuthor Commented:
ofcourse i can use.., but i need the id, it should be meaningful.,

i have used R0110072009

here R is for requistion and 01 is refers the department details and the next values refers DD/MM/YYYY

NewID() is a uniqueidentifier..

is it possible to do with varchar...
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
RiteshShahCommented:
well, in that case why don't you include hour, min and seconds in your ID?
0
 
Rajeshk_cgmAuthor Commented:
well.,... it will not conflict with Seconds.. ?

for example there is 50 system., at the same second every one is trying to get the value means.

Actually i need to show the ID in form load itself., while insert into the table am just using the id

In Stored Procedure itself, am inserting means, hope we will not get the conficts.


0
 
RiteshShahCommented:
well, you do show it in form load but while inserting records, check once again whether that ID has been used in table or not, if used than regenerate new and change your old ID, give user message that your ID is "__________".
0
 
Rajeshk_cgmAuthor Commented:
no... that will not be the solution., because am using that id, to send mail ..

am using that id in may cases..

so., i can't regenerte the id while insertion..

  private void GenerateId()
    {
        long i = 1; foreach (byte b in Guid.NewGuid().ToByteArray())
        {
            i *= ((int)b + 1);
        }
        string number = String.Format("{0:d3}", (DateTime.Now.Ticks / 10) % 1000000000);
        Response.Write(number.ToString());
    }


how to make the digit as fixed.. is it possible.

if so., i can use the above method with the Format R01ddmmyyyy (GuidValue3digit or 4 digit)
0
 
RiteshShahConnect With a Mentor Commented:
it is not fixed that what number in GUID you will get alphabate and at which position you will get number but can't you get simply first two character and last two character?
0
 
Rajeshk_cgmAuthor Commented:
ok.. fine is it possible.,  to get the newID() as 3/4 digit combination..
0
 
RiteshShahCommented:
you can get left 2 character and right 2 character
0
 
Rajeshk_cgmAuthor Commented:
SELECT RIGHT('0000'+ CONVERT(VARCHAR, ABS(CONVERT(INT, CONVERT(VARBINARY, NEWID())))), 3)

i have used the above method

and now the Id combination is R16072009003981     Last 3 digit is random number

i hope now i will not get the conflict.. right.. !!!!!
0
 
RiteshShahCommented:
yes, hope for it...
0
All Courses

From novice to tech pro — start learning today.