Run Stored Procedure

Use Run Stored Procedure to insert a default value in a field as part of a workflow process. The stored procedure performs complex calculations and can update other records in the database.

Do not use this workflow action unless you are an SQL expert and have tested your workflow extensively in a non-live environment. Poorly constructed or erroneous SQL could have a seriously detrimental impact on your system.

Field

Description

Type

The action to be performed.

Column

The value that the stored procedure returns is inserted in this column.

Value

The name of the stored procedure. The stored procedure must already be created on the database.

You must use the following parameters in the stored procedure. The stored procedure should always return (1).

Copy 
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)
  • @table_name: The table name of the workflowed entity.
  • @identity_name: The table ID. For example, Case_CaseId.
  • @id_no: The current record ID.
  • @logon_no: The current user ID.
  • @return_value: The value that's inserted in the field you specified in Column.

You can use the Cast method to change return_value to a different datatype (int or nchar). For example:

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