Generate ID with SQL Server

Developer Scripts

Versión en Español
Sometimes we have a number and want display it with left zeros on a specified lenght.

We have this:

Is needed display the 756 number on this way: 0000000756

We need tocreate this SQL Server Script:

CREATE FUNCTION [dbo].[fn_GenerateID]
    (@ID BIGINT, @LEN SMALLINT)
RETURNS NVARCHAR(100)
AS
/*
THIS FUNCTION GENERATE ID WITH LEFT ZEROS.
JUAN GABRIEL CASTILLO TURRUBIATES
tyrodeveloper@gmail.com
20 dic 2011
Example:
SELECT dbo.fn_GeneraFolio(243,5) AS RESULT
Where 243 is the number to be converted and
5 is the number of chars, the value result is:
|RESULT
00243
*/
BEGIN
    DECLARE @SRC_LEN SMALLINT, @DIFF_LEN SMALLINT, 
    @COUNT SMALLINT, @ZERO NVARCHAR(100)
    SET @ZERO=''
    SET @SRC_LEN = LEN(CAST(@ID AS NVARCHAR(100)))
    IF @LEN <= @SRC_LEN
    BEGIN
        --IF THE LENREQUESTED IS LESS OR EQUAL THAN THE ORIGINAL
        SET @ZERO = ''
    END
    ELSE
    BEGIN
        --IF THE LEN REQUESTED IS MORE THAN THE ORIGINAL
        SET @COUNT= 0
        SET @DIFF_LEN = @LEN - @SRC_LEN 
        WHILE(@COUNT < @DIFF_LEN)
        BEGIN
            SET @ZERO = @ZERO + '0'
            SET @COUNT = @COUNT + 1
        END
    END
    RETURN @ZERO + CAST(@ID AS NVARCHAR(100))
END

Implementation:

We have the 756 number and want to how with a lenght of 10:

SELECT dbo.fn_GenerateID(756,10) AS RESULT

Please Click on +1