As a follow-up to this FAQ, it seems that the sp_parse_json system procedure only supports numbers as double or integer: begin declare MyJson long varchar; select '{"MyDouble": -12345678901234.567890, "MyEValue": -1e6, "MyInt": -2147483648, "MyBigInt1": 2147483648, "MyBigInt2": -1234567890123456789, "MyBit": 1}' into MyJson; drop variable if exists sql_row; call sp_parse_json ('sql_row', MyJson); select '1 MyDouble' as "Variable", exprtype('select sql_row.MyDouble', 1) as Data_Type, sql_row.MyDouble as Value union all select '2 MyEValue', exprtype('select sql_row.MyEValue', 1), sql_row.MyEValue union all select '3 MyInt', exprtype('select sql_row.MyInt', 1), sql_row.MyInt union all select '4 MyBigInt1', exprtype('select sql_row.MyBigInt1', 1), sql_row.MyBigInt1 union all select '5 MyBigInt2', exprtype('select sql_row.MyBigInt2', 1), sql_row.MyBigInt2 union all select '6 MyBit', exprtype('select sql_row.MyBit', 1), sql_row.MyBit order by 1; end; returns 1 MyDouble;double;-12345678901234,568 2 MyEValue;double;-1000000 3 MyInt;integer;-2147483648 4 MyBigInt1;integer;2147483647 <-- wrong 5 MyBigInt2;integer;-2147483648 <-- wrong 6 MyBit;integer;1 This was tested with 17.0.10.6175. Resume: MyBigInt1 and MyBigInt2 are wrongly limited to the maximum positive resp. negative integer values, so it seems that JSON numbers in the format without fraction and exponent are wrongly casted to integer instead of bigint or other "huger" types. |
FWIW, it does not make a difference if the according variable used with sp_parse_json is declared to have a bigint component beforehand - it will nevertheless be returned as an integer:
returns