question - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

question

Author

Topic

helpme
Posting Yak Master

141 Posts

Posted - 2009-05-21 : 09:59:44

I have a table like this:

create table test1 (track_no char(8),
customer_name varchar(50),
divisionid char(4),
question varchar(500))

the users will enter customer_name, divisionid and the question. We would like to automatically generate the track_no behind the scenes. The track_no should be prefixed with 'GH' and the rest will be a sequential number.

example

GH000001
GH000002
GH000003

I am looking at trying to do something with a trigger, but I didn't know if that was the best way, or if it was even possible.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-21 : 10:12:43

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

E 12°55'05.63"
N 56°04'39.26"

helpme
Posting Yak Master

141 Posts

Posted - 2009-05-21 : 11:40:05

Just what I needed. Thanks Peso!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-25 : 00:45:45

try this one too u can implement this one in ur insert sp


DECLARE @refNo  		VARCHAR(50),
@referencenumber		VARCHAR(32)

SELECT  @ReferenceNumber		= ''

SELECT @ReferenceNumber = MAX(customer_name)
FROM test1 

PRINT @ReferenceNumber

IF ISNULL(@ReferenceNumber,'') = ''
SELECT @refno = 'GH'+ '01'
ELSE
SELECT @refno =  'GH'+ RIGHT('00000' + CAST(CAST(RIGHT(@ReferenceNumber, 4) AS INT) + 1 AS VARCHAR(6)), 6)

SELECT @referencenumber = @refno
SELECT @referencenumber
--and then insert the @referencenumber into that column

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources