Sep 3, 20214 min read

Call stored procedure from Dynamics NAV/Business Central On-Prem

Updated: Feb 21, 2022

Hi Everyone,

In this article, I will explain how you can call Stored Procedure from Dynamics NAV/Business Central (only work for On-Premises).

To accomplish this, I will create a table and a Stored Procedure in NAV/BC Database as shown in below screenshot:

Fig 1. Table and Stored Procedure in DB

Scripts in SQL:

————— — Table ————— CREATE TABLE EMPLOYEE( EmpID int identity(1,1) PRIMARY KEY, Name varchar(50), Salary decimal )

————— — Procedure ————— SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO — ============================================= — Author: <Author,,Tabrez Ajaz> — Create date: <Create Date,,17 Nov 2019> — Description: <Description,,Call from Navision/Business Central> — ============================================= CREATE PROCEDURE CallSPFromNAV_BC — Add the parameters for the stored procedure here @EmpName varchar(50), @EmpSalary decimal, @EmpID int output AS BEGIN — SET NOCOUNT ON added to prevent extra result sets from SET NOCOUNT ON; insert into EMPLOYEE select @EmpName,@EmpSalary set @EmpID = Scope_Identity() END GO

=================================================

C/AL Code in NAV:

Create any function in NAV or just use below code directly:

First declare some DotNet variables to call SQL procedure

For this demo i have created following function with 2 parameters: CallSPFromNAV(eName : Text[50];eSalary : Decimal)

NAV Code:

CallSPFromNAV(eName : Text[50];eSalary : Decimal)

// Create SQLCommand Object SQLCommand := SQLCommand.SqlCommand(); SQLCommand.CommandType := SQLCommand.CommandType.StoredProcedure;

// Create SQLConnection Object SQLConnection := SQLConnection.SqlConnection(‘Server=’+’localhost\TABREZ’+’;’ + ‘Database=’+’Demo Database NAV (10-0)’+’;’ + ‘Integrated Security=True’); // Use Integrated Security true for Windows Authentication // For SQL Authentication comment above code and uncomment below 2 lines //+ ‘Uid=’+’yourSQLUserName’+’;’ //+ ‘Pwd=’+’yourPassword’+’;’);

// Open SQL Connection SQLConnection.Open;

SQLCommand.CommandText(‘CallSPFromNAV’); // Pass Procedure name in CommandText Parameter SQLCommand.Connection := SQLConnection;

// Supply First Parameter: @EmpName // Initailize SQL Parameter Object SqlParamCycleCode := SqlParamCycleCode.SqlParameter(); // Parameter for Procedure SqlParamCycleCode.ParameterName := ‘@EmpName’; // Datatype for parameter SqlParamCycleCode.DbType := SQLDbType.String; // Set direction to Input for input parameters SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Input; // Value that supplied to parameter SqlParamCycleCode.Value := eName; // Provide size to passed parameter SqlParamCycleCode.Size := 250; // Add SQL Parameter Object to SQLCommand object SQLCommand.Parameters.Add(SqlParamCycleCode);

// Supply Second Parameter: @EmpSalary SqlParamCycleCode := SqlParamCycleCode.SqlParameter(); SqlParamCycleCode.ParameterName := ‘@EmpSalary’; SqlParamCycleCode.DbType := SQLDbType.Decimal; SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Input; SqlParamCycleCode.Value := eSalary; SqlParamCycleCode.Size := 250; SQLCommand.Parameters.Add(SqlParamCycleCode);

// Supply Third Parameter: @EmpID SqlParamCycleCode := SqlParamCycleCode.SqlParameter(); SqlParamCycleCode.ParameterName := ‘@EmpID’; SqlParamCycleCode.DbType := SQLDbType.Int32; // Set direction to Output for output parameters SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Output; // No need to provide value in case on output parameter [But when direction is both type InputOutput then pass value] SqlParamCycleCode.Value := ”; // When you want use the result from the SP then make sure to set the Size otherwise you will only get few characters in response SqlParamCycleCode.Size := 250; SQLCommand.Parameters.Add(SqlParamCycleCode);

// ExecuteNonQuery – to execute the procedure SQLCommand.ExecuteNonQuery; MESSAGE(‘Returned Employee ID: %1’,FORMAT(SQLCommand.Parameters.Item(‘@EmpID’).Value));

// Close SQL Connection SQLConnection.Close; // Clear SQLCommand Object CLEAR(SQLCommand); // Clear SQLConnection Object CLEAR(SQLConnection);

Call above method from a button or just call from any codeunit:

CallSPFromNAV(‘Tabrez’,50000.00);

Output Result:

Let’s check the table in SQL:

AL Code in Business Central:

Before going to write any code first add the settings.json file in your Project Solution by using the following steps as shown below: * Go to View -> Choose Command Pallete (shortcut: ctrl + shift + P) * Search for Open Workspace Settings (JSON) and select as shown below:

* Wrote code snippet like shown below based on your Business Central On-Premise version, in my case it is BC14 so 140, this Add-ins path may be different in your case.

Fig 2. settings.json file

{    
    "al.assemblyProbingPaths": [
        "./.netpackages", 
        "C:/WINDOWS/assembly",
        "C:/Program Files/Microsoft Dynamics 365 Business Central/140/Service/Add-ins" 
    ]
}

Let’s Create a page called “CallStoredProcedure” with the below code make sure to define the dotnet assembly for all the required SQL Variable:

page 80018 "CallStoredProcedure"
{
Caption = 'Call Stored Procedure - Demo';
PageType = Card;
ApplicationArea = All;
UsageCategory = Administration;

layout
{
    area(Content)
    {
    }
}

actions
{
    area(Processing)
    {
        action("Call SP from BC")
        {
            ApplicationArea = All;
            Promoted = true;
            PromotedIsBig = true;
            PromotedOnly = true;
            PromotedCategory = Process;
            Image = Insert;

            trigger OnAction()
            begin
                CallSPFromNAVorBC('Ajaz', 100000.00);
            end;
        }
    }
}

procedure CallSPFromNAVorBC(eName: Text[50]; eSalary: Decimal)
var
    SQLConnection: DotNet SQLConnection;
    SqlCommand: DotNet SqlCommand;
    SqlParamCycleCode: DotNet SqlParamCycleCode;
    SqlDbType: DotNet SqlDbType;
begin
    // Create SQLCommand Object
    SQLCommand := SQLCommand.SqlCommand();
    SQLCommand.CommandType := SQLCommand.CommandType.StoredProcedure;

    // Create SQLConnection Object
    SQLConnection := SQLConnection.SqlConnection('Server=' + 'DESKTOP-SL5HPGA\BCDEMO' + ';'
    + 'Database=' + 'Demo Database BC (14-0)' + ';'
    + 'Integrated Security=True');
    // Use Integrated Security true for Windows Authentication
    // For SQL Authentication comment above code and uncomment below 2 lines
    //+ 'Uid='+'yourSQLUserName'+';'
    //+ 'Pwd='+'yourPassword'+';');

    // Open SQL Connection
    SQLConnection.Open;

    SQLCommand.CommandText('CallSPFromNAV_BC'); // Pass Procedure name in CommandText Parameter
    SQLCommand.Connection := SQLConnection;

    // Supply First Parameter: @EmpName
    // Initailize SQL Parameter Object
    SqlParamCycleCode := SqlParamCycleCode.SqlParameter();
    // Parameter for Procedure
    SqlParamCycleCode.ParameterName := '@EmpName';
    // Datatype for parameter
    SqlParamCycleCode.DbType := SQLDbType.String;
    // Set direction to Input for input parameters
    SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Input;
    // Value that supplied to parameter
    SqlParamCycleCode.Value := eName;
    // Provide size to passed parameter
    SqlParamCycleCode.Size := 250;
    // Add SQL Parameter Object to SQLCommand object
    SQLCommand.Parameters.Add(SqlParamCycleCode);

    // Supply Second Parameter: @EmpSalary
    SqlParamCycleCode := SqlParamCycleCode.SqlParameter();
    SqlParamCycleCode.ParameterName := '@EmpSalary';
    SqlParamCycleCode.DbType := SQLDbType.Decimal;
    SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Input;
    SqlParamCycleCode.Value := eSalary;
    SqlParamCycleCode.Size := 250;
    SQLCommand.Parameters.Add(SqlParamCycleCode);

    // Supply Third Parameter: @EmpID
    SqlParamCycleCode := SqlParamCycleCode.SqlParameter();
    SqlParamCycleCode.ParameterName := '@EmpID';
    SqlParamCycleCode.DbType := SQLDbType.Int32;
    // Set direction to Output for output parameters
    SqlParamCycleCode.Direction := SqlParamCycleCode.Direction.Output;
    // No need to provide value in case on output parameter [But when direction is both type InputOutput then pass value]
    SqlParamCycleCode.Value := '';
    // When you want use the result from the SP then make sure to set the Size otherwise you will only get few characters in response
    SqlParamCycleCode.Size := 250;
    SQLCommand.Parameters.Add(SqlParamCycleCode);

    // ExecuteNonQuery - to execute the procedure
    SQLCommand.ExecuteNonQuery;
    MESSAGE('Returned Employee ID: %1', FORMAT(SQLCommand.Parameters.Item('@EmpID').Value));

    // Close SQL Connection
    SQLConnection.Close;
    // Clear SQLCommand Object
    CLEAR(SQLCommand);
    // Clear SQLConnection Object
    CLEAR(SQLConnection);
end;

var
    i: Integer;
}

dotnet
{
 assembly("System.Data")
 {
    Version = '4.5.0.0';
    Culture = 'neutral';
    PublicKeyToken = 'b77a5c561934e089';

    type("System.Data.SqlClient.SqlConnection"; "SqlConnection")
    {
    }
    type("System.Data.SqlClient.SqlCommand"; "SqlCommand")
    {
    }
    type("System.Data.SqlClient.SqlParameter"; "SqlParamCycleCode")
    {
    }
    type("System.Data.DbType"; "SqlDbType")
    {
    }
    type("System.Data.SqlClient.SqlDataReader"; "SqlDataReader")
    {
    }
 }
}

Look for the page and click on Call SP from the BC button you will get the return ID of the newly created entry in Employee Table, Result as shown in the below screenshot: