none
How to write T-SQL code that will work correctly in all versions of MS-SQL-Server when I want to use FORMAT (new since 2012)? RRS feed

  • Question

  • I want something like following:

    DECLARE @SqlSrvVersionMajor INT, @InputInt int, @NumberAsText nvarchar(30);

    set @InputInt = 123456789;

    set @SqlSrvVersionMajor = CONVERT(INT, (@@MICROSOFTVERSION / 0x1000000) & 0xff);

    set @NumberAsText = CONVERT(nvarchar(30), @InputInt );

    IF @SqlSrvVersionMajor >= 11 BEGIN

           set @NumberAsText = FORMAT(@InputInt,'### ### ### ### ###');

    END;

    PRINT ('Result = ' + @NumberAsText);

    When I run it on "Microsoft SQL Server" version 2008-R2 (or older) it fails (as expected) with following error-message:

    Msg 195, Level 15, State 10, Line 7

    'FORMAT' is not a recognized built-in function name.

    In other words: how to re-write line „set @NumberAsText = FORMAT(@InputInt,'### ### ### ### ###');“?

    By something like „
    set @TSQL = N'FORMAT(@InputInt,''### ### ### ### ###'')';

    EXECUTE(@TSQL);

    “?


    __________________________________________________________ If isn't above described anything, the following applies: Technical details: * OS: Windows Server v2008-R2, English, Enterprise Edition, x64, SP1 ** My User-Account is member of 'Administrators' local security group. * MS-SQL-Server: v2008-R2, English, Enterprise Edition, x64, SP1 ** My User-Account is member of 'SysAdmin' db-role.


    • Edited by David KRIZ Friday, August 9, 2019 10:53 AM typo
    Friday, August 9, 2019 10:52 AM

Answers

  • In other words: how to re-write line „set @NumberAsText = FORMAT(@InputInt,'### ### ### ### ###');“?

    By something like „
    set @TSQL = N'FORMAT(@InputInt,''### ### ### ### ###'')';

    EXECUTE(@TSQL);

    You can use a parameterized dynamic SQL statement as below. Or you could use one of the suggested methods that will work on both earlier and later versions to avoid the conditional execution and dynamic SQL.

    EXEC sp_executesql N'SET @NumberAsText = FORMAT(@InputInt,''### ### ### ### ###'');', N'@InputInt int, @NumberAsText nvarchar(30) OUTPUT', @InputInt= @InputInt,  @NumberAsText = @NumberAsText OUTPUT;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Friday, August 9, 2019 11:52 AM
    • Marked as answer by David KRIZ Friday, August 9, 2019 12:30 PM
    Friday, August 9, 2019 11:50 AM

All replies

  • I'm not sure what you Looking for
    the error message is clear or ? this function is not include as base function in SQL 2008 and you can't use a function with is not there.

    a solution for could be write your own "Format function" as udf or check the SQL Server Version before you use this functions.
    I real hope this will help a bit.

    MS Help for "Format" 

    https://docs.microsoft.com/de-de/sql/t-sql/functions/format-transact-sql?view=sql-server-2017

    here you find the old info 

    https://docs.microsoft.com/de-de/previous-versions/sql/?view=sqlallproducts-allversions

    klaus


    Klaus


    Friday, August 9, 2019 11:12 AM
  • Try this..

    declare @InputInt int = 1234567890
    
    Select stuff(stuff(stuff(@InputInt, Len(@InputInt)-2, 0, ' '), Len(@InputInt)-5, 0, ' '), Len(@InputInt)-8, 0, ' ')
    Friday, August 9, 2019 11:31 AM
  • Hello Klaus,

    unfortunately you didn't understand correctly to my question.

    I want to know how to write only one T-SQL script for all versions of MS-SQL-Server. And inside of it when it detects old version (<2012), then it will use CONVERT. And when it detects new version (>=2012), then it will use FORMAT.


    __________________________________________________________ If isn't above described anything, the following applies: Technical details: * OS: Windows Server v2008-R2, English, Enterprise Edition, x64, SP1 ** My User-Account is member of 'Administrators' local security group. * MS-SQL-Server: v2008-R2, English, Enterprise Edition, x64, SP1 ** My User-Account is member of 'SysAdmin' db-role.

    Friday, August 9, 2019 11:36 AM
  • A script is compiled completely before execution. Attempts to compile a script in a 2008 engine will generate errors if you use functionality that does not exist in that engine. So you will need to resort to dynamic to write a single script that works in all versions. 
    Friday, August 9, 2019 11:50 AM
  • In other words: how to re-write line „set @NumberAsText = FORMAT(@InputInt,'### ### ### ### ###');“?

    By something like „
    set @TSQL = N'FORMAT(@InputInt,''### ### ### ### ###'')';

    EXECUTE(@TSQL);

    You can use a parameterized dynamic SQL statement as below. Or you could use one of the suggested methods that will work on both earlier and later versions to avoid the conditional execution and dynamic SQL.

    EXEC sp_executesql N'SET @NumberAsText = FORMAT(@InputInt,''### ### ### ### ###'');', N'@InputInt int, @NumberAsText nvarchar(30) OUTPUT', @InputInt= @InputInt,  @NumberAsText = @NumberAsText OUTPUT;


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    • Edited by Dan GuzmanMVP Friday, August 9, 2019 11:52 AM
    • Marked as answer by David KRIZ Friday, August 9, 2019 12:30 PM
    Friday, August 9, 2019 11:50 AM
  • Yes! Thank you very much! It is exactly what I cannot remember.
    Just FYI - my final version is:

    EXEC

    sp_executesqlN'SET @NumberAsTextOut = FORMAT(@InputIntPar,''### ### ### ### ###'');'


    ,N'@InputIntPar int, @NumberAsTextOut nvarchar(30) OUTPUT',@InputIntPar =@InputInt,@NumberAsTextOut =@NumberAsText OUTPUT;


    __________________________________________________________ If isn't above described anything, the following applies: Technical details: * OS: Windows Server v2008-R2, English, Enterprise Edition, x64, SP1 ** My User-Account is member of 'Administrators' local security group. * MS-SQL-Server: v2008-R2, English, Enterprise Edition, x64, SP1 ** My User-Account is member of 'SysAdmin' db-role.

    Friday, August 9, 2019 12:30 PM