Custom Merge via Stored Procedure Example 1

This example does more than return data. It actually builds a phrase based on the data present.

The scenario:

You sell to resellers, and if a reseller does at least $500 in sales volume with you six months, that reseller is 'Active.' In an email or merge letter to your resellers, you want to thank them for their most recent purchase. If that purchase qualifies them as an Active reseller, you want to mention this in the letter. If there are no sales, you want to say as much.

The database:

In this case, all sales are logged in the CRM system as history records. This example will compile and run against a GoldMine SQL system, and can be easily adapted to MSCRM, SalesLogix, or any other system.

The merge template:

Any RTF, HTM, or OXP can be used (the OXP for a simple text email). For the setup we are creating, the merge code &SPS_TexttoMerge can be inserted anywhere in the text and it will merge.

The Custom Merge Alias in the OmniRush Administrator:

Nothing to it, it looks like this:

The Stored Procedure (This will look better pasted into SQL Server Query Analyzer -- some indenting and line breaks may need to be manually adjusted after pasting):

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

IF EXISTS (SELECT name FROM sysobjects

         WHERE name = 'sp_ZFGetLastSale' AND type = 'P')

   DROP PROCEDURE sp_ZFGetLastSale

GO

create procedure dbo.sp_ZFGetLastSale

@sAccountno varchar(20)

/* The job of this sproc is to look at the most recent

   sale in history. If there is no sale, it says to effect

   "You have not made any sale." If there is a sale it says

   "You made a sale of $XXX on DDMMYYY" and if the sale was

   for more than $500, it says

   "You made a sale of $XXX on DDMMYYY, this maintains you as an

   active reseller, thank you!"

*/

as begin

set nocount on

--*******************************************************

declare

@sRecid varchar(25),

@sPurchDate varchar(25),

@sPurchAmount varchar(25),

@iGetDataFlag int,

@sReturnString varchar(300),

@iRecentPurch int

/*

Test values for development:

Set @sAccountno = '98121157088(_NC)+Chu' -- = '&CONTACTID'

Set @sAccountno = 'A2082043917$1SU+\Per'

Set @sAccountno = '96120233266946958Ste'

Set @sAccountno = 'A3112054096*_^(K\Amy'

Set @sAccountno = '98120446579&>S9_WSpi'

*/

--Decide if there are any sales within the last 24 months.

--This query is tuned to look only for history records created

--by CashRush v4. You can easily retune it to look for any record.

Set @iGetDataFlag = (select count(*) from conthist

       where accountno = @sAccountno

       and

       resultcode = 'CMP'     

  and

       (ref like '%PRCH%' or ref like '%COD%' or ref like '%NET%'  )

  and

       24 > datediff(month,createon,getdate()))

--Now look for sales within the last 6 months--this is what

--is required to be an active reseller.

Set @iRecentPurch = (select count(*) from conthist

       where accountno = @sAccountno

       and

  6 > datediff(month,createon,getdate())

       and

       resultcode = 'CMP'    

  and

       (ref like '%PRCH%' or ref like '%COD%' or ref like '%NET%'  ))

if @iGetDataFlag > 0  --if there are any sales in the last 2 years, let's get the details!

    begin

        --We use a simple cursor to actually run the query and load just

        --one record--since we "order by" date here, only the most recent

        --record will get loaded into the cursor.

    declare cr_GetSale cursor

    forward_only

    for

       select recid, accountno, ondate, duration from conthist

       where accountno = @sAccountno

       and

       resultcode = 'CMP'

       and

       (ref like '%PRCH%' or ref like '%COD%' or ref like '%NET%'  )

       order by ondate DESC

    open cr_GetSale

    --Now we pull the data from the cursor into tsql variables that

    --we can use downstream.

    fetch from cr_GetSale

    into @sRecid, @sAccountno, @sPurchdate, @sPurchamount

    --All done with the cursor.

    close cr_GetSale

    deallocate cr_GetSale

    --Now we have all the data we want in variables, so we build the text we want

    --to merge.

    set @sReturnString = 'Your most recent purchase was for $' + @sPurchAmount +

      ' on ' + (select datename(month, @sPurchDate)) + ' ' +

      (select datename(d, @sPurchDate)) + ', ' + (select datename(yyyy, @sPurchDate)) + '.'

    --Now we look for sales in the last 6 months that would qualify the reseller

    --as an Active reseller.

    if @iRecentPurch > 0 and (Cast(@sPurchAmount as numeric) > 500)

    begin

       set @sReturnString = @sReturnString + ' Your recent purchases have maintained you as an

       Active reseller. Thank you!'

    end

end

if @iGetDataFlag < 1 --No sales in the last 2 years... so that is easy.

    begin    

      set @sReturnString = ' There are no recent purchases on file. '

    end

--Now we take the text we built and pass it back as the result of the sproc.

select @sReturnString as TexttoMerge

end