SQL Server

REST with Query Params

See more REST Examples

Demonstrates how to add query params for a REST request. 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 @iTmp0 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 requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- Let's say we want to send a GET request to 
    -- https://example.com/search?query=hello%20world&category=books&sortBy=price&filterBy=inStock

    -- Notice that the query parameter values must be URL encoded.

    -- Let's also explain the parts of the above URL:

    --     Scheme: https
    --         The scheme specifies the protocol used to access the resource. 
    --         In this case, it is "https," which indicates that the resource is accessed using the Hypertext Transfer Protocol Secure (HTTPS).
    -- 
    --     Host: example.com
    --         The host part of the URL identifies the domain name or IP address of the server hosting the resource. In this case, "example.com" is the host.
    -- 
    --     Path: /search
    --         The path is the specific location or resource on the server that the client wants to access. 
    --         In this URL, the path is "/search," indicating that the client is requesting the "search" resource on the server.
    -- 
    --     Query Parameters:
    --         Query parameters are used to send additional data to the server as key-value pairs. 
    --         They are separated from the path by a question mark ? and each parameter is separated by an ampersand &.
    -- 
    --         The above URL has four query parameters:
    --             query=hello%20world: The "query" parameter with the value "hello world". The %20 represents the URL-encoded space character in the value.
    --             category=books: The "category" parameter with the value "books".
    --             sortBy=price: The "sortBy" parameter with the value "price".
    --             filterBy=inStock: The "filterBy" parameter with the value "inStock".

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

    -- Connect to the REST server.

    -- The Host part of the URL is passed in the 1st argument.
    -- The Scheme part of the URL ("https") is indicated by the 2nd and 3rd arguments (port and bTls).
    DECLARE @bTls int
    SELECT @bTls = 1
    DECLARE @port int
    SELECT @port = 443
    DECLARE @bAutoReconnect int
    SELECT @bAutoReconnect = 1
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'example.com', @port, @bTls, @bAutoReconnect

    -- There are 3 ways to send the above GET request.

    -- 1) Send the request with path and query params pre-built, where the query param values are URL encoded.
    DECLARE @responseJson nvarchar(4000)
    EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseJson OUT, 'GET', '/search?query=hello%20world&category=books&sortBy=price&filterBy=inStock'
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    -- 2) Pass only the Path part of the URL, and specify the query params separately by calling AddQueryParams beforehand.
    --    Again, the query params must be already URL encoded when passed to AddQueryParams
    EXEC sp_OAMethod @rest, 'ClearAllQueryParams', @success OUT
    EXEC sp_OAMethod @rest, 'AddQueryParams', @success OUT, 'query=hello%20world&category=books&sortBy=price&filterBy=inStock'
    EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseJson OUT, 'GET', '/search'
    -- ...
    -- ...

    -- 3) Pass each query parameter separately by calling AddQueryParam.  In this case, the query param value should be passed without URL encoding.
    --    (ClearAllQueryParams ensures any params set for previous request are cleared.)
    EXEC sp_OAMethod @rest, 'ClearAllQueryParams', @success OUT
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'query', 'hello world'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'category', 'books'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'sortBy', 'price'
    EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'filterBy', 'inStock'
    EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseJson OUT, 'GET', '/search'
    -- ...
    -- ...

    EXEC @hr = sp_OADestroy @rest


END
GO