Executing a Stored Procedure on Microsoft SQL Server with FME

Introduction

Although FME can easily send SQL queries to MS SQL Server, you may already have a query or more complex function defined in SQL Server as a Stored Procedure. FME can execute stored procedures using the SQLExecutor or SQLCreator transformers and supply attributes or parameter values as arguments to the procedure.

Create the Stored Procedure

In SQL Server create your stored procedure. This can be done using SQL Server Management Studio within your database under the Programmability node or with other development tools. In this example the procedure is called dbo.MyProc. The arguments or parameters of the procedure will be defined within the procedure like this.

 ALTER PROCEDURE [dbo].[MyProc]  
     -- Add the parameters for the stored procedure here 
	@ADDRESSID int, @COUNTY int 

and then specified within the query like this:

   SELECT * from dbo.ADDRESS_POINTS where ADDRESSID = @ADDRESSID and COUNTY = @COUNTY 

Take note of the order of the arguments so you can supply them with FME in the correct order. FME will supply an ADDRESSID and a COUNTY.

Note: We have only shown parts of the stored procedure above - the complete procedure is attached (MyProc.sql).

Using the SQLExecutor and Supplying the Parameters as Attributes

Look at the first bookmark in the attached workspace.

1. We start with a Creator so that we have something to initiate the SQLExecutor transformer.

2. The AttributeCreator is used to create attributes for ADDRESSID and COUNTY. Here we have hard-codes these values but you can supply them be sending any FME feature with these attributes.

3. The SQLExecutor is the transformer we use to connect to the database and execute the query. When you first add this transformer you will need to specify some parameters. The format is Microsoft SQL Server Non-spatial and the dataset is your database name. Fill in the user name and password or use Windows Authentication.

4. Next we need to enter the SQL Statement by clicking on the ellipsis beside the text box. Normally we could enter SQL syntax using the tables or columns from the left side. In this case we will enter the execute statement like this:

  exec dbo.MyProc '@Value(ADDRESSID)','@Value(COUNTY)' 

The parameters of the stored procedure are provided in order and separated by a comma. The @Value() syntax tells FME to send the value of that attribute as the parameter to query.
5. Records returned by the query will come out of the Result port and are sent to the Data Inspector (Visualizer) in the example. Don't forget to click on the "Select No-Geometry" button to see these no geometry features.

Note: The attributes of the output features will not be exposed - meaning they will be on the features but not accessible in workbench. You can expose them using the AttributeExposer transformer to explicitly name the attributes you want to see or expose them in the SQLExecutor transformer.

Using the SQLCreator and Published Parameters

The second bookmark shows another example very similar to the first. Notice in the navigator pane we have two published parameters already set-up for ADDRESSID and COUNTY.

In this case we start with the SQLCreator and connect to the database in the same way as in the first example. However the SQL statement looks slightly different since we want to get the values for the stored procedure parameters from the published parameters:

   exec dbo.MyProc '$(ADDRESSID)','$(COUNTY)' 

Again, the parameters for the stored procedure are provided in order and separated by a comma. The $() syntax tells FME to send the value of the published parameter to the procedure.