Click here to Skip to main content
Click here to Skip to main content
Go to top

Auto-incremented ID with NVARCHAR/VARCHAR datatype

, 19 Jul 2012
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
India India
No Biography provided

Comments and Discussions

 
QuestionHi Author Pinmembermanojnutakki26-Mar-13 1:45 
QuestionNeed urgent help PinmemberTpankaj15-Jan-13 15:03 
AnswerRe: Need urgent help PinmemberTejas Vaishnav16-Jan-13 18:37 
Questionerror Pinmemberupup724-Nov-12 18:52 
AnswerRe: error PinmemberTejas Vaishnav26-Nov-12 23:03 
QuestionAuto increment Id with varchar/nvarchar datatype.. PinmemberJitendra Kumar Gangwar28-Sep-12 23:43 
SuggestionImprove Your solution PinmemberStrange_Pirate28-Sep-12 1:12 
GeneralMy vote of 1 PinmemberMarc Scheuner20-Jul-12 4:54 
GeneralMy vote of 2 PinmemberAlluvialDeposit19-Jul-12 1:07 
GeneralRe: My vote of 2 PinmemberTejas_Vaishnav19-Jul-12 2:57 
SuggestionDont post rude reply coder PinmemberStrange_Pirate28-Sep-12 1:14 
GeneralMy vote of 3 PinmemberDeveloper201110-Jul-12 11:04 
QuestionFormatting! PinmemberVivek Krishnamurthy6-Jul-12 9:18 
AnswerRe: Formatting! PinmvpOriginalGriff6-Jul-12 9: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 | Mobile
Web02 | 2.8.140916.1 | Last Updated 19 Jul 2012
Article Copyright 2012 by Tejas Vaishnav
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid