Calling stored procedures

Lime CRM supports customized stored procedures and version 10.5 onward also supports the ability to call these via Lime CRM Web Service. A stored procedure can be used to update, calculate or retrieve data.

A stored procedure is executed using the ExecuteProcedure method, which has the following signature (C# syntax):

string ExecuteProcedure(ref string procedureXml, bool async)

The parameter procedureXml is an XML node containing a description of the procedure and how it should be called.

The parameterasync indicates whether the procedure should be executed synchronously or asynchronously, and should only be used when the procedure does not return any data because Lime CRM Web Service does not return data for asynchronous execution.

The function also returns an XML document, the contents of which depend entirely on what data the procedure returns.

In order to make a stored procedure available externally, its name must be preceded by “csp_” and the – FLAG_EXTERNALACCESS – flag must be added to the procedure code.

The following example generates a procedure that takes the entry ID for a company and returns the number of employees via a bidirectional parameter and the entries for the employees in the form of an XML document.

If all goes well, in this case the specified company is returned as 0; otherwise an error code is returned, in this case 1, meaning that the company does not exist.

CREATE PROCEDURE [dbo].[csp_getemployees]
   @@idcompany     int,
   @@employeecount int OUTPUT
AS
 -- FLAG_EXTERNALACCESS --
 
 DECLARE @retval int
 SET @retval = 0
 
 IF (NOT EXISTS(SELECT * FROM dbo.[company] WHERE [status] = 0 AND [idcompany] = @@idcompany))
     SET @retval = 1
 
 IF (@retval = 0)
 BEGIN
     SELECT @@employeecount = COUNT(*) FROM dbo.[person] WHERE [status] = 0 AND [company] = @@idcompany
 
     SELECT
               1 AS [TAG]
             , NULL AS [PARENT]
             , [name] AS [person!1!name]
             , [email] AS [person!1!email]
             , [phone] AS [person!1!phone]
           FROM dbo.[person] WHERE [status] = 0 AND [company] = @@idcompany
            ORDER BY [name]
             FOR XML EXPLICIT, BINARY BASE64
 END
 
 RETURN @retval

The following example shows the XML specification for calling a procedure called csp_getemployees.

<procedure name="csp_getemployees" log="0" timeout="300">
  <parameter name="@@idcompany" value="1356001" valuetype="3"/>
  <parameter name="@@employeecount" value="0" valuetype="3" output="1"/>
</procedure>

Name

Specifies the name of the procedure that will be executed - this is mandatory.

Log

Specifies whether the execution and any errors should be logged in the infolog, and accepts “0” and “1”, where “0” is used if nothing is specified.

Timeout

Specifies how many seconds the operation may take as a maximum. If nothing is specified, the default value for SQL Server is used. The minimum value is “0” and the maximum value is “300”. If the execution is expected to take longer than five minutes (300/60), we recommend that you generate a job in SQL Server, which will be started by the procedure instead.

A parameter element must exist for every parameter that is sent to the procedure.

Name

Specifies the name of the parameter - this is mandatory.

Value

Indicates the value of the parameter - this is mandatory.

Valuetype

Indicates the data type for the value, and is used to enable Lime Server to recreate the value correctly before it is sent to SQL Server. For this reason, the parameter is specified in the form of a VARTYPE constant. Data type values are available at http://msdn.microsoft.com/en-us/library/10wcf65z(VS.85).aspx.

Output

Specifies the direction of a parameter and accepts “0” and “1”, where “0” is used by default if nothing is specified. “0” indicates that the direction is “in”, while “1” indicates that the direction is both in and out, i.e. that the parameter is bidirectional. Bidirectional parameters may be both used and updated by the procedure, which means that you must treat the parameter exactly like an “in” parameter when calling the procedure. The procedure usually just updates the parameter, but this parameter must still be specified, since Lime CRM Web Service can only return parameters that are specified.

You always get back an updated version of the specification. In the specification, returnvalue has appeared. This is a status code in the form of a whole integer that was returned from the procedure. A value of “0” means that the execution was successful while other values are error codes specific to the procedure. In addition, all parameters with output set to “1” have been updated (provided that the procedure has modified these values).

<procedure name="csp_getemployees" log="0" timeout="300" returnvalue="0">
  <parameter name="@@idcompany" value="1356001" valuetype="3"/>
  <parameter name="@@employeecount" value="4" valuetype="3" output="1"/>
</procedure>

You always get back an XML document in the following format:

<?xml version="1.0" encoding="UTF-16" ?>
<data/>

All data that your procedure returns ends up in the data node. Each procedure returns the following:

<?xml version="1.0" encoding="UTF-16" ?>
<data>
  <person name="Karl Kula" email="karl.kula@fakeorg.se" phone="08-123 123"/>
  <person name="Charles Bullit" email="charles.bullit@fakeorg.com" phone=""/>
  <person name="Grodan Boll" email="grodan.boll@fakeorg.se" phone=""/>
  <person name="Frog von Ball" email="frog.von.ball@fakeorg.com" phone=""/>
</data>