Code Snippet posted by:
CGN007 | Posted on: 6/7/2012 | Category:
SQL Server Codes | Views: 679 | Status:
[Member] |
Points: 40
|
Alert Moderator
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
)