sql server identity

CREATE TABLE Employee
(
      ID VARCHAR(50)
    , Name VARCHAR(255)
)


Stored Procedure


CREATE PROCEDURE dbo.AutoIncrement_Id
(
    @Name VARCHAR(255)
)
AS
DECLARE @Max INT
    , @ID VARCHAR(10)

IF NOT EXISTS(SELECT ID FROM Employee)
BEGIN
    SET @ID = 'L01'       
    INSERT INTO dbo.Employee(ID,Name)
    VALUES(@ID, @Name)   
END
ELSE
BEGIN
    SELECT @Max = CONVERT(INT, SUBSTRING(CONVERT(VARCHAR(10),ID), 2, 10)) FROM Employee
    SET @ID = 'L' + RIGHT('0' + CONVERT(VARCHAR(10), @Max + 1), 5)
    INSERT INTO dbo.Employee(ID,Name)
    VALUES(@ID, @Name)
END

Execution of Stored Procedure

EXEC dbo.AutoIncrement_Id 'Raj'
EXEC dbo.AutoIncrement_Id 'Ravi'
EXEC dbo.AutoIncrement_Id 'Rahul'


Display result

SELECT *
FROM dbo.Employee