====== 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.

===== Creating a procedure =====
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.

<code sql>
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
</code>

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

  - Execute the ''lsp_setdatabasetimestamp'' and ''lsp_refreshldc'' procedures. This refreshes the internal procedure cache.
  - 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.
  - 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.

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

<code VB>
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
</code>
 
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:

<code VB>
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
</code>

==== Logging ====
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.

==== Execution mode ====
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.