SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Constant in Autonumber ID Field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zorlice
Starting Member

2 Posts

Posted - 04/15/2007 :  13:56:18  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 04/15/2007 :  14:39:49  Show Profile  Visit jezemine's Homepage  Reply with Quote
You can't do this with an identity column. identity columns have to be integer types.


www.elsasoft.org
Go to Top of Page

Vinnie881
Flowing Fount of Yak Knowledge

USA
1222 Posts

Posted - 04/15/2007 :  16:09:38  Show Profile  Reply with Quote
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




Edited by - Vinnie881 on 04/15/2007 16:17:51
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/15/2007 :  20:02:55  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 04/16/2007 :  00:01:40  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 04/16/2007 :  00:04:29  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
And F_TABLE_NUMBER_RANGE function, you can see it in Script Library

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000