how to increment the value in store procedure.

Dear try this SP I changes in



Alter PROC dbo.usp_Employee_Insert
(
    @Employee_Name VARCHAR(255)
)
AS
    DECLARE @Max INT
    , @EmpID VARCHAR(10)

IF NOT EXISTS(SELECT EmpID FROM Employee)
BEGIN
    SET @EmpID = 'EMP0000001'      
    INSERT INTO dbo.Employee(EmpID, Employee_Name)
    VALUES(@EmpID, @Employee_Name)  
END
ELSE
BEGIN
    SELECT @Max = CONVERT(INT, SUBSTRING(CONVERT(VARCHAR(10), EmpID), 4, 7)) FROM Employee
    SELECT @EmpID = 'EMP' + RIGHT('0000000000' + CONVERT(VARCHAR(10), @Max + 1), 7)
    INSERT INTO dbo.Employee(EmpID, Employee_Name)
    VALUES(@EmpID, @Employee_Name)
END

hi Selvi,

Can u please explain what is purpose of doing this, because there are so many idea to achive this

Please refer one of my post.

http://www.c-sharpcorner.com/Forums/Thread/163837/hi-how-to-auto-increment-for-text-and-char-in-sql-table.aspx

hope this will help u.

its throw the error ma 

Hi Selvi,

Try this...

Table Structure

CREATE TABLE Employee
(
      EmpID VARCHAR(50)
    , Employee_Name VARCHAR(255)
)

Stored Procedure

CREATE PROC dbo.usp_Employee_Insert
(
    @Employee_Name VARCHAR(255)
)
AS
DECLARE @Max INT
    , @EmpID VARCHAR(10)

IF NOT EXISTS(SELECT EmpID FROM Employee)
BEGIN
    SET @EmpID = 'E00001'       
    INSERT INTO dbo.Employee(EmpID, Employee_Name)
    VALUES(@EmpID, @Employee_Name)   
END
ELSE
BEGIN
    SELECT @Max = CONVERT(INT, SUBSTRING(CONVERT(VARCHAR(10), EmpID), 2, 10)) FROM Employee
    SET @EmpID = 'E' + RIGHT('0000' + CONVERT(VARCHAR(10), @Max + 1), 5)
    INSERT INTO dbo.Employee(EmpID, Employee_Name)
    VALUES(@EmpID, @Employee_Name)
END

Execution of Stored Procedure

EXEC dbo.usp_Employee_Insert 'Raj'
EXEC dbo.usp_Employee_Insert 'Ravi'
EXEC dbo.usp_Employee_Insert 'Rahul'

Display result

SELECT *
FROM dbo.Employee




Thanks
If this post helps you mark it as answer