Execute MSSql Server Stored Procedure from WSO2 Enterprise Integrator

Even though we can write SQL queries in database agnostic manner when we expose database tables as services with WSO2 Enterprise Integrator, there might be some challenges when dealing with stored procedures. Recently I found one such a situation.

I was working on a project which was using Microsoft SQL Server as the database. For one use case, there was a requirement to execute an update and return the updated records to the frontend. As you know, we can’t do this with a single SQL query. Both these operations should happen within a single transaction. So we choose to use a stored procedure to handle this.

Let’s look at the stored procedure created to handle this situation.

CREATE OR ALTER PROCEDURE dbo.SELECT_RECORDS_BY_STATUS @P0 nvarchar(1)
AS
BEGIN
    -- Create a temp table to hold the record ids
    DECLARE @TEMP_IDS TABLE (
        T_ID varchar (50) NOT NULL
    )
    -- Start the transaction
    BEGIN TRANSACTION

    -- Turn off the results for the next query
    SET NOCOUNT ON
    -- Select pending record ids into the temporary table
    INSERT INTO @TEMP_IDS SELECT T_ID FROM RECORDS WHERE T_STATUS=@P0
	
    -- Turn off the results for the next query 
    SET NOCOUNT ON
    -- Update the records as Retrieved
    UPDATE RECORDS SET T_STATUS = ‘R’ WHERE T_ID IN (SELECT T_ID FROM @TEMP_IDS)

    -- Turn on the results since we need this in the response
    SET NOCOUNT OFF
    -- Select the updated records
    SELECT * FROM RECORDS WHERE T_ID IN (SELECT T_ID FROM @TEMP_IDS)

    -- Commit the transaction and leave
    COMMIT TRANSACTION  
    RETURN
END

So it is a simple stored procedure, what is the big deal?

When we have multiple Queries in a stored procedure, it returns multiple result sets to the client. At least this is the behaviour for MSSql Server. Problem is that WSO2 Enterprise Integrator Data services cannot handle multiple result sets. So we have to specifically mention which result set we need as the output. We have to use the SET NOCOUNT ON/OFF option to handle this situation.

Invoking this stored procedure from the Data Service is simple

...
<query id="UpdateAndSelectRecords_Query" useConfig="MS_DB">
    <sql>{CALL [dbo].[SELECT_RECORDS_BY_STATUS](?)}</sql>
    <result element="records" rowName="record">
        <element column="T_ID" name="id" xsdType="xs:string"/>
        <element column="NAME" name="name" xsdType="xs:string"/>
    </result>
    <param name="STATUS" ordinal="1" sqlType="STRING"/>
</query>
...