need primary key value based on a persons name and 4 auto numbers

[mague4718]

I need to create a primary key with the following format:


TABLE
----
ID Firstname MI LastName

I want ID to be automatically generated the ID to contain

first letter of first name
first letter of MI (or add a "-" in non available)
first letter of last name
and 4-6 automatically generated numbers

EX.

MBP3487 Margarita B Perez
BOW9044 Bill O Walker

 

[tlbroadbent]

Assuming you have a way to generate the number the following T-SQL code can be used to Update insert the ID.

-- @fn contains the first name
-- @mi contains the middle initial
-- @ln contains the last name
-- @num contains the number to use in for the ID

Declare @id char(9)

Set
@id = left(@fn,1) +
Case When isnull(@mn,'')='' Then '-' Else @mn End +
left(@ln,1) +
right('00000' + convert(varchar(6), @num),6)

Insert TblName Values (@id, @fn, @mn, @ln)

--------------------------------------

You could create a table to hold the current auto number value and write a statment to get the number and update the table in one statement.

Example: Table, IDNumber, has 1 column - ID

Update IDNumber Set ID=ID+1, @num=ID+1 Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm