(SQL Server) Using JSON StringOf with Non-String Members

See more JSON Examples

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    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