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

Question

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

Tabular output

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

It needs to preserve the numeric value as numeric without converting it to a string in the JSON result.
Cannot change the format of individual columns since columns are dynamic.

Probably `AWP`column is a `float` column. What is the information stored in the `@PermittedColumnIDs` table? — Zhorov, May 05 '20 at 07:58
One possible approach is to use `CONVERT(numeric(x, y), Description) AS Description` when you dynamically generate the `SELECT` statement for each `float` column. The only part from the documentation about how `FOR JSON` converts SQL Server data types to JSON types is [here](https://learn.microsoft.com/en-us/sql/relational-databases/json/how-for-json-converts-sql-server-data-types-to-json-data-types-sql-server?view=sql-server-ver15) and there is no information about the format that is used for this conversion. — Zhorov, May 05 '20 at 11:21
What if the dynamically selected column's data type is VARCHAR? Isn't this function tries to convert it also to a numeric? — Harsha W, May 05 '20 at 11:40
`DECLARE @Cols VARCHAR(MAX) = (SELECT STUFF(( SELECT ',' +'['+ s.Description + '] AS [' + REPLACE(s.Description, ' ', '') + ']' FROM @PermittedColumnIDs s ORDER BY s.ID FOR XML PATH('')),1, 1, '')) ` Could you please show me how to apply the above code segment in here — Harsha W, May 05 '20 at 12:28

Zhorov · Accepted Answer · 2020-05-05T13:16:09.307

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

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

1 Answers1