SQL Server

Using JSON StringOf with Non-String Members

See more JSON Examples

If a JSON member is a boolean, integer, or null, using StringOf will return its string representation. SQL Server

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @sTmp0 nvarchar(4000)
    --  Create JSON with members of different data types.
    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'a', 123
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'b', 1
    EXEC sp_OAMethod @json, 'UpdateNull', @success OUT, 'c'

    EXEC sp_OASetProperty @json, 'EmitCompact', 0
    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    --  Resulting JSON:
    --  {
    --    "a": 123,
    --    "b": true,
    --    "c": null
    --  }

    DECLARE @a nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @a OUT, 'a'

    PRINT @a

    DECLARE @b nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @b OUT, 'b'

    PRINT @b

    DECLARE @c nvarchar(4000)
    EXEC sp_OAMethod @json, 'StringOf', @c OUT, 'c'

    PRINT @c

    --  Output

    --  123
    --  true
    --  null

    --  If you want to get the integer, boolean, or null value
    --  you need to use the methods matching the data type
    DECLARE @ival int
    EXEC sp_OAMethod @json, 'IntOf', @ival OUT, 'a'
    DECLARE @bval int
    EXEC sp_OAMethod @json, 'BoolOf', @bval OUT, 'b'
    DECLARE @hasNull int
    EXEC sp_OAMethod @json, 'IsNullOf', @hasNull OUT, 'c'

    EXEC @hr = sp_OADestroy @json


END
GO