I just read your response. You are definitely right about encapsulating the logic into a function. It does make things much cleaner, and it never occurred to me that I would need to pad more than 10 leading zeros to an integer. I guess that shows my level of experience.
I also noticed that neither of our methods work with negative integers.
Here is my solution to that problem. Please forgive the word wrap.
ALTER Function [dbo].[DBA_fnAddLeadingZeros](@Int INT, @TotalLength INT)
AS
BEGIN
DECLARE @Return VARCHAR(2000);
--IF @TotalLength > 100 SELECT @TotalLength = 100
IF @Int < 0
BEGIN
-- moves the - sign to the first charactor
SELECT @Return = Replicate('0',@TotalLength) + CAST(ABS(@Int) AS VARCHAR);
SELECT @Return = '-' + Right(@Return,@TotalLength -1);
END
ELSE
BEGIN
SELECT @Return = Replicate('0',@TotalLength) + CAST(@Int AS VARCHAR);
SELECT @Return = Right(@Return,@TotalLength);
END
RETURN @return;
END
Thanks
Todd