Autoincrement value using stored procedure

We will know how to autoincrement int as well as varchar values in database using stored procedure while inserting records into the tables.

Using int datatype 

Table Structure 

create table AutoIncrementId
(
     id int,
     name varchar(50)
)

Stored Procedure 

CREATE 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 


CREATE 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