Generating employee code like EMP007 in SQLSERVER2008

CGN007
Posted by CGN007 under Sql Server category on | Points: 40 | Views : 1913
Generating employee code like EMP007 in SQLSERVER2008

DECLARE @employeecount INT
DECLARE @empid VARCHAR(10)
DECLARE @tempid INT
SELECT @employeecount= COUNT(*) FROM emp WHERE emp_id LIKE'EMP%'
IF @employeecount=0
SELECT @empid='EMP001';
ELSE
BEGIN
SELECT @tempid =MAX(CAST(REPLACE(emp_id,'EMP','')AS INT )+1 ) FROM emp;
IF LEN(@tempid)=1
SELECT @empid='EMP00'+CAST(@tempid AS VARCHAR(10));
IF LEN(@tempid)=2
SELECT @empid='EMP0'+CAST(@tempid AS VARCHAR(10));
IF LEN(@tempid)>2
SELECT @empid='EMP'+CAST(@tempid AS VARCHAR(10));
END
PRINT @empid;


Table Schema
CREATE TABLE [dbo].[emp](
[emp_id] [varchar](50) NULL,
[emp_name] [varchar](50) NULL
)

Comments or Responses

Login to post response