Formatting Numbers by padding with leading zeros in SQL Server

DECLARE @empNumber INT = 7123
SELECT STUFF('000000', 6-LEN(@empNumber)+1, LEN(@empNumber), @empNumber)
Or, as per your query

SELECT STUFF('000000', 6-LEN(EmployeeID)+1, LEN(EmployeeID), EmployeeID)
         AS EmployeeCode
FROM dbo.RequestItems
WHERE ID=0

You can change your procedure in this way

SELECT Right('000000' + CONVERT(NVARCHAR, EmployeeID), 6) AS EmpIDText,
       EmployeeID
FROM dbo.RequestItems
WHERE ID=0
However this will change your EmployeeID to a string, so I suggest to add again the original numeric value