(SQL Server) Cerved API Advanced Entity Search

The "Advanced Entity Search" API allows you to find subjects in the Cerved database that meet specific search criteria, specified through the parameters provided as input to the service.

For more information, see https://apps-developer.cerved.com/

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
    DECLARE @iTmp0 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    -- Implements the following CURL command:

    -- curl -X POST \
    --   https://api.cerved.com/cervedApi/v1/entitySearch/advanced \
    --   -H 'accept: application/json' \
    --   -H 'apikey: ********************************' \
    -- -d '{
    --   "search_text": "string",
    --   "activity_status_codes": [
    --     "A",
    --     "C",
    --     "D",
    --     "F",
    --     "I",
    --     "L",
    --     "N",
    --     "P",
    --     "R",
    --     "S",
    --     "T"
    --   ],
    --   "birth_date": "string",
    --   "name": "string",
    --   "subject_types": [
    --     "PERSON",
    --     "INDIVIDUAL_COMPANY",
    --     "COMPANY",
    --     "FOREIGN",
    --     "OTHER"
    --   ],
    --   "tax_code": "string",
    --   "vat_number": "string",
    --   "rea": {
    --     "cciaa": "string",
    --     "rea_number": 0
    --   },
    --   "city_istat_code": "string",
    --   "province_code": "string"
    -- }'

    -- Use the following online tool to generate HTTP code from a CURL command
    -- Convert a cURL Command to HTTP Source Code

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

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

    -- {
    --   "search_text": "string",
    --   "activity_status_codes": [
    --     "A",
    --     "C",
    --     "D",
    --     "F",
    --     "I",
    --     "L",
    --     "N",
    --     "P",
    --     "R",
    --     "S",
    --     "T"
    --   ],
    --   "birth_date": "string",
    --   "name": "string",
    --   "subject_types": [
    --     "PERSON",
    --     "INDIVIDUAL_COMPANY",
    --     "COMPANY",
    --     "FOREIGN",
    --     "OTHER"
    --   ],
    --   "tax_code": "string",
    --   "vat_number": "string",
    --   "rea": {
    --     "cciaa": "string",
    --     "rea_number": 0
    --   },
    --   "city_istat_code": "string",
    --   "province_code": "string"
    -- }

    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    -- IMPORTANT:  Only include the lines for the search criteria you wish to include.
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'search_text', 'string'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'activity_status_codes[0]', 'A'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'activity_status_codes[1]', 'C'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'activity_status_codes[2]', 'D'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'activity_status_codes[3]', 'F'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'activity_status_codes[4]', 'I'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'activity_status_codes[5]', 'L'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'activity_status_codes[6]', 'N'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'activity_status_codes[7]', 'P'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'activity_status_codes[8]', 'R'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'activity_status_codes[9]', 'S'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'activity_status_codes[10]', 'T'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'birth_date', 'string'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'name', 'string'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'subject_types[0]', 'PERSON'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'subject_types[1]', 'INDIVIDUAL_COMPANY'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'subject_types[2]', 'COMPANY'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'subject_types[3]', 'FOREIGN'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'subject_types[4]', 'OTHER'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'tax_code', 'string'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'vat_number', 'string'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'rea.cciaa', 'string'
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'rea.rea_number', 0
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'city_istat_code', 'string'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'province_code', 'string'

    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'accept', 'application/json'
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'apikey', '********************************'

    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PostJson3', @resp OUT, 'https://api.cerved.com/cervedApi/v1/entitySearch/advanced', 'application/json', @json
    EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @json
        RETURN
      END

    DECLARE @sbResponseBody int
    -- Use "Chilkat_9_5_0.StringBuilder" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseBody OUT

    EXEC sp_OAMethod @resp, 'GetBodySb', @success OUT, @sbResponseBody
    DECLARE @jResp int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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 @resp

        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sbResponseBody
        EXEC @hr = sp_OADestroy @jResp
        RETURN
      END
    EXEC @hr = sp_OADestroy @resp

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

    -- {
    --   "subjects": [
    --     {
    --       "subject_id": "string",
    --       "subject_type": "string",
    --       "tax_code": "string",
    --       "address": {
    --         "street": {
    --           "description": "string"
    --         },
    --         "city": {
    --           "code": "string",
    --           "istat_code": "string",
    --           "description": "string"
    --         },
    --         "province": {
    --           "code": "string",
    --           "description": "string"
    --         },
    --         "postal_code": "string",
    --         "country": {
    --           "code": "string",
    --           "description": "string"
    --         }
    --       },
    --       "company_info": {
    --         "legal_form": {
    --           "code": "string",
    --           "description": "string",
    --           "class_code": "string"
    --         },
    --         "activity_status": {
    --           "code": "string",
    --           "description": "string"
    --         },
    --         "business_name": "string",
    --         "economic_activity": {
    --           "ateco": {
    --             "code": "string",
    --             "description": "string"
    --           }
    --         },
    --         "head_office": "true",
    --         "lei_code": "string",
    --         "operational_flag": "true",
    --         "public_administration": {
    --           "pa_subject": "true",
    --           "pa_supplier": "true",
    --           "pa_owned": "true"
    --         },
    --         "rea_code": {
    --           "cciaa": "string",
    --           "rea_number": 0,
    --           "registration_date": "string"
    --         },
    --         "vat_number": "string",
    --         "no_rea_data": {
    --           "form_code": "string",
    --           "form_description": "string"
    --         }
    --       },
    --       "person_info": {
    --         "first_name": "string",
    --         "last_name": "string",
    --         "name": "string",
    --         "birth_date": "string",
    --         "birth_place": {
    --           "street": {
    --             "description": "string"
    --           },
    --           "city": {
    --             "code": "string",
    --             "istat_code": "string",
    --             "description": "string"
    --           },
    --           "province": {
    --             "code": "string",
    --             "description": "string"
    --           },
    --           "postal_code": "string",
    --           "country": {
    --             "code": "string",
    --             "description": "string"
    --           }
    --         }
    --       }
    --     }
    --   ],
    --   "subjects_total_number": 0
    -- }

    -- 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 @subject_id nvarchar(4000)

    DECLARE @subject_type nvarchar(4000)

    DECLARE @tax_code nvarchar(4000)

    DECLARE @addressStreetDescription nvarchar(4000)

    DECLARE @addressCityCode nvarchar(4000)

    DECLARE @addressCityIstat_code nvarchar(4000)

    DECLARE @addressCityDescription nvarchar(4000)

    DECLARE @addressProvinceCode nvarchar(4000)

    DECLARE @addressProvinceDescription nvarchar(4000)

    DECLARE @addressPostal_code nvarchar(4000)

    DECLARE @addressCountryCode nvarchar(4000)

    DECLARE @addressCountryDescription nvarchar(4000)

    DECLARE @company_infoLegal_formCode nvarchar(4000)

    DECLARE @company_infoLegal_formDescription nvarchar(4000)

    DECLARE @company_infoLegal_formClass_code nvarchar(4000)

    DECLARE @company_infoActivity_statusCode nvarchar(4000)

    DECLARE @company_infoActivity_statusDescription nvarchar(4000)

    DECLARE @company_infoBusiness_name nvarchar(4000)

    DECLARE @company_infoEconomic_activityAtecoCode nvarchar(4000)

    DECLARE @company_infoEconomic_activityAtecoDescription nvarchar(4000)

    DECLARE @company_infoHead_office nvarchar(4000)

    DECLARE @company_infoLei_code nvarchar(4000)

    DECLARE @company_infoOperational_flag nvarchar(4000)

    DECLARE @company_infoPublic_administrationPa_subject nvarchar(4000)

    DECLARE @company_infoPublic_administrationPa_supplier nvarchar(4000)

    DECLARE @company_infoPublic_administrationPa_owned nvarchar(4000)

    DECLARE @company_infoRea_codeCciaa nvarchar(4000)

    DECLARE @company_infoRea_codeRea_number int

    DECLARE @company_infoRea_codeRegistration_date nvarchar(4000)

    DECLARE @company_infoVat_number nvarchar(4000)

    DECLARE @company_infoNo_rea_dataForm_code nvarchar(4000)

    DECLARE @company_infoNo_rea_dataForm_description nvarchar(4000)

    DECLARE @person_infoFirst_name nvarchar(4000)

    DECLARE @person_infoLast_name nvarchar(4000)

    DECLARE @person_infoName nvarchar(4000)

    DECLARE @person_infoBirth_date nvarchar(4000)

    DECLARE @person_infoBirth_placeStreetDescription nvarchar(4000)

    DECLARE @person_infoBirth_placeCityCode nvarchar(4000)

    DECLARE @person_infoBirth_placeCityIstat_code nvarchar(4000)

    DECLARE @person_infoBirth_placeCityDescription nvarchar(4000)

    DECLARE @person_infoBirth_placeProvinceCode nvarchar(4000)

    DECLARE @person_infoBirth_placeProvinceDescription nvarchar(4000)

    DECLARE @person_infoBirth_placePostal_code nvarchar(4000)

    DECLARE @person_infoBirth_placeCountryCode nvarchar(4000)

    DECLARE @person_infoBirth_placeCountryDescription nvarchar(4000)

    DECLARE @subjects_total_number int
    EXEC sp_OAMethod @jResp, 'IntOf', @subjects_total_number OUT, 'subjects_total_number'
    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @jResp, 'SizeOfArray', @count_i OUT, 'subjects'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jResp, 'I', @i
        EXEC sp_OAMethod @jResp, 'StringOf', @subject_id OUT, 'subjects[i].subject_id'
        EXEC sp_OAMethod @jResp, 'StringOf', @subject_type OUT, 'subjects[i].subject_type'
        EXEC sp_OAMethod @jResp, 'StringOf', @tax_code OUT, 'subjects[i].tax_code'
        EXEC sp_OAMethod @jResp, 'StringOf', @addressStreetDescription OUT, 'subjects[i].address.street.description'
        EXEC sp_OAMethod @jResp, 'StringOf', @addressCityCode OUT, 'subjects[i].address.city.code'
        EXEC sp_OAMethod @jResp, 'StringOf', @addressCityIstat_code OUT, 'subjects[i].address.city.istat_code'
        EXEC sp_OAMethod @jResp, 'StringOf', @addressCityDescription OUT, 'subjects[i].address.city.description'
        EXEC sp_OAMethod @jResp, 'StringOf', @addressProvinceCode OUT, 'subjects[i].address.province.code'
        EXEC sp_OAMethod @jResp, 'StringOf', @addressProvinceDescription OUT, 'subjects[i].address.province.description'
        EXEC sp_OAMethod @jResp, 'StringOf', @addressPostal_code OUT, 'subjects[i].address.postal_code'
        EXEC sp_OAMethod @jResp, 'StringOf', @addressCountryCode OUT, 'subjects[i].address.country.code'
        EXEC sp_OAMethod @jResp, 'StringOf', @addressCountryDescription OUT, 'subjects[i].address.country.description'
        EXEC sp_OAMethod @jResp, 'StringOf', @company_infoLegal_formCode OUT, 'subjects[i].company_info.legal_form.code'
        EXEC sp_OAMethod @jResp, 'StringOf', @company_infoLegal_formDescription OUT, 'subjects[i].company_info.legal_form.description'
        EXEC sp_OAMethod @jResp, 'StringOf', @company_infoLegal_formClass_code OUT, 'subjects[i].company_info.legal_form.class_code'
        EXEC sp_OAMethod @jResp, 'StringOf', @company_infoActivity_statusCode OUT, 'subjects[i].company_info.activity_status.code'
        EXEC sp_OAMethod @jResp, 'StringOf', @company_infoActivity_statusDescription OUT, 'subjects[i].company_info.activity_status.description'
        EXEC sp_OAMethod @jResp, 'StringOf', @company_infoBusiness_name OUT, 'subjects[i].company_info.business_name'
        EXEC sp_OAMethod @jResp, 'StringOf', @company_infoEconomic_activityAtecoCode OUT, 'subjects[i].company_info.economic_activity.ateco.code'
        EXEC sp_OAMethod @jResp, 'StringOf', @company_infoEconomic_activityAtecoDescription OUT, 'subjects[i].company_info.economic_activity.ateco.description'
        EXEC sp_OAMethod @jResp, 'StringOf', @company_infoHead_office OUT, 'subjects[i].company_info.head_office'
        EXEC sp_OAMethod @jResp, 'StringOf', @company_infoLei_code OUT, 'subjects[i].company_info.lei_code'
        EXEC sp_OAMethod @jResp, 'StringOf', @company_infoOperational_flag OUT, 'subjects[i].company_info.operational_flag'
        EXEC sp_OAMethod @jResp, 'StringOf', @company_infoPublic_administrationPa_subject OUT, 'subjects[i].company_info.public_administration.pa_subject'
        EXEC sp_OAMethod @jResp, 'StringOf', @company_infoPublic_administrationPa_supplier OUT, 'subjects[i].company_info.public_administration.pa_supplier'
        EXEC sp_OAMethod @jResp, 'StringOf', @company_infoPublic_administrationPa_owned OUT, 'subjects[i].company_info.public_administration.pa_owned'
        EXEC sp_OAMethod @jResp, 'StringOf', @company_infoRea_codeCciaa OUT, 'subjects[i].company_info.rea_code.cciaa'
        EXEC sp_OAMethod @jResp, 'IntOf', @company_infoRea_codeRea_number OUT, 'subjects[i].company_info.rea_code.rea_number'
        EXEC sp_OAMethod @jResp, 'StringOf', @company_infoRea_codeRegistration_date OUT, 'subjects[i].company_info.rea_code.registration_date'
        EXEC sp_OAMethod @jResp, 'StringOf', @company_infoVat_number OUT, 'subjects[i].company_info.vat_number'
        EXEC sp_OAMethod @jResp, 'StringOf', @company_infoNo_rea_dataForm_code OUT, 'subjects[i].company_info.no_rea_data.form_code'
        EXEC sp_OAMethod @jResp, 'StringOf', @company_infoNo_rea_dataForm_description OUT, 'subjects[i].company_info.no_rea_data.form_description'
        EXEC sp_OAMethod @jResp, 'StringOf', @person_infoFirst_name OUT, 'subjects[i].person_info.first_name'
        EXEC sp_OAMethod @jResp, 'StringOf', @person_infoLast_name OUT, 'subjects[i].person_info.last_name'
        EXEC sp_OAMethod @jResp, 'StringOf', @person_infoName OUT, 'subjects[i].person_info.name'
        EXEC sp_OAMethod @jResp, 'StringOf', @person_infoBirth_date OUT, 'subjects[i].person_info.birth_date'
        EXEC sp_OAMethod @jResp, 'StringOf', @person_infoBirth_placeStreetDescription OUT, 'subjects[i].person_info.birth_place.street.description'
        EXEC sp_OAMethod @jResp, 'StringOf', @person_infoBirth_placeCityCode OUT, 'subjects[i].person_info.birth_place.city.code'
        EXEC sp_OAMethod @jResp, 'StringOf', @person_infoBirth_placeCityIstat_code OUT, 'subjects[i].person_info.birth_place.city.istat_code'
        EXEC sp_OAMethod @jResp, 'StringOf', @person_infoBirth_placeCityDescription OUT, 'subjects[i].person_info.birth_place.city.description'
        EXEC sp_OAMethod @jResp, 'StringOf', @person_infoBirth_placeProvinceCode OUT, 'subjects[i].person_info.birth_place.province.code'
        EXEC sp_OAMethod @jResp, 'StringOf', @person_infoBirth_placeProvinceDescription OUT, 'subjects[i].person_info.birth_place.province.description'
        EXEC sp_OAMethod @jResp, 'StringOf', @person_infoBirth_placePostal_code OUT, 'subjects[i].person_info.birth_place.postal_code'
        EXEC sp_OAMethod @jResp, 'StringOf', @person_infoBirth_placeCountryCode OUT, 'subjects[i].person_info.birth_place.country.code'
        EXEC sp_OAMethod @jResp, 'StringOf', @person_infoBirth_placeCountryDescription OUT, 'subjects[i].person_info.birth_place.country.description'
        SELECT @i = @i + 1
      END

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


END
GO