Wednesday, 4 July 2012

AUTO INCREMENTED ID WITH NVARCHAR/VARCHAR DATATYPE


INTRODUNCTION

As we all know SQL provide functionality for Auto Incremented Column and we (mostly) use it for creating primary keys. But there is one limitation for it, it will Auto Increment only NUMERIC value like 1,2,3……up to N. But sometimes we need something like this. Suppose I have one table which will hold the data from many department like Marketing ,HR ,Networking etc. and my requirement is something like to insert a PK like M001,M002,M003…. if Marketing Department Record, and N001,N002,N003…. If Networking Department record or same as for HR likes H001, H002, H003... So how to manage this automatically.

Logic for creating Auto Incremented ID with VARCHAR/NVARCHAR data type is modified as per requirement.

SOLUTIONS

/* 
 HERE I CREATED DUMMY TABLE FOR DEMONSTRATING YOU
*/

CREATE TABLE TESTING(
 ID VARCHAR(5),
 NAME VARCHAR(15),
 DESCP VARCHAR(50)
);
GO


/*
 CREATING PROCEDURE FOR INSERTING RECORD WITH NVARCHAR AUTOINCREMENTED ID
*/
CREATE PROCEDURE SP_INSERT
 @NAME VARCHAR(MAX),
 @DESCP VARCHAR(MAX)
AS
BEGIN
 /* Logic for Getting New ID as Per the NAME with PRE FIX */
 DECLARE @NEWID VARCHAR(5);
 DECLARE @PREFIX VARCHAR(1);
 SET @PREFIX = UPPER(SUBSTRING(@NAME, 1, 1))
 SELECT @NEWID = (@PREFIX + replicate('0', 3 - len(CONVERT(VARCHAR,N.OID + 1))) + CONVERT(VARCHAR,N.OID + 1)) FROM (
  SELECT CASE WHEN MAX(T.TID) IS null then 0 else MAX(T.TID) end as OID FROM (
   SELECT SUBSTRING(ID, 1, 1) as PRE_FIX,SUBSTRING(ID, 2, LEN(ID)) as TID FROM Testing
  ) AS T WHERE T.PRE_FIX = @PREFIX
 ) AS N

 /* INSERT QUERY FOR NEW RECORD */
 INSERT INTO Testing VALUES (@NEWID,@NAME,@DESCP)
END
GO

/* HERE YOU NEED TO PASS A VALUE TO THIS SP LIKE THIS
 @NAME = 'MANAGEMENT'
 @DESCP = 'YOUR MANAGEMENT DESCRIPTION'
 
 @NAME = 'NETWORK'
 @DESCP = 'YOUR NETWORK DESCRIPTION'
 
 IT WILL AUTOMATICALLY INSERT THE RECORD
 IF YOU GO FOR MANAGEMENT THEN THE ID WILL BE
 M001 FOR FIRST TIME then NEXT TIME IT WILL M002
 AND THEN IF YOU GO FOR NETWORK THEN THE ID WILL BE
 N001 FOR FIRST NETWORK RECORD THEN N002...
*/  
SP_INSERT 'MANAGEMENT','YOUR MANAGEMENT DESCRIPTION';

SP_INSERT 'NETWORK','YOUR NETWORK DESCRIPTION';

SP_INSERT 'HR','YOUR HR DESCRIPTION';

SP_INSERT 'MANAGEMENT','YOUR MANAGEMENT DESCRIPTION 2';

SP_INSERT 'NETWORK','YOUR NETWORK DESCRIPTION 2';

SP_INSERT 'HR','YOUR HR DESCRIPTION 2';


/* 
 SELECT QUERY FOR SELECTING RECORD INSERTED UISNG SP_INSERT 
*/
SELECT * FROM TESTING

RESULT

ID    NAME            DESCP
----- --------------- ------------------------------
M001  MANAGEMENT      YOUR MANAGEMENT DESCRIPTION
N001  NETWORK         YOUR NETWORK DESCRIPTION
H001  HR              YOUR HR DESCRIPTION
M002  MANAGEMENT      YOUR MANAGEMENT DESCRIPTION 2
N002  NETWORK         YOUR NETWORK DESCRIPTION 2
H002  HR              YOUR HR DESCRIPTION 2

No comments:

Post a Comment