SugarCRM Updating a Record

-- 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)
    DECLARE @success int
    SELECT @success = 0

    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    -- Implements the following CURL command:

    -- curl -X PUT -H OAuth-Token:<access_token> -H Cache-Control:no-cache -d '{ 
    --    "name":"Updated Record"
    -- }' http://<site_url>/rest/v10/Accounts/<record_id>

    -- Use this online tool to generate code from sample JSON:
    -- Generate Code to Create JSON

    -- The following JSON is sent in the request body.

    -- {
    --   "name": "Updated Record"
    -- }

    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'name', 'Updated Record'

    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Cache-Control', 'no-cache'
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'OAuth-Token', '<access_token>'

    DECLARE @sbRequestBody int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbRequestBody OUT

    EXEC sp_OAMethod @json, 'EmitSb', @success OUT, @sbRequestBody

    DECLARE @resp int
    EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT

    EXEC sp_OAMethod @http, 'HttpSb', @success OUT, 'PUT', 'http://<site_url>/rest/v10/Accounts/<record_id>', @sbRequestBody, 'utf-8', 'application/json', @resp
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sbRequestBody
        EXEC @hr = sp_OADestroy @resp
        RETURN
      END

    DECLARE @sbResponseBody int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT

    EXEC sp_OAMethod @resp, 'GetBodySb', @success OUT, @sbResponseBody
    DECLARE @jResp int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jResp OUT

    EXEC sp_OAMethod @jResp, 'LoadSb', @success OUT, @sbResponseBody
    EXEC sp_OASetProperty @jResp, 'EmitCompact', 0


    PRINT 'Response Body:'
    EXEC sp_OAMethod @jResp, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    DECLARE @respStatusCode int
    EXEC sp_OAGetProperty @resp, 'StatusCode', @respStatusCode OUT

    PRINT 'Response Status Code = ' + @respStatusCode
    IF @respStatusCode >= 400
      BEGIN

        PRINT 'Response Header:'
        EXEC sp_OAGetProperty @resp, 'Header', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sbRequestBody
        EXEC @hr = sp_OADestroy @resp
        EXEC @hr = sp_OADestroy @sbResponseBody
        EXEC @hr = sp_OADestroy @jResp
        RETURN
      END

    -- Sample JSON response:
    -- (Sample code for parsing the JSON response is shown below)

    -- {
    --   "id": "ab2222df-73da-0e92-6887-5705428f4d68",
    --   "name": "Updated Test Record",
    --   "date_entered": "2016-04-06T15:03:21-04:00",
    --   "date_modified": "2016-04-06T15:03:22-04:00",
    --   "modified_user_id": "1",
    --   "modified_by_name": "Administrator",
    --   "modified_user_link": {
    --     "full_name": "Administrator",
    --     "id": "1",
    --     "_acl": {
    --       "fields": [
    --       ],
    --       "delete": "no",
    --       "_hash": "8e11bf9be8f04daddee9d08d44ea891e"
    --     }
    --   },
    --   "created_by": "1",
    --   "created_by_name": "Administrator",
    --   "created_by_link": {
    --     "full_name": "Administrator",
    --     "id": "1",
    --     "_acl": {
    --       "fields": [
    --       ],
    --       "delete": "no",
    --       "_hash": "8e11bf9be8f04daddee9d08d44ea891e"
    --     }
    --   },
    --   "description": "",
    --   "deleted": false,
    --   "facebook": "",
    --   "twitter": "",
    --   "googleplus": "",
    --   "account_type": "",
    --   "industry": "",
    --   "annual_revenue": "",
    --   "phone_fax": "",
    --   "billing_address_street": "",
    --   "billing_address_street_2": "",
    --   "billing_address_street_3": "",
    --   "billing_address_street_4": "",
    --   "billing_address_city": "",
    --   "billing_address_state": "",
    --   "billing_address_postalcode": "",
    --   "billing_address_country": "",
    --   "rating": "",
    --   "phone_office": "",
    --   "phone_alternate": "",
    --   "website": "",
    --   "ownership": "",
    --   "employees": "",
    --   "ticker_symbol": "",
    --   "shipping_address_street": "",
    --   "shipping_address_street_2": "",
    --   "shipping_address_street_3": "",
    --   "shipping_address_street_4": "",
    --   "shipping_address_city": "",
    --   "shipping_address_state": "",
    --   "shipping_address_postalcode": "",
    --   "shipping_address_country": "",
    --   "parent_id": "",
    --   "sic_code": "",
    --   "duns_num": "",
    --   "parent_name": "",
    --   "member_of": {
    --     "name": "",
    --     "id": "",
    --     "_acl": {
    --       "fields": [
    --       ],
    --       "_hash": "654d337e0e912edaa00dbb0fb3dc3c17"
    --     }
    --   },
    --   "campaign_id": "",
    --   "campaign_name": "",
    --   "campaign_accounts": {
    --     "name": "",
    --     "id": "",
    --     "_acl": {
    --       "fields": [
    --       ],
    --       "_hash": "654d337e0e912edaa00dbb0fb3dc3c17"
    --     }
    --   },
    --   "following": true,
    --   "my_favorite": false,
    --   "tag": [
    --   ],
    --   "assigned_user_id": "",
    --   "assigned_user_name": "",
    --   "assigned_user_link": {
    --     "full_name": "",
    --     "id": "",
    --     "_acl": {
    --       "fields": [
    --       ],
    --       "_hash": "654d337e0e912edaa00dbb0fb3dc3c17"
    --     }
    --   },
    --   "team_count": "",
    --   "team_count_link": {
    --     "team_count": "",
    --     "id": "1",
    --     "_acl": {
    --       "fields": [
    --       ],
    --       "_hash": "654d337e0e912edaa00dbb0fb3dc3c17"
    --     }
    --   },
    --   "team_name": [
    --     {
    --       "id": 1,
    --       "name": "Global",
    --       "name_2": "",
    --       "primary": true
    --     }
    --   ],
    --   "email": [
    --     {
    --       "email_address": "test@sugar.com",
    --       "invalid_email": false,
    --       "opt_out": false,
    --       "primary_address": true,
    --       "reply_to_address": false
    --     }
    --   ],
    --   "email1": "test@sugar.com",
    --   "email2": "",
    --   "invalid_email": false,
    --   "email_opt_out": false,
    --   "email_addresses_non_primary": "",
    --   "_acl": {
    --     "fields": {}
    --   },
    --   "_module": "Accounts"
    -- }

    -- Sample code for parsing the JSON response...
    -- Use the following online tool to generate parsing code from sample JSON:
    -- Generate Parsing Code from JSON

    DECLARE @id nvarchar(4000)

    DECLARE @name nvarchar(4000)

    DECLARE @date_entered nvarchar(4000)

    DECLARE @date_modified nvarchar(4000)

    DECLARE @modified_user_id nvarchar(4000)

    DECLARE @modified_by_name nvarchar(4000)

    DECLARE @modified_user_linkFull_name nvarchar(4000)

    DECLARE @modified_user_linkId nvarchar(4000)

    DECLARE @modified_user_link_aclDelete nvarchar(4000)

    DECLARE @modified_user_link_acl_hash nvarchar(4000)

    DECLARE @created_by nvarchar(4000)

    DECLARE @created_by_name nvarchar(4000)

    DECLARE @created_by_linkFull_name nvarchar(4000)

    DECLARE @created_by_linkId nvarchar(4000)

    DECLARE @created_by_link_aclDelete nvarchar(4000)

    DECLARE @created_by_link_acl_hash nvarchar(4000)

    DECLARE @description nvarchar(4000)

    DECLARE @deleted int

    DECLARE @facebook nvarchar(4000)

    DECLARE @twitter nvarchar(4000)

    DECLARE @googleplus nvarchar(4000)

    DECLARE @account_type nvarchar(4000)

    DECLARE @industry nvarchar(4000)

    DECLARE @annual_revenue nvarchar(4000)

    DECLARE @phone_fax nvarchar(4000)

    DECLARE @billing_address_street nvarchar(4000)

    DECLARE @billing_address_street_2 nvarchar(4000)

    DECLARE @billing_address_street_3 nvarchar(4000)

    DECLARE @billing_address_street_4 nvarchar(4000)

    DECLARE @billing_address_city nvarchar(4000)

    DECLARE @billing_address_state nvarchar(4000)

    DECLARE @billing_address_postalcode nvarchar(4000)

    DECLARE @billing_address_country nvarchar(4000)

    DECLARE @rating nvarchar(4000)

    DECLARE @phone_office nvarchar(4000)

    DECLARE @phone_alternate nvarchar(4000)

    DECLARE @website nvarchar(4000)

    DECLARE @ownership nvarchar(4000)

    DECLARE @employees nvarchar(4000)

    DECLARE @ticker_symbol nvarchar(4000)

    DECLARE @shipping_address_street nvarchar(4000)

    DECLARE @shipping_address_street_2 nvarchar(4000)

    DECLARE @shipping_address_street_3 nvarchar(4000)

    DECLARE @shipping_address_street_4 nvarchar(4000)

    DECLARE @shipping_address_city nvarchar(4000)

    DECLARE @shipping_address_state nvarchar(4000)

    DECLARE @shipping_address_postalcode nvarchar(4000)

    DECLARE @shipping_address_country nvarchar(4000)

    DECLARE @parent_id nvarchar(4000)

    DECLARE @sic_code nvarchar(4000)

    DECLARE @duns_num nvarchar(4000)

    DECLARE @parent_name nvarchar(4000)

    DECLARE @member_ofName nvarchar(4000)

    DECLARE @member_ofId nvarchar(4000)

    DECLARE @member_of_acl_hash nvarchar(4000)

    DECLARE @campaign_id nvarchar(4000)

    DECLARE @campaign_name nvarchar(4000)

    DECLARE @campaign_accountsName nvarchar(4000)

    DECLARE @campaign_accountsId nvarchar(4000)

    DECLARE @campaign_accounts_acl_hash nvarchar(4000)

    DECLARE @following int

    DECLARE @my_favorite int

    DECLARE @assigned_user_id nvarchar(4000)

    DECLARE @assigned_user_name nvarchar(4000)

    DECLARE @assigned_user_linkFull_name nvarchar(4000)

    DECLARE @assigned_user_linkId nvarchar(4000)

    DECLARE @assigned_user_link_acl_hash nvarchar(4000)

    DECLARE @team_count nvarchar(4000)

    DECLARE @team_count_linkTeam_count nvarchar(4000)

    DECLARE @team_count_linkId nvarchar(4000)

    DECLARE @team_count_link_acl_hash nvarchar(4000)

    DECLARE @email1 nvarchar(4000)

    DECLARE @email2 nvarchar(4000)

    DECLARE @invalid_email int

    DECLARE @email_opt_out int

    DECLARE @email_addresses_non_primary nvarchar(4000)

    DECLARE @v_module nvarchar(4000)

    DECLARE @i int

    DECLARE @count_i int

    DECLARE @id_int int

    DECLARE @name_2 nvarchar(4000)

    DECLARE @primary int

    DECLARE @email_address nvarchar(4000)

    DECLARE @opt_out int

    DECLARE @primary_address int

    DECLARE @reply_to_address int

    EXEC sp_OAMethod @jResp, 'StringOf', @id OUT, 'id'
    EXEC sp_OAMethod @jResp, 'StringOf', @name OUT, 'name'
    EXEC sp_OAMethod @jResp, 'StringOf', @date_entered OUT, 'date_entered'
    EXEC sp_OAMethod @jResp, 'StringOf', @date_modified OUT, 'date_modified'
    EXEC sp_OAMethod @jResp, 'StringOf', @modified_user_id OUT, 'modified_user_id'
    EXEC sp_OAMethod @jResp, 'StringOf', @modified_by_name OUT, 'modified_by_name'
    EXEC sp_OAMethod @jResp, 'StringOf', @modified_user_linkFull_name OUT, 'modified_user_link.full_name'
    EXEC sp_OAMethod @jResp, 'StringOf', @modified_user_linkId OUT, 'modified_user_link.id'
    EXEC sp_OAMethod @jResp, 'StringOf', @modified_user_link_aclDelete OUT, 'modified_user_link._acl.delete'
    EXEC sp_OAMethod @jResp, 'StringOf', @modified_user_link_acl_hash OUT, 'modified_user_link._acl._hash'
    EXEC sp_OAMethod @jResp, 'StringOf', @created_by OUT, 'created_by'
    EXEC sp_OAMethod @jResp, 'StringOf', @created_by_name OUT, 'created_by_name'
    EXEC sp_OAMethod @jResp, 'StringOf', @created_by_linkFull_name OUT, 'created_by_link.full_name'
    EXEC sp_OAMethod @jResp, 'StringOf', @created_by_linkId OUT, 'created_by_link.id'
    EXEC sp_OAMethod @jResp, 'StringOf', @created_by_link_aclDelete OUT, 'created_by_link._acl.delete'
    EXEC sp_OAMethod @jResp, 'StringOf', @created_by_link_acl_hash OUT, 'created_by_link._acl._hash'
    EXEC sp_OAMethod @jResp, 'StringOf', @description OUT, 'description'
    EXEC sp_OAMethod @jResp, 'BoolOf', @deleted OUT, 'deleted'
    EXEC sp_OAMethod @jResp, 'StringOf', @facebook OUT, 'facebook'
    EXEC sp_OAMethod @jResp, 'StringOf', @twitter OUT, 'twitter'
    EXEC sp_OAMethod @jResp, 'StringOf', @googleplus OUT, 'googleplus'
    EXEC sp_OAMethod @jResp, 'StringOf', @account_type OUT, 'account_type'
    EXEC sp_OAMethod @jResp, 'StringOf', @industry OUT, 'industry'
    EXEC sp_OAMethod @jResp, 'StringOf', @annual_revenue OUT, 'annual_revenue'
    EXEC sp_OAMethod @jResp, 'StringOf', @phone_fax OUT, 'phone_fax'
    EXEC sp_OAMethod @jResp, 'StringOf', @billing_address_street OUT, 'billing_address_street'
    EXEC sp_OAMethod @jResp, 'StringOf', @billing_address_street_2 OUT, 'billing_address_street_2'
    EXEC sp_OAMethod @jResp, 'StringOf', @billing_address_street_3 OUT, 'billing_address_street_3'
    EXEC sp_OAMethod @jResp, 'StringOf', @billing_address_street_4 OUT, 'billing_address_street_4'
    EXEC sp_OAMethod @jResp, 'StringOf', @billing_address_city OUT, 'billing_address_city'
    EXEC sp_OAMethod @jResp, 'StringOf', @billing_address_state OUT, 'billing_address_state'
    EXEC sp_OAMethod @jResp, 'StringOf', @billing_address_postalcode OUT, 'billing_address_postalcode'
    EXEC sp_OAMethod @jResp, 'StringOf', @billing_address_country OUT, 'billing_address_country'
    EXEC sp_OAMethod @jResp, 'StringOf', @rating OUT, 'rating'
    EXEC sp_OAMethod @jResp, 'StringOf', @phone_office OUT, 'phone_office'
    EXEC sp_OAMethod @jResp, 'StringOf', @phone_alternate OUT, 'phone_alternate'
    EXEC sp_OAMethod @jResp, 'StringOf', @website OUT, 'website'
    EXEC sp_OAMethod @jResp, 'StringOf', @ownership OUT, 'ownership'
    EXEC sp_OAMethod @jResp, 'StringOf', @employees OUT, 'employees'
    EXEC sp_OAMethod @jResp, 'StringOf', @ticker_symbol OUT, 'ticker_symbol'
    EXEC sp_OAMethod @jResp, 'StringOf', @shipping_address_street OUT, 'shipping_address_street'
    EXEC sp_OAMethod @jResp, 'StringOf', @shipping_address_street_2 OUT, 'shipping_address_street_2'
    EXEC sp_OAMethod @jResp, 'StringOf', @shipping_address_street_3 OUT, 'shipping_address_street_3'
    EXEC sp_OAMethod @jResp, 'StringOf', @shipping_address_street_4 OUT, 'shipping_address_street_4'
    EXEC sp_OAMethod @jResp, 'StringOf', @shipping_address_city OUT, 'shipping_address_city'
    EXEC sp_OAMethod @jResp, 'StringOf', @shipping_address_state OUT, 'shipping_address_state'
    EXEC sp_OAMethod @jResp, 'StringOf', @shipping_address_postalcode OUT, 'shipping_address_postalcode'
    EXEC sp_OAMethod @jResp, 'StringOf', @shipping_address_country OUT, 'shipping_address_country'
    EXEC sp_OAMethod @jResp, 'StringOf', @parent_id OUT, 'parent_id'
    EXEC sp_OAMethod @jResp, 'StringOf', @sic_code OUT, 'sic_code'
    EXEC sp_OAMethod @jResp, 'StringOf', @duns_num OUT, 'duns_num'
    EXEC sp_OAMethod @jResp, 'StringOf', @parent_name OUT, 'parent_name'
    EXEC sp_OAMethod @jResp, 'StringOf', @member_ofName OUT, 'member_of.name'
    EXEC sp_OAMethod @jResp, 'StringOf', @member_ofId OUT, 'member_of.id'
    EXEC sp_OAMethod @jResp, 'StringOf', @member_of_acl_hash OUT, 'member_of._acl._hash'
    EXEC sp_OAMethod @jResp, 'StringOf', @campaign_id OUT, 'campaign_id'
    EXEC sp_OAMethod @jResp, 'StringOf', @campaign_name OUT, 'campaign_name'
    EXEC sp_OAMethod @jResp, 'StringOf', @campaign_accountsName OUT, 'campaign_accounts.name'
    EXEC sp_OAMethod @jResp, 'StringOf', @campaign_accountsId OUT, 'campaign_accounts.id'
    EXEC sp_OAMethod @jResp, 'StringOf', @campaign_accounts_acl_hash OUT, 'campaign_accounts._acl._hash'
    EXEC sp_OAMethod @jResp, 'BoolOf', @following OUT, 'following'
    EXEC sp_OAMethod @jResp, 'BoolOf', @my_favorite OUT, 'my_favorite'
    EXEC sp_OAMethod @jResp, 'StringOf', @assigned_user_id OUT, 'assigned_user_id'
    EXEC sp_OAMethod @jResp, 'StringOf', @assigned_user_name OUT, 'assigned_user_name'
    EXEC sp_OAMethod @jResp, 'StringOf', @assigned_user_linkFull_name OUT, 'assigned_user_link.full_name'
    EXEC sp_OAMethod @jResp, 'StringOf', @assigned_user_linkId OUT, 'assigned_user_link.id'
    EXEC sp_OAMethod @jResp, 'StringOf', @assigned_user_link_acl_hash OUT, 'assigned_user_link._acl._hash'
    EXEC sp_OAMethod @jResp, 'StringOf', @team_count OUT, 'team_count'
    EXEC sp_OAMethod @jResp, 'StringOf', @team_count_linkTeam_count OUT, 'team_count_link.team_count'
    EXEC sp_OAMethod @jResp, 'StringOf', @team_count_linkId OUT, 'team_count_link.id'
    EXEC sp_OAMethod @jResp, 'StringOf', @team_count_link_acl_hash OUT, 'team_count_link._acl._hash'
    EXEC sp_OAMethod @jResp, 'StringOf', @email1 OUT, 'email1'
    EXEC sp_OAMethod @jResp, 'StringOf', @email2 OUT, 'email2'
    EXEC sp_OAMethod @jResp, 'BoolOf', @invalid_email OUT, 'invalid_email'
    EXEC sp_OAMethod @jResp, 'BoolOf', @email_opt_out OUT, 'email_opt_out'
    EXEC sp_OAMethod @jResp, 'StringOf', @email_addresses_non_primary OUT, 'email_addresses_non_primary'
    EXEC sp_OAMethod @jResp, 'StringOf', @v_module OUT, '_module'
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'modified_user_link._acl.fields'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'created_by_link._acl.fields'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'member_of._acl.fields'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'campaign_accounts._acl.fields'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'tag'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'assigned_user_link._acl.fields'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'team_count_link._acl.fields'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'team_name'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        EXEC sp_OAMethod @jResp, 'IntOf', @id_int OUT, 'team_name[i].id'
        EXEC sp_OAMethod @jResp, 'StringOf', @name OUT, 'team_name[i].name'
        EXEC sp_OAMethod @jResp, 'StringOf', @name_2 OUT, 'team_name[i].name_2'
        EXEC sp_OAMethod @jResp, 'BoolOf', @primary OUT, 'team_name[i].primary'
        SELECT @i = @i + 1
      END
    SELECT @i = 0
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'email'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        EXEC sp_OAMethod @jResp, 'StringOf', @email_address OUT, 'email[i].email_address'
        EXEC sp_OAMethod @jResp, 'BoolOf', @invalid_email OUT, 'email[i].invalid_email'
        EXEC sp_OAMethod @jResp, 'BoolOf', @opt_out OUT, 'email[i].opt_out'
        EXEC sp_OAMethod @jResp, 'BoolOf', @primary_address OUT, 'email[i].primary_address'
        EXEC sp_OAMethod @jResp, 'BoolOf', @reply_to_address OUT, 'email[i].reply_to_address'
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @sbRequestBody
    EXEC @hr = sp_OADestroy @resp
    EXEC @hr = sp_OADestroy @sbResponseBody
    EXEC @hr = sp_OADestroy @jResp


END
GO