Stored procedures

Custom stored procedures can be triggered from the object model (VBA). This can be very useful if much data needs to be processed since the data never has to leave the server.

Only custom stored procedures may be executed from VBA. The system has two requirements for custom procedures:

• It must have “csp_” prefixed to its name.
• It must be flagged with a -- FLAG_EXTERNALACCESS -- comment somewhere in the procedure body.

The following example creates a custom procedure that given the record ID of a coworker returns the total sum from all time reports from companies that the coworker is responsible for. In addition, an XML document is returned containing information on the ten largest customers for the coworker. If all goes well 0 is returned, otherwise 1.

CREATE PROCEDURE [dbo].[csp_toptencustomers]
   @@responsible   INT, 
   @@totalamount   INT OUTPUT
AS
-- FLAG_EXTERNALACCESS -- 
 
  DECLARE @retval INT
  SET @retval = 0
 
  -- Make sure the coworker exists and is active
 
  IF (NOT EXISTS(SELECT * FROM dbo.[coworker] WHERE [STATUS] = 0 AND [inactive] = 0 AND [idcoworker] = @@responsible))
     SET @retval = 1
 
  IF (@retval = 0)
  BEGIN
     -- Retrieve all time reports for the coworker
     SELECT @@totalamount = SUM(t.[units] * t.[price]) FROM dbo.[timereport] t
         INNER JOIN dbo.[company] c ON c.[idcompany] = t.[company]
         WHERE c.[STATUS] = 0 AND t.[STATUS] = 0 AND c.[responsible] = @@responsible
 
     -- Then retrieve the ten biggest customers
     SELECT TOP 10
         1 AS [TAG]
       , NULL AS [PARENT]
       , c.[name] AS [topten!1!company]
       , SUM(t.[units] * t.[price]) AS [topten!1!amount]
     FROM dbo.[timereport] t
       INNER JOIN dbo.[company] c ON c.[idcompany] = t.[company]
       WHERE c.[STATUS] = 0 AND t.[STATUS] = 0 AND c.[responsible] = @@responsible
       GROUP BY c.[name]
      ORDER BY [topten!1!amount]
       FOR XML EXPLICIT, BINARY BASE64
  END
 
  RETURN @retval
 
GO

In order for Lime CRM Server to become of the new custom procedure, these steps must be followed:

1. Execute the lsp_setdatabasetimestamp and lsp_refreshldc procedures. This refreshes the internal procedure cache.
2. Launch Component Services from the Start menu. Go to Computers | My Computer | COM+ Applications. Right-click Lundalogik Data Components and select Shut down from the popup menu.
3. Restart Lime CRM Desktop Client.

The above procedures needs to be done whenever adding or removing custom procedures and also when changing the signature of existing procedures, e.g. changing anything to do with procedure parameters.

Custom procedures are available from VBA through the Database.Procedures object. The following example lists all procedures and dumps information about their parameters:

Public Sub DisplayProcedureInfo()
 
   Dim Procedure As LDE.Procedure
   Dim Parameter As LDE.ProcedureParameter
 
   For Each Procedure In Database.Procedures
       Debug.Print "Procedure " & Procedure.Name
 
       For Each Parameter In Procedure.Parameters
           Debug.Print vbTab & Parameter.Name & " as " _
                             & Parameter.DataType _
                             & IIf (Parameter.IsOutput, " for output", "")
       Next Parameter
   Next Procedure
 
End Sub

A custom procedure can be accessed either by index or by name, where the latter is the safest alternative. In the following example the procedure created above is executed:

Public Sub ExecuteTopTen()
 
   Dim Procedure As LDE.Procedure
 
   Set Procedure = Database.Procedures("csp_toptencustomers")
 
   Procedure.Parameters("@@responsible").InputValue = Database.ActiveUser.ID
 
   ' Log the execution to the Info Log
   Procedure.Log = True
 
   ' Run it synchronously
   Procedure.Execute False
 
   ' Print the output parameter value
   Debug.Print Procedure.Parameters("@@totalamount").OutputValue
 
   ' Print the XML output
   Debug.Print Procedure.Result
 
End Sub

The Log property controls whether or not Lime CRM will log whether or not it was possible to execute the procedure at all. Any other logging needs to be handled by the procedure itself.

Procedures may be run synchronously or asynchronously depending on the boolean flag passed to Procedure.Execute. Procedures can only be run asynchronously if they don't have out parameters or return an XML document. Extended logging is recommended.