jsonsql-serverdynamicdynamicqueryfor-json

Omitting Scientific Notation in Numeric columns when working with Dynamic SQL returning JSON

The below dynamic query returns the output of the numeric columns in Scientific notation. 

DECLARE @Cols VARCHAR(MAX) = (SELECT STUFF((
SELECT ',' +'['+ Description + ']' FROM @PermittedColumnIDs

DECLARE @Query NVARCHAR(MAX) = 'SELECT TOP 1000 '+ @Cols +' FROM ' + (SELECT ViewName FROM
@DynamicQueryProps) + ' FOR JSON AUTO';
EXECUTE sp_executesql @Query

JSON output

JSON

Tabular output

Table

As above mentioned, tabular view returns the AWP value properly and JSON view returns it with the scientific notation. How to get the JSON AWP column without Sc. notation.

Please note that

Solution

  • It's too long for a comment, so I post this as an answer. I'm able to find only these explanations in the documentation about how FOR JSON converts SQL Server data types to JSON types. So, as a possible workaround, you may try to convert the float columns to numeric using information from system catalog views (I assume, that the SELECT is against a view).

    DECLARE @cols varchar(MAX) = STUFF(
    (
    SELECT 
        ', ' +
        CASE 
           WHEN t.[name] = 'float' THEN 'CONVERT(numeric(10, 2), [' + p.[description] + ']) AS [' + p.[description] + N'] '
           ELSE p.[description] 
        END
    FROM sys.columns c
    JOIN sys.views v ON c.object_id = v.object_id
    JOIN sys.schemas s ON v.schema_id = s.schema_id
    JOIN sys.types t ON c.system_type_id = t.system_type_id
    JOIN @PermittedColumnIDs p ON p.[description] = c.[name]
    WHERE v.[name] = (SELECT ViewName FROM @DynamicQueryProps) AND s.[name] = 'dbo'
    FOR XML PATH(''), TYPE
    ).value('.', 'varchar(max)'), 1, 1, ''
)

DECLARE @query nvarchar(max) 
SET @query = 
   N' SELECT TOP 1000 '+ @Cols +
   N' FROM [' + (SELECT ViewName FROM @DynamicQueryProps) + ']' +
   N' FOR JSON AUTO';

EXECUTE sp_executesql @query