Sunday, May 28, 2017

WSO2 DSS - Using Microsoft SQL Server Stored Procedures

The following example shows how to use Microsoft SQL Server stored procedure which has input parameter, output parameter and a return value with WSO2 DSS.

SQL for create table and procedure

CREATE TABLE [dbo].[tblTelphoneBook](

  [name] [varchar](50) NULL,

  [telephone] [varchar](50) NULL


insert into tblTelphoneBook values('Jane','4433');

CREATE PROCEDURE [dbo].[PhoneBookSP3]  

  @name varchar(50),  

  @telephone varchar(50OUTPUT  



SELECT @telephone = telephone from tblTelphoneBook where name = @name;

return 13;

Data Service

<data name="TestMSSQLService" transports="http https local">

  <config enableOData="false" id="MSSQLDB">

     <property name="driverClassName"></property>

     <property name="url">jdbc:sqlserver://localhost:1433;databaseName=Master</property>

     <property name="username">sa</property>

     <property name="password">test</property>


  <query id="testQuery3" useConfig="MSSQLDB">

     <sql>{? = call [dbo].[PhoneBookSP3](?,?)}</sql>

     <result element="Entries" rowName="record">

        <element column="telephone" name="telephone" xsdType="string"/>

        <element column="status" name="status" xsdType="integer"/>


     <param name="status" sqlType="INTEGER" type="OUT"/>

     <param name="name" sqlType="STRING"/>

     <param name="telephone" sqlType="STRING" type="OUT"/>


  <operation name="opTestQuery3">

     <call-query href="testQuery3">

        <with-param name="name" query-param="name"/>



Request and Response


  <p:opTestQuery3 xmlns:p="">

     <!--Exactly 1 occurrence-->




<Entries xmlns="">






No comments:

Post a Comment