Click here to Skip to main content
11,438,226 members (56,176 online)
Click here to Skip to main content
Technical Blog

Auto-incremented ID with NVARCHAR/VARCHAR datatype

, 19 Jul 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
Auto incremented ID using VARCHAR data type.

Introduction

As we all know SQL provides functionality for auto incremented columns and we (mostly) use them for creating primary keys. But there is a limitation for it, it will auto increment only NUMERIC values like 1,2,3……up to N. But sometimes we need something like this. Suppose I have a table which will hold data from many departments 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.  

The logic for creating auto incremented ID with VARCHAR/NVARCHAR data type is modified as per requirement.

Solution

/* 
 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 AUTO-INCREMENTED 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 USING 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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Tejas Vaishnav
Software Developer (Senior)
India India
No Biography provided

Comments and Discussions

 
QuestionHi Author Pin
manojnutakki26-Mar-13 2:45
membermanojnutakki26-Mar-13 2:45 
QuestionNeed urgent help Pin
Tpankaj15-Jan-13 16:03
memberTpankaj15-Jan-13 16:03 
AnswerRe: Need urgent help Pin
Tejas Vaishnav16-Jan-13 19:37
memberTejas Vaishnav16-Jan-13 19:37 
Questionerror Pin
upup724-Nov-12 19:52
memberupup724-Nov-12 19:52 
AnswerRe: error Pin
Tejas Vaishnav27-Nov-12 0:03
memberTejas Vaishnav27-Nov-12 0:03 
QuestionAuto increment Id with varchar/nvarchar datatype.. Pin
Jitendra Kumar Gangwar29-Sep-12 0:43
memberJitendra Kumar Gangwar29-Sep-12 0:43 
SuggestionImprove Your solution Pin
Strange_Pirate28-Sep-12 2:12
memberStrange_Pirate28-Sep-12 2:12 
GeneralMy vote of 1 Pin
Marc Scheuner20-Jul-12 5:54
memberMarc Scheuner20-Jul-12 5:54 
GeneralMy vote of 2 Pin
AlluvialDeposit19-Jul-12 2:07
memberAlluvialDeposit19-Jul-12 2:07 
GeneralRe: My vote of 2 Pin
Tejas_Vaishnav19-Jul-12 3:57
memberTejas_Vaishnav19-Jul-12 3:57 
SuggestionDont post rude reply coder Pin
Strange_Pirate28-Sep-12 2:14
memberStrange_Pirate28-Sep-12 2:14 
GeneralMy vote of 3 Pin
Developer201110-Jul-12 12:04
memberDeveloper201110-Jul-12 12:04 
QuestionFormatting! Pin
Vivek Krishnamurthy6-Jul-12 10:18
memberVivek Krishnamurthy6-Jul-12 10:18 
AnswerRe: Formatting! Pin
OriginalGriff6-Jul-12 10:35
mvpOriginalGriff6-Jul-12 10:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150506.1 | Last Updated 19 Jul 2012
Article Copyright 2012 by Tejas Vaishnav
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid