Click here to Skip to main content
15,567,994 members
Articles / Database Development / SQL Server / SQL Server 2008
Technical Blog
Posted 7 Jul 2012

Stats

47.6K views
9 bookmarked

Auto-incremented ID with NVARCHAR/VARCHAR datatype

Rate me:
Please Sign up or sign in to vote.
3.00/5 (5 votes)
19 Jul 2012CPOL
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 the requirement.

Solution

SQL
/* 
 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)


Written By
Software Developer (Senior)
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionNeed some help Pin
Akshay12rao8-May-17 2:24
Akshay12rao8-May-17 2:24 
QuestionHi Author Pin
manojnutakki26-Mar-13 2:45
manojnutakki26-Mar-13 2:45 
QuestionNeed urgent help Pin
Tpankaj15-Jan-13 16:03
Tpankaj15-Jan-13 16:03 
AnswerRe: Need urgent help Pin
Tejas Vaishnav16-Jan-13 19:37
professionalTejas Vaishnav16-Jan-13 19:37 
Questionerror Pin
upup724-Nov-12 19:52
upup724-Nov-12 19:52 
AnswerRe: error Pin
Tejas Vaishnav27-Nov-12 0:03
professionalTejas Vaishnav27-Nov-12 0:03 
QuestionAuto increment Id with varchar/nvarchar datatype.. Pin
Jitendra Kumar Gangwar29-Sep-12 0:43
Jitendra Kumar Gangwar29-Sep-12 0:43 
SuggestionImprove Your solution Pin
Strange_Pirate28-Sep-12 2:12
Strange_Pirate28-Sep-12 2:12 
GeneralMy vote of 1 Pin
Marc Scheuner20-Jul-12 5:54
professionalMarc Scheuner20-Jul-12 5:54 
GeneralMy vote of 2 Pin
StianSandberg19-Jul-12 2:07
StianSandberg19-Jul-12 2:07 
GeneralRe: My vote of 2 Pin
Tejas Vaishnav19-Jul-12 3:57
professionalTejas Vaishnav19-Jul-12 3:57 
My friend this depends on requirement.
and i am pretty good to design a huge database as well with more complexity.
Thanks & Regards
Tejas Vaishnav
Find me on Facebook | Blog

SuggestionDont post rude reply coder Pin
Strange_Pirate28-Sep-12 2:14
Strange_Pirate28-Sep-12 2:14 
GeneralMy vote of 3 Pin
WebMaster10-Jul-12 12:04
WebMaster10-Jul-12 12:04 
QuestionFormatting! Pin
Vivek Krishnamurthy6-Jul-12 10:18
Vivek Krishnamurthy6-Jul-12 10:18 
AnswerRe: Formatting! Pin
OriginalGriff6-Jul-12 10:35
mvaOriginalGriff6-Jul-12 10:35 

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

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