Autoincrement value using stored procedure

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)

as

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)

)

AS

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