In this blog we will know how to autoincrement int datatype as well as varchar datatype values in database using stored procedure while inserting records into the tables.

Using int data type

Table Structure

Create table AutoIncrementId

(

id int,

name varchar(50)

)

Stored Procedure

ALTER procedure AutoIncrementIdpro

@name varchar(50)

declare @Id int

Select @Id=count(Id)+1 from AutoIncrementId

Begin

Insert into AutoIncrementId values (@Id,@name)

End

Execution of Stored Procedure

EXEC dbo.AutoIncrementIdpro 'Raj'

EXEC dbo.AutoIncrementIdpro 'Ravi'

EXEC dbo.AutoIncrementIdpro 'Rahul'

Display result

SELECT *

FROM dbo.AutoIncrementId

Output

id name

1 Raj

2 Ravi

3 Rahul

Using varchar datatype

CREATE TABLE Employee

(

ID VARCHAR(50)

, Name VARCHAR(255)

)

Stored Procedure

ALTER PROCEDURE dbo.AutoIncrement_Id

(

@Name VARCHAR(255)

)

DECLARE @Max INT

, @ID VARCHAR(10)

IF NOT EXISTS(SELECT ID FROM Employee)

BEGIN

SET @ID = 'E00001'

INSERT INTO dbo.Employee(ID,Name)

VALUES(@ID, @Name)

END

ELSE

BEGIN

SELECT @Max = CONVERT(INT, SUBSTRING(CONVERT(VARCHAR(10),ID), 2, 10)) FROM Employee

SET @ID = 'E' + RIGHT('0000' + CONVERT(VARCHAR(10), @Max + 1), 5)

INSERT INTO dbo.Employee(ID,Name)

VALUES(@ID, @Name)

END

Execution of Stored Procedure

EXEC dbo.AutoIncrement_Id 'Raj'

EXEC dbo.AutoIncrement_Id 'Ravi'

EXEC dbo.AutoIncrement_Id 'Rahul'

Display result

SELECT *

FROM dbo. Employee

Output

ID Name

E00001 Raj

E00002 Ravi

E00003 Rahul

Thanks for reading