generate unique id

capri · April 5th, 2008, 10:43 PM

hi ,

i have a table with name information which contain field

(patient id
name
address)

which contain datatype

patid int
name varchar
address varchar

patid is primary key and his identity is 1

so it contain data

patientid name address

1 john xyz
2 mark abc
3 kam rtg

the problem is that i need patientid in this way

patientid name address

patid_1 john xyz
patid_2 mark abc
patid_3 kam rtg
. .
. .
. .

how i generate id in this format
i have no idea how i can do this .plz someone help me

Jeff Moden · April 6th, 2008, 10:18 AM

This is wrong for so many reasons, there's not enough room to list them all on this simple forum. However, there is a way to do this without causing the damage it'll do in the future. Create a calculated column that "feeds" off the PatientID column... Something like this...

Code:

ALTER TABLE Patient --Assuming your table name is "Patient" because you didn't say
ADD PatID AS 'PatID_'+CAST(PatientID AS VARCHAR(10))

It can also be indexed and used as an "Alternate key" for foreign key and performance reasons...

Code:

CREATE UNIQUE INDEX IXU_Patient_PatID
ON jbmTest (PatID)

--Jeff Moden