Constant in Autonumber ID Field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Constant in Autonumber ID Field

Author

Topic

zorlice
Starting Member

2 Posts

Posted - 2007-04-15 : 13:56:18

I know this may be in the wrong forum, but I have a question. I am working on a system for a video store. I have rentals and sales for videos. I have set the ID fields for rentals and sales to be autonumbers and increment by 1, but I would like to have an S in front of sales IDs and R in front of rental IDs at all times. It is kind of like a constant in all autonumber ID fields. I want the S and R to be in every ID field, but the number to change. Thanks.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-04-15 : 14:39:49

You can't do this with an identity column. identity columns have to be integer types.

www.elsasoft.org

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-15 : 16:09:38

You can do this with a second varchar column. After a Insert, you would update the second column to be 's' or 'r' + identcol depending on the type of sale.


create table tb1(SaleID int identity(1,1) not null,SaleType varchar(20) not null ,SaleTypeID varchar(20) null

declare @SaleType varchar(20),@SaleTypeID vachar(20),@ID int
set @SaleType = 'S'

insert into Tb1(SaleType)
Values(@SaleType)
set @ID = Scope_IDentity()
set @SaleTypeID = @SaleType + cast(@ID as varchar(10))

update tb1
set SaleTypeID = @SaleTypeID
where SaleID = @ID

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-15 : 20:02:55

It is not a good idea to do this. It is difficult to program, and would limit the scalability of your system.

If you really need to display data this way, for example, in reports, you can add a simple conversion in a view and use that.


create table T_RENTAL 
(
	RENTAL_ID int not null identity(1,1) primary key clustered,
	COL_1 int
)
go
create view V_RENTAL
as
select
	RENTAL_REPORT_ID = 'R'+right('0000000'+convert(varchar,RENTAL_ID),7),
	RENTAL_ID,
	COL_1
from
	T_RENTAL
go
insert into T_RENTAL (COL_1)
select
	COL_1 =NUMBER
from
	F_TABLE_NUMBER_RANGE(101,110)

select * from V_RENTAL
go

drop table T_RENTAL
drop view  V_RENTAL


Results:

(10 row(s) affected)

RENTAL_REPORT_ID RENTAL_ID   COL_1       
---------------- ----------- ----------- 
R0000001         1           101
R0000002         2           102
R0000003         3           103
R0000004         4           104
R0000005         5           105
R0000006         6           106
R0000007         7           107
R0000008         8           108
R0000009         9           109
R0000010         10          110

(10 row(s) affected)

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-16 : 00:01:40

If you want to show data in front end, you can easily append it

ex in VB6

Format(column,"R0000000")

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-16 : 00:04:29

And F_TABLE_NUMBER_RANGE function, you can see it in Script Library

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources