Click here to Skip to main content
15,879,095 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi
My requirement is invoice no should be auto number example given below

1, 001 - Do
2, 002 - Do
3, 003 - Do


SQL
CREATE TABLE [dbo].[Customers] (
    [pk_ID] [int] IDENTITY (001 -Do, 1) NOT NULL ,
    [sValue] [char] (10) NULL ,
    [sDesc] [char] (10) NULL ,
    [iOrd] [numeric](18, 0) NULL 
) ON [PRIMARY]


I am getting error for this
could you pls help to solve this issue

Regards
Lancy
Posted
Updated 31-Oct-11 6:02am
v2
Comments
Rob Philpott 31-Oct-11 12:04pm    
What is '- Do'? You can't have that as part of an identity.

CREATE TABLE [dbo].[Customers] (
 [pk_ID] [int] IDENTITY NOT NULL ,
 [sValue] [char] (10) NULL ,
 [sDesc] [char] (10) NULL ,
 [iOrd] [numeric](18, 0) NULL 
) ON [PRIMARY]


Turn pk_ID for pk_ID=1 into 1, 001 - Do using string.Format

Best regards
Espen Harlinn
 
Share this answer
 
Comments
Mehdi Gholam 31-Oct-11 12:06pm    
5'ed
Espen Harlinn 31-Oct-11 12:09pm    
Thank you, Mehdi :)
Monjurul Habib 31-Oct-11 16:24pm    
my 5!
Espen Harlinn 31-Oct-11 16:34pm    
Thank you, Monjurul!
There is no such an identity field but you can use computed fields to achieve that output :

SQL
CREATE TABLE [dbo].[Customers](
     [pk_ID] [int] IDENTITY(1,1) NOT NULL,
     [sValue] [char] (10) NULL ,
     [sDesc] [char] (10) NULL ,
     [iOrd] [numeric](18, 0) NULL ,
     [cus_id]  AS (substring(CONVERT([varchar],(100000000000.)+[id],0),(2),(11))+' - Do') PERSISTED
) ON [PRIMARY]


Note :

1- PERSITED is important if you want to use cus_id in table relations
2- 11 is number of zeros in 100 billion. if you want another number, count its zeros and substitude 11 with it.
3- if you got ARITHABORT error when inserting rows to the table : right click your database in SSMS then select Properties then in options section set Arithmetic Abort Enabled to True.

And finally if it solved your problem do no forget to set this as an answer or vote it up :)
 
Share this answer
 
Comments
Espen Harlinn 31-Oct-11 16:36pm    
Nice one, Amir :)
Amir Mahfoozi 1-Nov-11 1:19am    
Thank you dear Espen ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900