Home → Using LincDoc 3.1+ → Actions → Action Details: Execute Stored Procedure

11.21. Action Details: Execute Stored Procedure

Overview

This action will call a database stored procedure in a database external to (outside of ) LincDoc, passing to it an XML string which contains all of the form data.

The basic steps for this process are as follows:

Set up the database connection using the databases option (accessed via the system button).

Note: Your license key must already have the database integration module enabled.
Create the stored procedure following the example code shown in Example: Creating the Stored Procedure below.
On the Admin dialog box's Actions tab, add an execute stored procedure action to the desired eForm or Document Package. For more information, see Actions.

Configuration

The execute stored procedure dialog box, which contains all of the action's options, is shown below.

When configuring the execute stored procedure action in your form, perform the following steps:

Choose the desired database using the Connection drop-down list.
Type the appropriate stored procedure name in the corresponding text box.
(Optional) If the stored procedure returns a value, it can be stored in a LincDoc field using the Result options.

Click the check box to the left of the Store result in the text.
From the field drop-down list, select the desired field.

Once all parameters are entered, click the test button to confirm LincDoc can access the stored procedure.

Example: Creating the Stored Procedure

The following example shows a sample stored procedure which assumes that data is coming from an expense report. Notice that the stored procedure writes data to two different tables: the multi-value (or multi-row) data goes into the dbo.expenseItems table, while the non-multi-valued data goes into the dbo.expenseReports table.

While not strictly necessary, this example also shows the entire XML input being written to the expenseXML table, which can be handy as a debugging tool to see the full data being passed to the stored procedure.

The docId variable contains the unique identifier assigned by LincDoc to this data record. It is appropriate to use this variable as a primary key in other tables in which you may be storing data.

Important: The details required to create the stored procedure are beyond the scope of this topic and help system. Please refer to your database vendor's documentation or local database administrator for details.

-- must have single input value of varchar(max) for the incoming XML
-- single varchar output is optional 
-- NOTE THIS EXAMPLE DOES INSERTS ONLY. IT DOES NOT UPDATE THE DATABASE TABLES. 
CREATE PROCEDURE [dbo].[SaveExpenses]
        @inVal [varchar](max),
        @outVal [varchar] (20) OUTPUT
WITH EXECUTE AS CALLER
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from 
        -- interfering with SELECT statements. 
        SET NOCOUNT ON;
        SET XACT_ABORT ON;
        
        BEGIN TRY
            BEGIN TRANSACTION;
                
                -- convert incoming parameter to XML 
                declare @val xml
                set @val = CAST(@inVal as xml)
                
                -- retrieve the document id (unique identifier) 
                declare @docId varchar(50)
                set @docId = @val.value('/document[1]/@doc-id', 'varchar(50)')
                -- retrieve the edit-url
                declare @editUrl varchar(255)
                set @editUrl = @val.value('/document[1]/@edit-url', 'varchar(255)')
                -- store the document XML in table 
                insert into expenseXML(cDocId, xData) values(@docId, @inVal)
 
            -- maximum expense id counter 
            declare @expId int
            SELECT @expId = MAX(nExpenseId) from dbo.expenseReports  

            if @expId is null
               set @expId = 1
            else
               set @expId = @expId + 1
 
            set @outVal = cast(@expId as varchar(20))

            -- main expense report table insert 
            insert into dbo.expenseReports (nExpenseId, cPayee, cPayeeAddr, cPayeeCity, cPayeeSt,
            cPayeeZip, cBusinessPurpose, dtStart, dtEnd, dtPayeeSigned, cApproval, cManager, dtManagerSigned )
  
            select @expId, * from (
                    select 
                T.c.value('(./field[@name="Payee_Name"][1])', 'varchar(50)') as payee,
                T.c.value('(./field[@name="Payee_Address"][1])', 'varchar(50)') as addr,        
                T.c.value('(./field[@name="Payee_City"][1])', 'varchar(30)') as city,   
                T.c.value('(./field[@name="Payee_State"][1])', 'varchar(6)') as st,
                T.c.value('(./field[@name="Payee_Zip"][1])', 'varchar(9)') as zip,
                T.c.value('(./field[@name="Business_Purpose_for_Expenses"][1])', 'varchar(200)') as businessPur,
                T.c.value('(./field[@name="Expense_Period_Start_Date"][1])', 'smalldatetime') as startDate,
                T.c.value('(./field[@name="Expense_Period_End_Date"][1])', 'smalldatetime') as endDate,
                T.c.value('(./field[@name="Payee_Date"][1])', 'smalldatetime') as payeeSignDate,
                T.c.value('(./field[@name="approvalYN"][1])', 'varchar(50)') as approval,
                T.c.value('(./field[@name="ManagerName"][1])', 'varchar(50)') as manager,
                T.c.value('(./field[@name="ManagerDate"][1])', 'smalldatetime') as managerSignDate
                    from @val.nodes('/document/data') T(c)) as T 
        
                 
                 -- individual expenses (multi-row) 
                 -- group name is found in XML                  
                 INSERT into dbo.expenseItems (nExpenseId, dtExpense, cType, cDesc, mAmount, tImage)
                 
                 SELECT @expId, expense_date, expense_type, expense_desc, expense_amt, expense_image FROM (
                    select
                T.c.value('(../@name)[1]', 'varchar(255)') as group_name,
                T.c.value('(@index) [1]', 'varchar(255)') as row_index,              
                    T.c.value('(./field[@name="expenseDate"][1])', 'smalldatetime') as expense_date,
                T.c.value('(./field[@name="expenseType"][1])', 'varchar(50)') as expense_type,
                T.c.value('(./field[@name="expenseDesc"][1])', 'varchar(120)') as expense_desc,
                T.c.value('(./field[@name="expenseAmount"][1])', 'money') as expense_amt,
                T.c.value('(./field[@name="expense_image"][1])', 'varbinary(MAX)') as expense_image
                FROM @val.nodes('/document/data/group/row')  T(c)) as P
                where group_name = 'expense fields.doc-table-0'
        
            COMMIT TRANSACTION;
        END TRY
        BEGIN CATCH
            IF (XACT_STATE()) = -1
        BEGIN
            PRINT
               N'The transaction is in an uncommittable state.' +
               'Rolling back transaction.'
            ROLLBACK TRANSACTION;
        END;

        -- Test whether the transaction is committable. 
        IF (XACT_STATE()) = 1
        BEGIN
            PRINT
               N'The transaction is committable.' +
               'Committing transaction.'
            COMMIT TRANSACTION;   
        END;

            THROW;
            
        END CATCH;
END

Using Hyperlinks

The XML stream passed to the stored procedure will also contain up to three hyperlinks for editing the data in LincDoc, viewing the generated document, or electronically signing the document (assuming it actually contains at least one signature field). These links could be used, for example, in a workflow. The three links are tagged in the XML as edit-url, view-url, and sign-url. See the example code above, which retrieves the edit URL into the editUrl variable.

This page was: Helpful | Not Helpful

← 27.3. Creating an Email Template