Hello All, We are going to start new batch from next week. message/call or mail us for more details.

Autoincrement value using stored procedure


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

3 comments:

  1. This blogs has .Net training using programming by difference of integer variable stored by the tables. That is auto increments of datatypes.

    Dotnet Training in Chennai

    ReplyDelete
  2. Really it is an amazing article I had ever read. I hope it will help a lot for all. Can you more read now visit Dot Net Certification Training Institute in Delhi

    ReplyDelete