Autoincrement value using stored procedure

Satyapriyanayak
Posted by Satyapriyanayak under Sql Server category on | Points: 40 | Views : 3765
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

Comments or Responses

Login to post response