Calling Stored Procedures from a Merge Alias

OmniRush supports the use of stored procedures to grab data to be merged.  This opens up more flexibility, as within a stored procedure you can control a large number of parameters.  For example:

• Perform complex joins, including conditional joins

• Merge default data in cases where the query finds no live data

• Format data, especially numeric data

• Call extended stored procedures

• Control how many rows your query returns.

• Conditionally execute other queries based on the results of the initial query.

• etc.

Quick List:

Example 1

Example 2

Example 3

Example 4 (advanced example #1)

Stored Procedures also have the benefit of being pre-compiled by the database server.  This means that performance can be better than passing a SELECT statement.

The steps to use stored procedures from OmniRush are as follows:

1. Develop the stored procedure in ISQL.

2. Be sure to have the stored procedure accept a parameter for the accountno (on GoldMine, you can pass the accountno, the recid of the calendar record, or both.  On SalesLogix, you can pass the contact recid, account recid, activity recid, or any blend.)

3. Test your stored procedure by executing it from ISQL and passing an accountno or recid parameter to validate the output.

4. Add a merge code to OmniRush that calls the stored procedure.

5. Create a merge form that uses the merge code.

6. Test it!

In the examples, we will use a simple stored procedure that pulls the very first, earliest, history record from GoldMine's conthist.  An alternate version is presented that pulls the last, most recent record from conthist.  The main benefit of the stored procedure in this instance, as opposed to using a straight SELECT in OmniRush is the availability of the SET ROWCOUNT which controls how many records the SQL server will return.

Here is the example:

create procedure sp_findhist

@gmacct varchar(20)

AS

set rowcount 1

select * from

conthist

where

accountno = @gmacct

order by

createon

Here is an alternate version:

create procedure sp_findhist2

@gmacct varchar(20)

AS

set rowcount 1

select * from

conthist

where

accountno = @gmacct

order by

createon DESC

We test the stored proc in ISQL by passing an accountno or recid as a parameter in quotes:

sp_findhist "941130 2934300   -Ri"

Now create a new merge code in the OmniRush merge configurator:

Then, create a merge form.  Here is an example.

Figure 1 (above):  The table in the merge template to receive the stored proc results.

The resulting output is then this:

Figure 2 (above):  The resulting merged document.