Generate a unique field (No GUID pls) 1

[Solomons]

Hi All,
I'm trying to generate a column with unique values.
Kinda like an autoincrement field only that the number is generated by a stored proc.
Is using the datatime stamp the only way to do this?
Is there any other way of doing this

Thanks in Advance

 

[donutman]

It's a rather strange request. Why isn't a GUID ok (computers don't care about asthetics)? Why isn't an identity column ok? But the direct answer to your question is yes. Look up Insert Triggers in BOL and see if that would solve your problem...but use a GUID instead!
-Karl

 

[tb]

Hope this is what you were looking for ...

** Add column with unique constraint
ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE

**Add Unique contraint
ALTER TABLE publishers
ADD CONSTRAINT uqc_pub_name
UNIQUE (pub_name)
--WITH IGNORE_DUP_KEY


I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[Solomons]

Thanks tb, but that wasnt what I was looking for. I'm mostly try to figure out a query.
e.g.
Insert into Table (RowNum)
select max (RowNum) from Table;

Something to this effect (syntax may not be correct, but u get the picture).

Only I need a system where a number is returned back to a calling ASP page (its an invoice number, explains why its cannot be a GUID)
I has to be unique and should not be generated again. Time stamp is usually best in this case, but i'm looking for options.

Thanks.

 

[donutman]

I assume that the issue is that you want the invoice number prior to the actual insert otherwise a simple identity column with a start of 100,000 and an increment of 1 would work. I would use an InvoiceNumber table that had an identity column and a dummy field. Do an insert and return the identity field value. Use that as the invoice number. If the number isn't actually used...it doesn't matter.
-Karl

 

[tb]

I figured out that I was on the wrong track after I hit the submit button ... sorry ... but can I add another 2 sents worth?

>donutman: "If the number isn't actually used...it doesn't matter."
For auditing puprposes - would you not have to explain why the numbers are not sequencial? Say you are missing 50 invoice numbers you have eg. Number 1001 and the next one is Number 1051.

Then you might consider this ...
Table:
AutoID
InvoiceNumber
DateTime

(The first invoice number will have to be manually added)
You can select the invoice numbers for that particular day into a cursor (I know the opinions on that) and loop to see if they are sequencial .. if not you return the number that was skipped and insert the lost number into the table when the user finally submits the invoice. Then just double check on the insert for duplicates.

Hope I didn't make an idiot of myself ... again






I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[tb]

Also found this thread709-846570

I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[Solomons]

Thanks a lot tb.
You too donutman.

 

[dazzer123]

I posted a similar thread before, got some good answers

thread183-836466