How to call stored procedure from MS Dynamics NAV 2018 using Automation Variable?

Automation Variable in NAV 2018

Frankly this is my first interaction with automation variable in my project. So I want to share with NAV community how can we connect our SQL Database and interact with stored procedures to process NAV database.

Step 1. Take the below variables.

Name                  DataType                          Subtype

lADOConnection Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Connection

lADOCommand Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Command

lADOParameter Automation 'Microsoft ActiveX Data Objects 2.8 Library'.Parameter

lvarActiveConnection Variant

Step 2. Write the below code :-

IF ISCLEAR(lADOConnection) THEN

CREATE(lADOConnection,FALSE,TRUE);

//lADOConnection.ConnectionString := 'Driver={SQL Server};Server=Server Address;Database=Database NAme;Uid=sa;Pwd=Password;

Check with other users also but provide some admin rights on SQL.

  lADOConnection.Open;

  IF ISCLEAR(lADOCommand) THEN
  CREATE(lADOCommand,FALSE,TRUE);
           
  lvarActiveConnection := lADOConnection;
  lADOCommand.ActiveConnection := lvarActiveConnection; 

  lADOCommand.CommandText := 'Stored Procedure Name';

  lADOCommand.CommandType := 4;
  lADOCommand.CommandTimeout := 0; 


  lADOCommand.Execute;

  lADOConnection.Close;
  CLEAR(lADOConnection);
  CLEAR(lADOParameter);
  CLEAR(lADOCommand);

 MESSAGE('Done Successfully');

So NAV Readers just try this code if you face any issue , comment below. Also for some usage bookmark this page.