how to increment the value in store procedure.

Question

how to increment the value in store procedure.i need like this Emp1001,Emp1002,Emp1003., 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

how to increment the value in store procedure.

Answers (4)