The Workflow Rule Action - Run Stored Procedure

Hints, Tips and Tricks

Technical Hints Tips and Tricks that cover customization and development using Sage CRM. API usage and coding are covered.

The Workflow Rule Action - Run Stored Procedure

  • Comments 4
  • Likes

The system administration guide discusses the workflow action 'run stored procedure'.  This article expands on the information contained in the documentation. 

A workflow rule action of type 'run stored procedure' can be created very simply just like any other rule.

The purpose of the action is to return a default value into a field.  The stored procedure allows complex calculations to be carried out and perhaps also other tasks such as update of other records within the database.

When creating the workflow action you need to consider three items of information

  • Action Type:  The type of action to be performed.
  • Column:  The column the stored procedure is to be executed on.  This will receive the value return by the stored procedure
  • Value: The name of the stored procedure. This stored procedure must already exist within the database.  For example in MS Sql Server 2005 it will be defined in the database Programmability, Stored Procedures area controlled by the Management Studio.

The storedprocedure being called must use a very particular structure.  It has to contain certain variables and provide a particular return variable.

CREATE PROCEDURE [xxx]
@table_name nvarchar (50),
@identity_name varchar (50),
@id_no int,
@logon_no int,
@return_value varchar(20) OUTPUT
AS
SELECT @return_value = '999'
RETURN (1)

Note:

Within the Stored Procedure other variables maybe used to aid in the processing.  But when the stored procedure is invoked from the interface the variables values below are passed to the stored procedure.

  • @table_name contains the table name of the current entity being workflowed.
  • @identity_name will contain the identity field of the table (E.g. Case_CaseId, Oppo_OpportunityId etc.)
  • @id_no contains the id value of the current record.
  • @logon_no contains the value of the userid of the current user.
  • @return_value is the return value that is entered into the field with which the workflow action is associated.

The return_value can be changed to a different datatype such an 'int' or 'nchar' by the use of the Cast method.

EG:

CREATE PROCEDURE wf_addFreeNote
@table_name nvarchar (50),
@identity_name varchar (50),
@id_no,
@logon_no int,
@return_value varchar(20) OUTPUT
AS
SELECT @return_value = Cast ( @logon_no as varchar)
RETURN (1)

The stored procedure should always return (1)

Comments
  • I have a client with an issue raised using this workflow action - there are some fields displayed for entry and a save button. The user can click the save button multiple times and the stored procedure fires for each click, resulting in multiple records. They are on 6.1 SP1. I wonder if this was fixed in a later release?

  • I had a look at Sage CRM v7.1 (and I think it is the same for Sage CRM v7.0) and in the screen created by the workflow rule the save button has the following code

    javascript:try{checkSubmit(document.EntryForm);}catch(e){document.EntryForm.submit();}

    The checkSubmit function stops the save button being clicked more than once.

  • Thanks Jeff, I'll use that

  • Hi all,

    I have a problem with run and stored procedure field of table, No run of workflow rule.

    I created a new field of stored procedure type in company table. My objective is to get get the number of opportunities open and display a read-only field. In the parameter field value I specified the name of the stored procedure in the database without passing parameters, with the following contents:

    "CREATE PROCEDURE [xxx]

    @table_name nvarchar (50),

    @identity_name varchar (50),

    @id_no int,

    @logon_no int,

    @return_value varchar(20) OUTPUT

    AS

    SELECT @return_value = '999'

    RETURN (1)"

    It want to return a valou of my sql select. Is possible to filtre the sql select?

    How I can return a value of sql select in a read only field of screen?? It is not necessary to store the value in the table, just consult.

    Thanks for your time.