Monday, March 19, 2012

Generare ID with SQL Server

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