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_GenerateID(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 GO
Please visit: www.TyroDeveloper.com
Please Click on +1
No comments:
Post a Comment