Procedure to create string ID

Question

Procedure to create string ID

nghoiming02

SSC-Addicted

Points: 478
More actions
February 28, 2004 at 11:43 pm

#84306

Dear nice guys,
How to create procedure or trigger in SQL Server 2000 to create string ID when new records inserted.
Could anyone give some example for it??

Thanks a lot!!!!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Andy Warren SSC Guru Points: 119902 More actions · Answer 1

I guess the first question is why a string id? Typically integers or uniqueidentifiers are used. Unless the value will be made of some component of the data itself even a string key will usually be derived from a number somewhere.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

nghoiming02 SSC-Addicted Points: 478 More actions · Answer 2

Andy,

The reason for using string ID is that I need to use a character in the beginning of ID e.g. A00001. The first letter is to indicate project code and the remaining number is to identify student.

How can I do??

Hendra Eka Putra SSCrazy Points: 2015 More actions · Answer 3

You can create a USER-DEFINED FUNCTION (UDF) to generate a complex ID, and then use that UDF on an AFTER INSERT TRIGGER for the table.If you don't need a complex ID, just simply combine any string that you need on AFTER INSERT TRIGGER for the table.Cheers -- Hendra

malb Valued Member Points: 59 More actions · Answer 4

Hi nghoiming02

I provide a user viewable serial number on a table in addition to its primary key value. It is similar to your requirement.

I do this by fetching the current top numeric value (bypassing the text prefix, in my case this is XA or XB but could be anything. I want the serial number to be 4 digits or more so from the top number (incremented by 1) i calculate the number of digits and ensure it is at least 4. I then form the new serial number using STR and pad with leading zeros. Finally I add the prefix and update the new serial number on the new record, already added to the table.

I'm sure there is a faster, more succinct way to do this but I only have a maximum of about 20 of these per day, so this works fine.

Here is my way:

-- Generate a new name using the top number in the table

SELECT @shNum = MAX(CAST(ISNULL(RIGHT(strName, (LEN(strName) - 2)), 0) AS int)) + 1

FROM tblName

-- Check for no entries in the table

if @shNum is null

select @shNum = 1

select @shDigits = ceiling(log10(@shNum + 1))

-- Min 4 digits

if @shDigits < 4

set @shDigits = 4

set @strName = REPLACE(STR(@shNum, @shDigits, 0), ' ', '0')

-- Form the Name

if @bFlag = 0

set @strName = 'XB' + @strName

else

set @strName = 'XA' + @strName

-- Write the new batch name to the new batch record

UPDATE tblName SET strName = @strName

WHERE lngIdName = @lngId

Amit Jethva SSCarpal Tunnel Points: 4707 More actions · Answer 5

If your table will have seprate columns for ProjectCode and Student and your application design permits, You may even use computed Columns !!

create table test

( projectcode char(1),

studentnum int ,

uniquecode as projectcode + right ( '00000' + convert(varchar,studentnum ) , 5) char(6)

)

-- Amit

"There is no 'patch' for stupidity."

Download the Updated SQL Server 2005 Books Online.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

I would also use two separate columns for project code and student (ID ?). The rest (eg, the presentation of A00001 can easily be done at the client application.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

malb77 Grasshopper Points: 24 More actions · Answer 7

Yes good point Frank,

I only use this serial number technique where the prefix only tells the user something about the 'type' of record, not inforamtion contained in other fields or tables.