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
Thanks for reading