Using int datatype
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 data
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