how to increment the value in store procedure.

Question

how to increment the value in store procedure.i need like this Emp1001Emp1002Emp1003. with coding in sqlserver

Kunal Vaishya · Answer

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

Jignesh Trivedi · Answer

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.

selvi subramanian · Answer

its throw the error ma Msg 207 Level 16 State 1 Procedure usp_Employee_Insert Line 12 Invalid column name 'Employee_Name'. Msg 207 Level 16 State 1 Procedure usp_Employee_Insert Line 19 Invalid column name 'Employee_Name'.

Satyapriya Nayak · Answer

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