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




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

SELECT REPLICATE('0',6-LEN(EmployeeId)) + EmployeeId
And the code to remove these 0s and get back the 'real' number:

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


As clean as it could get and give scope of replacing with variables:

Select RIGHT(REPLICATE('0',6) + EmployeeID, 6) from dbo.RequestItems
WHERE ID=0


SELECT replicate('0', 6 - len(employeeID)) + convert(varchar, employeeID) as employeeID
FROM dbo.RequestItems
WHERE ID=0


SELECT
    cast(replace(str(EmployeeID,6),' ','0')as char(6))
FROM dbo.RequestItems
WHERE ID=0