How to: Run SQL Store Procedures

In this article

LS One Site Manager

LS Central and LS Nav

DD can execute Store Procedures, both the once that return value and those that do not.

LS One Site Manager

With LS One Site Manager Demo data comes a Scheduler Job that runs a few Store Procedures subjobs. You can find a sample in the Subjob setup, look up Subjobs that start with the name p-spXXXXX. These jobs are Action jobs, and they will look for actions with an Object Name starting with the Store Procedure name that is set in the subjob. The jobs will execute the Parameter value as is when the Store Procedure is called. The same subjob can call different Store Procedures as long as they all start with the name specified in the subjob setup.

Sample of LS One Site Manager subjob

Sample of a record in the REPLICATIONACTIONS table

Copy 
ActionID    Action    ObjectName                            AuditContext    
5            4        spSECURITY_SetLocalProfileHash_1_0    5BD7C5DA-F27F-4F72-9B2F-BF1E9FAF6336

Parameters    
@Login=N'admin',@DATAAREAID=N'LSR',@LocalProfileHash=N'8914CB6CD60957EB0E35395725F07810'    

DateCreated                    DATAAREAID
2013-11-27 20:40:54.200        LSR

LS Central and LS Nav

In current LS Nav versions, there is no direct support to run Store Procedure jobs within the LS Nav Scheduler. This can be done by calling the jobs from a code.

The sample below calls a Store Procedure named sp_test1 with parameter 1. The return values are three fields that can be pulled the same way as if it was a reply from table query. If the Store procedure does not return any value, you can skip the "HasAnswer" part.

Copy
LS Nav Codeunit Sample
VAR
DataDir : Automation 'TransAutomClient'.TransAutomClient";
 
// Create Interactive Connection to DDSRV Host with SQL Database Connection string
DataDir.CreateConnection('DDSRV','server=ddsrv;nt=tcp;dbname=nav;user=super;|ms|none',
TRUE);
 
// Set Job to be Procedure job
DataDir.SetRequestType(5);        // Procedure
DataDir.SetResultType(1);        // List
DataDir.SetProcedure('sp_test1', '1');
 
// Send job to DD
IF NOT DataDir.Send() THEN
  EXIT;
 
// If Store procedure does not returns value, this part can be skipped
// CreateConnection call does not need to be interactive if there is no return value
IF NOT DataDir.HasAnswer() THEN
  EXIT;
 
WHILE DataDir.NextValueList() DO BEGIN
  IntVal := DataDir.GetInt(0);
  IntVal := DataDir.GetInt(1);
  StrVal := DataDir.GetString(2);
END;
 
// Done reading result, disconnect from DD
DataDir.Disconnect();