A Stored Procedure as a Data Source

This Cookbook article refers to the AdventureWorks database which needs to be installed at a location available to your development environment. If you have not already done so, you can download the database from Download the AdventureWorks Database.

It is assumed that you already know how to configure a datasource for a SQL Server database.  If not, it is strongly recommended that you do so.  You can follow the process by looking at the Cookbook articles Use a SQL Server Data Source to include Customer information on a template and/or Configure A Data Source for an Elite Database.

The process of defining a stored procedure as a data source is almost the same as that for defining a normal table or view as a data source, with the following differences:

• Where the configuration wizard presents a choice of tables and views, it will now also include the stored procedures in that list.
• Once a stored procedure is chosen, then configuration wizard will end, and give you the opportunity to modify the Definition String, using the Use Data Source’s  Editor button for that.

Some concepts to bear in mind are (these concepts are all illustrated in the sample template Using a Stored Procedure as a Data Source.xdtpx which is delivered into the My Documents\XpressDox\Samples folder by the XpressDox desktop installer):

1. All data sources require that an “ID” be defined, by which unique items can be retrieved from a data source.  For a table or view, the ID is typically the primary key, or some other unique identifier such as an account number, or person number.  In the case of a stored procedure, the ID consists at least of the arguments to the stored procedure, but may have more components.  For example, in the case of the AdventureWorks database, the stored procedure dbo.uspGetManagerEmployees will get all the employees for a given manager.  In this case the ManagerID is the single argument which needs to be sent to the stored procedure, but since this stored procedure returns a collection of Employee objects, an extra item, viz. the EmployeeID, is necessary as part of the ID of this data source.  This is to make sure that the system knows how to distinguish between the multiple Employee objects returned by this stored procedure, which will have the same ManagerID.
2. The usual data source retrieval commands can be used to get the data returned by a stored procedure.  The above-mentioned sample template uses the LinkToDataSource Command and this is arguably the command which suits stored procedures best.
3. As template author, you should have knowledge of what kind of information a stored procedure returns.  If it returns a scalar value, or a number of scalar values (such as the Sales.uspGetEmployeeSalesYTD stored procedure used in the above sample), then the LinkToDataSource (or other data source command) should specify the “id=<EmployeeID>” type syntax.  If, however, the stored procedure returns a collection, then the “range=…” syntax should be used.  For example, if dbo.uspGetManagerEmployees is called using something like “id=<ManagerID>” in the data source command, then only the first Employee for that ManagerID will be returned.  Calling the dbo.uspGetManagerEmployees stored procedure should be initiated using the “range=<ManagerID>” syntax in the data source command.