SQL Execute Query action

This is an example of JSON request and response message content for a mocked SQL Execute Query action.

Request

This request executes a stored procedure called dbo.ThisIsMyStoredProcedure, taking two parameters:

{
    "query": "EXEC dbo.ThisIsMyStoredProcedure @CustomerId, @CountryCode",
    "queryParameters": {
        "CustomerId": "12345",
        "CountryCode": "UK"
    }
}

This request executes a SQL statement, passing a Job Title as a parameter:

{
    "query": "SELECT Id, FirstName, LastName, DateOfBirth, JobTitle FROM dbo.Customer WHERE JobTitle = @JobTitle",
    "queryParameters": {
        "JobTitle": "Footballer"
    }
}

Successful Response

This response indicates that the query was successful and no records are returned:

[]

The test case should respond with a HTTP 200 (OK) status code.

This response indicates that the query was successful and three records are returned:

[
    [
        {
            "Id": "12345",
            "FirstName": "David",
            "LastName": "Smith",
            "DateOfBirth": "1997-03-23",
            "JobTitle": "Footballer"
        },
        {
            "Id": "12602",
            "FirstName": "Simon",
            "LastName": "Jones",
            "DateOfBirth": "1993-12-01",
            "JobTitle": "Footballer"
        },
        {
            "Id": "16934",
            "FirstName": "Johnny",
            "LastName": "Baker",
            "DateOfBirth": "1992-01-31",
            "JobTitle": "Footballer"
        }
    ]
]

The test case should respond with a HTTP 200 (OK) status code.

Failed Response

This response indicates that the workflow could not authenticate with the SQL database:

{
    "code": "ServiceProviderActionFailed",
    "message": "The service provider action failed with error code 'ServiceOperationFailed' and error message 'Login failed for user 'myusername'.'."
}

The test case should respond with a HTTP 401 (Unauthorized) status code.