How to run the sql query on the data service by sending a parameter?

I have a sql query. It takes a dynamic parameter. Using Dataservice in WSO2 Integration Studio how can I send parameters to this query from outside?

There are two ways to do this. By sending a HTTP POST request and reading the parameter from the payload or sending a HTTP GET request and reading from the query parameters.

Option 01 : Sending a POST request

In your resource add the parameter you want to accept.

<resource method="POST" path="/getProducts">
      <call-query href="someselect">
         <with-param name="categoryId" query-param="categoryId"/>
      </call-query>
</resource>

Then in your Query, you can pass the parameter.

<query id="someselect" useConfig="default">
      <sql>Select * from Products where categoryId = :categoryId</sql>
      <param name="categoryId" paramType="SCALAR" sqlType="STRING"/>
      <result outputType="json">RESULT MAPPING</result>
</query>

The request payload will look like something below.

{
  "_select": {
      "categoryId" : 1
  }
}

Option 02: Sending a GET request

In your resource add the parameter you want to accept as a query param.

<resource method="GET" path="/getProducts">
      <call-query href="someselect">
         <with-param name="categoryId" query-param="categoryId"/>
      </call-query>
</resource>

Then in your Query, you can pass the parameter.

<query id="someselect" useConfig="default">
      <sql>Select * from Products where categoryId = :categoryId</sql>
      <param name="categoryId" paramType="SCALAR" sqlType="STRING"/>
      <result outputType="json">RESULT MAPPING</result>
</query>

The request will look like something below.

curl -v https://DATASERVICE_URL/Service/resource?categoryId=12345