MS SQL Server Zero Padding

Question

EDIT:
I'm changing column datatype to Varchar, should suggestion work, answer will be upvoted

Full Story: I receive data for a person with an associated temporary number for every person that is 5 digits long, I process this information and then send variables to a stored procedure that handles the inserting of this data. When sending the variables to the stored procedure I appear to be losing any prefixed 0's.
For example:
Number sent to stored Proc - Number actually inserted column

12345 - 12345  
01234 - 1234  
12340 - 12340  

This only appears to be happening for numbers with a 0 in front. Meaning if I received:
00012 it would insert as 12

Is there a way where I could either update the column to always 0 pad to the left by a fixed number, meaning if we got 12 it would automatically make the value 00012.
OR
Is there a way to do this with the variable when its received by the stored procedure before the variable is inserted into the table. Something along the lines of:

SET @zeroPaddedFixedNum = LeftPad(@numberRecieved, '0', 5);  

Additionally, I now need to stop any more numbers from inserting and update all current incorrectly lengthed numbers. Any suggestions?

Perhaps it's just my Google ability that has failed but I have tried searching numerous pages.

Answer 1

For this, the column should be of varchar datatype. You can then do this

Insert into table(col)
select right('00000'+cast(@var as varchar(5)),5)

EDIT : To update existing data

Update table
set col=right('00000'+cast(col as varchar(5)),5)
where len(col)<5