Formatting Numbers by padding with leading zeros in SQL Server

Change the number 6 to whatever your total length needs to be:

SELECT REPLICATE('0',6-LEN(EmployeeId)) + EmployeeId

If the column is an INT, you can use RTRIM to implicitly convert it to a VARCHAR

SELECT REPLICATE('0',6-LEN(RTRIM(EmployeeId))) + RTRIM(EmployeeId)

And the code to remove these 0s and get back the 'real' number:

SELECT RIGHT(EmployeeId,(LEN(EmployeeId) - PATINDEX('%[^0]%',EmployeeId)) + 1)

Just use the FORMAT function (works on SQL Server 2012 or newer):

SELECT FORMAT(EmployeeID, '000000')
FROM dbo.RequestItems
WHERE ID=0 

Reference: http://msdn.microsoft.com/en-us/library/hh213505.aspx

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 assumes that your EmployeeID is a numeric value and this code change the result to a string, I suggest to add again the original numeric value

EDIT Of course I have not read carefully the question above. It says that the field is a char(6) so EmployeeID is not a numeric value. While this answer has still a value per se, it is not the correct answer to the question above.