The latest version of Flynet Designer for Web Services (DWS2010) , includes an enhancement for Stored Procedures. This example uses a SQL Server Stored Procedure.

 

The first Step, once you have made a connection to the database is to type EXEC followed by the Stored Procedure name. Click on the 'Test/Fetch Parameters' button and the expected input parameters are returned.

 

 

Once the expected parameters are returned you can add them after the stored procedure name by typing them in, at this stage you must know the data type of the parameter and this must be manually entered as below, in the 'type' property enter the data type of the parameter. In the 'value' property of the grid below enter a valid value for the parameter type , in this case the @OrderId is an 'int' so a valid numeric value is entered before hitting the 'Test/Fetch Parameters' button again.

 

 

 

pressing the the 'Test/Fetch Parameters' button returns the data , press ok at this stage.

 

 

The Output parameters are then automatically returned and entered into the grid as below.

 

 

 

Earlier versions of the Designer for Web Services  require a workaround (see below).In this example the SQL server database Northwind will be used to demonstrate using Stored Procedures First define a Data Source Connection to the Northwind Database. Test the connection and then create a new Query  using the connection you have just created.

 

In this example the Stored Procedure CustOrderHist will be used from the Northwind database.

 

The Stored Procedure CustOrderHist is shown below as it appears in SQL server:

 

Please take note of the following, after the name of the procedure in this example there is a parameter where the CustomerId is passed to the Stored Procedure , note the Data type of any parameter to be passed.There are also two columns that are returned,these are after the SELECT statement , these are ProductName and Quantity , you will need to be aware of what datatypes these columns are when defining the Query using DWS.

 

The syntax used in DWS is shown in the example below:

 

Enter the text EXEC at the start of the Query SQL statement, followed by the name of the Stored procedure. You then need to enter any parameters that need to be passed , by following the DWS SQL syntax rules.You can enter any number of parameters  by separating them with a comma   e.g ~param0~, ~param1~ etc.

 

Note: Please remember to enclose string parameters with quotes as shown in the example above , integers or  doubles etc. do not need quotes.

 

Directly underneath the EXEC statement you now need to enter values for the number of columns you need to return to the Web Service.Here there are two columns required , one for ProductName and one for Quantity.You do not need to enter the actual names but just make sure the names are unique.

Note: You must prefix the entries with --  as shown above.

 

Once you have entered your SQL statement , the grid display should contain values for your entries as shown below.

 

Note the direction of the relevant entries , any parameters to be passed should have the direction set to 'In' and any columns to be returned should be set to 'Out' .You also need to take care to define the datatypes correcty here as well, in the above example output2 will contain the value for Quantity so this had to be set to 'int'.

 

You should now be able to hit the Test button to test that your Query works.

 

You may receive some informational messages that the datatypes do not match , click OK on these , in the example shown you will get one such message. Data types such as 'nchar' can be entered as a data type of string, 'Int32' can be entered as 'int'.

 

Once your test is successful you can generate your Web service.