There are times when program logic is so complex that the correct resulting dataset can only be achieved via a stored procedure that returns a data set. The challenge in this is that by default, BIPLUS does not allow a developer to run any queries that do not start with the keywords SELECT or WITH. With defines reusable inline views that can be used multiple times by the same main query. This technique is also referred to as **Subquery Factoring** or **Common Table Expressions**. 

More information on this is available from the following URL’s (valid links at the time of guide creation, February 2013).\\
http://msdn.microsoft.com/en-us/library/ms190766.aspx\\
http://msdn.microsoft.com/en-us/library/ms186243.aspx \\
http://oraclequirks.blogspot.com/2007/04/better-sql-with-subquery-factoring.html\\
http://www.oracle-base.com/articles/misc/with-clause.php\\
http://forum.springframework.org/showthread.php?t=19848

As an example, we are going to document how to execute a Microsoft Sql Server stored procedure called CustOrderHist, which is supplied with the demo Northwind Database. This stored procedure accepts a single parameter.

To start off with, let us look at what the SP (Stored Procedure) does using Microsoft Query Analyzer.

{{:biplus:documentation:store_procedure_1.png?nolink&550|}}

The graphic above shows us how to create the Stored Procedures in the Sql Server.

{{:biplus:documentation:store_procedure_2.png?nolink&500|}}

If we try to execute this stored procedure in BIPLUS, we get an error. BIPLUS does not like the query starting with the verb **EXECUTE**, it must be **SELECT** or **WITH**. Just to verify, we can execute the stored procedure in Sql Server via query analyzer.

Execution of the Stored procedure is as simple as typing in:\\
**Execute dbo.CustOrderHist 'ALFKI'**. Here, we are passing customer Id **ALFKI** as a parameter.

The resultant data set is shown in the result pane. See the image below.

{{:biplus:documentation:store_procedure_3.png?nolink&400|}}

So what we need to do now is to tell BIPLUS to allow a query to begin with **Execute**. We need to edit the BIPLUS defaults file, which is found under **$TOMCAT_HOME/webapps/APPLICATION_NAME/configuration**. The file is called biplus_defaults.xml. Edit this with an XML Capable editor (try anything other than Notepad).

{{:biplus:documentation:store_procedure_4.png?nolink&500|}}

If the entry is not in the file, then create an entry / tag for <sql-start-allowed>.  As per the graphic below, we have now included some additional verbs that queries may begin with. This is applicable to all datasources.


{{:biplus:documentation:store_procedure_5.png?nolink&550|}}

Restart the tomcat server. Open a fresh session and run the report. The results appear as below.

{{:biplus:documentation:store_procedure_6.png?nolink&550|}}

**Note:** the words UPDATE, DELETE, INSERT and FOR UPDATE are still taboo words and will not be allowed in a query. However, if these DML statements are found inside a stored procedure, then there is nothing that can be done to prevent them from executing, as they are part and parcel of the Stored Procedures.