Create a table tbEmployeeMaster by following query :-

CREATE TABLE tbEmployeeMaster
(
            EmpId  INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
            EmpName VARCHAR(100),
            EmpCode VARCHAR(15)
)

Create a user defined function 'fnEmpCode' to get new Employee code of specified length and specified prefix using following query :-

CREATE FUNCTION fnEmpCode
(
            @EmpPrefix VARCHAR(10),
            @EmpLength INT
)
RETURNS VARCHAR(20)
AS
BEGIN
--Get maximum Emp id from table i.e. the last generated Empid in the table. (initially 0 if table has no data).

DECLARE @MaxEmpId INT;
SET @MaxEmpId= ISNULL((SELECT MAX(EmpId) FROM tbEmployeeMaster),0)

--Increment maxempid by 1 to get next emp id.
SET @MaxEmpId+=1

DECLARE @EmpCode VARCHAR(20),@i INT=1;

WHILE(@i=1)
BEGIN
--Generate new emp code of specified code length prefixed by specified prefix passed as parameters.

SET @EmpCode=@EmpPrefix +RIGHT(REPLICATE('0', @EmpLength-1) + CONVERT(VARCHAR(20),@MaxEmpId),@EmpLength)

--Check generated emp code. If already exists then get next emp code untill we get fresh emp code.
IF EXISTS(SELECT 1 FROM tbEmployeeMaster WHERE EmpCode=@EmpCode)
BEGIN
            SET @MaxEmpId +=1
END
ELSE
BEGIN
            SET @i=0
END
END
--Return newly generated emp code
RETURN @EmpCode
END

In above function line which actually generates the alphanumeric employee code is "SET @EmpCode=@EmpPrefix +RIGHT(REPLICATE('0', @EmpLength-1) + CONVERT(VARCHAR(20),@MaxEmpId),@EmpLength)" where @EmpCode is a variable defined in function and @EmpPrefix,@EmpLength-1 are passed variables to function. @EmpPrefix variable is concatenated with RIGHT function having two parameters, 1st parameter is REPLICATE function concatenated with CONVERT function and 2nd parameter is @EmpLength. REPLICATE function within RIGHT function will repeat the string '0' with specified number of times i.e  '@EmpLength-1' times concatenate with CONVERT function converting type of  @MaxEmpId to varchar. Finally RIGHT function will generate a string from rightmost side and will get appended to @EmpPrefix from rightmost side.  

Finally create a stored procedure ‘spEmpDetails_Save’ where 'fnEmpCode' function is called to get auto generated alphanumeric employee code to save in table with other employee details using
following query :-

CREATE PROC spEmpDetails_Save
(
            @EmpName   VARCHAR(100)     
)
AS
BEGIN
            SET NOCOUNT ON;
            --Get New Emp Code by calling fnEmpCode function.
            DECLARE @EmpCode VARCHAR(20)=(SELECT dbo.fnEmpCode('EMP-',5))

            --Insert employee detail including generated emp code in table
            INSERT INTO tbEmployeeMaster (EmpName, EmpCode)
            VALUES(@EmpName,@EmpCode);
END