Tabrez Blog

Hi Everyone,

In this article, I will explain how you can call Stored Procedure from Dynamics NAV.

For this example I will create a table and a Stored Procedure in NAV DB as shown in below screenshot:

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>
-- =============================================
CREATE PROCEDURE CallSPFromNAV
-- 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

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

Code in NAV:

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

• First declare some DotNet variables to call SQL procedure
  
  Subtype in text for easy copy and paste:
  SQLConnection: 
  System.Data.SqlClient.SqlConnection.'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
  SQLCommand:
  System.Data.SqlClient.SqlCommand.'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
  SqlParamCycleCode:
  System.Data.SqlClient.SqlParameter.'System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
  SQLDbType:
  System.Data.DbType.'System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'
  
  
• 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 table in SQL:

Wallah!

I hope you understand how to call stored procedure from Dynamics NAV.

Stay Tuned!