Autoincrement value using stored procedure

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

Comments or Responses

Login to post response