This function simply formats an integer with commas to separate thousands, if necessary. 

This object is referenced by various other objects in this blog, including view vwTable which will be presented soon.

USE Admin

IF Object_ID('dbo.udfFormatInteger') Is Not Null

      DROP FUNCTION dbo.udfFormatInteger

go

CREATE FUNCTION dbo.udfFormatInteger

      (@In bigint)

RETURNS varchar(20)

AS

/*    DATE              AUTHOR            REMARKS    

      6/22/13           PPaiva            Initial creation.

     

      DESCRIPTION

            Formats a given integer with commas.

                  Examples:

                              IN                OUT

                              123               123

                            12345            12,345

                           -54321           -54,321

      USAGE

            SELECT dbo.udfFormatInteger(1234567890)

            SELECT dbo.udfFormatInteger(12345)

            SELECT dbo.udfFormatInteger(-54321)

            SELECT dbo.udfFormatInteger(0)

*/

BEGIN

      DECLARE @Out varchar(20),

                  @sIn varchar(20),

                  @Balance varchar(20),

                  @CurrTextLen smallint,

                  @IsNegative bit

      IF @In < 0

            BEGIN

                  SET @IsNegative = 1

                  SET @In = Abs(@In)

            END

      SET @sIn = Convert(varchar, @In)

      SET @CurrTextLen = Len(@sIn)

      SET @Out = ''

      SET @Balance = @sIn

      IF @CurrTextLen > 3

            BEGIN

                  WHILE 1 = 1

                        BEGIN

                              SET @Out = ',' + Right(@Balance, 3) + @Out

                              SET @Balance = Substring(@sIn, 1, @CurrTextLen - 3)

                              SET @CurrTextLen = Len(@Balance)

                              IF @CurrTextLen > 3

                                    CONTINUE

                              ELSE

                                    BEGIN

                                          SET @Out = @Balance + @Out

                                          BREAK

                                    END  

                        END

            END

      ELSE

            SET @Out = @sIn

      IF @IsNegative = 1

            SET @Out = '-' + @Out

      RETURN @Out

END