🔍

Type Custom Stored Procedure

Object Structure

The Different types of XPOR Data Sources cover most of the bases you will ever need to use to query and return data from your XPOR - from the simple Objects by Type, through to the Query Guided options.  BUT in some cases this is still not enough.  So XPOR provides the ability for a Custom Stored Procedure to be added to the list of XPOR custom stored procedures.

Custom Stored Procedures are not the thing we expect the majority of XPOR users to be able to generate - hence the various standard Data Source types.  But in certain circumstances you may need to consider having a "Stored Procedure" produced.

For example. a standard Data Source will return all records that match the path - FULLY.  In one of our previous examples, we had;

Simple XPOR Model

We were looking for all the CD software in the system.  If the Document didn't have any software - nothing is returned.  This makes perfect sense BUT in some cases you might want to list out ALL Documents and their software EVEN IF DOCUMENTS DON'T HAVE ANY SOFTWARE.

This cannot be achieved with XPOR query data sources.  Documents with no software will not be returned.  It CAN be achieved with a slightly different approach - the Content Repeater (separate Help Resource) or with a Custom Sourced Procedure data source.

A second example where a Custom Stored Procedure may be used is where a Document has TWO CD's associated to it.  The standard XPOR Data Source will return this BUT will list the Document twice.  You may prefer to have the document listed once and the different CD's listed next to it.  Again this can be achieved with Content Repeaters OR with a Custom Stored Procedure on a Grid form.

The attached video demonstrates how to create a Custom Stored Procedure Data Source.

What is a Stored Procedure?

A stored procedure is a data base analyst thing!  It is a group of sql statements (the way that Databases accept questions) that has been created and stored in the database, ready to be used whenever they are called by some code. Stored procedures will accept input parameters so that a single procedure can be used with different input criteria.

How to Add a Stored Procedure into XPOR (Developer)

Firstly open SQL Server, make sure you are connected to the correct server, the correct database and the location where databases are stored. From here there are 2 ways to add a new stored procedure: Starting from scratch and using an existing Stored Procedure as a template. When the Stored Procedure has been created it wil appear in the list of Custom Stored Procedures above.

Making a stored procedure from scratch

Right click "Stored Procedures", hover "New" and click "Stored Procedure" as shown in the image below. This opens up an editor with rough guidlines to get you started.

Making a stored procedure from an existing Stored Procedure

Right click a Stored Procedures that is similar to what you want to achieve and click "Modify" as shown in the image below. This opens up an editor of that Stored procedure. From here you can change the "ALTER PROCEDURE" to be "CREATE PROCEDURE", rename the procedure and press F5 or Excecute. Then change back to "ALTER PROCEDURE" and make the necessary changes that you need for your new Stored procedure.