Executing Stored Procedures From C/AL

aisnet

The following is a real world example of how can you execute and get data from SQL Stored Procedures.

SQL Server Stored Procedure

The following code is a stored procedure that generates a logistics company's Shipment No.:

/********************************************************************************************************************** *** AISNET-IT
***
*** Changes Log:
***
*** 20100709-PAP:
*** Generates a TourLine Logictics Shipment No. using post codes, service code and item code.
***
**********************************************************************************************************************/

CREATE PROCEDURE TOURLINE_NewShipment
(@SourceBranchCode VARCHAR(6)
, @ToPostCode VARCHAR(10)
, @ServiceType VARCHAR(3)
)
AS

DECLARE @vNewShipment VARCHAR(20);
DECLARE @vServiceType VARCHAR(3);
DECLARE @vNextShipmentNo INT;
DECLARE @vNextSUBShipmentNo INT;
DECLARE @ToBranchCode VARCHAR(6);
DECLARE @vFlag INT;

BEGIN
SET @vNewShipment = 'ERROR';
SET @vFlag = 0;


/*** Component: Next Shipment No. ***/
/******************************/
SET @vNextSUBShipmentNo = (
SELECT Value
FROM [euroterminal$Generic Data]
WHERE [Code] = 'TOURLINE2') + 1;

IF (@vNextSUBShipmentNo > 999) BEGIN
SET @vFlag = 1;
SET @vNextSUBShipmentNo = 1;
END

UPDATE [euroterminal$Generic Data]
SET Value = @vNextSUBShipmentNo
WHERE [Code] = 'TOURLINE2';

IF (@vFlag = 1) BEGIN
SET @vNextShipmentNo = (
SELECT Value
FROM [euroterminal$Generic Data]
WHERE [Code] = 'TOURLINE') + 1;

IF (@vNextShipmentNo > 5999999)
SET @vNextShipmentNo = 5000001;

UPDATE [euroterminal$Generic Data]
SET Value = @vNextShipmentNo
WHERE [Code] = 'TOURLINE';

END ELSE BEGIN

SET @vNextShipmentNo = (
SELECT Value
FROM [euroterminal$Generic Data]
WHERE [Code] = 'TOURLINE');
END


/*** Component: TL Branches ***/
/****************************/

SELECT @ToBranchCode = (
SELECT TOP 1 (BranchCode)
FROM TOURLINE.dbo.TL_Counties
WHERE CAST(PostCode AS INT) = @ToPostCode);
*/


/*** Component: New Shipment ***/
/********************************/
IF (@vNextSUBShipmentNo < 10) BEGIN
SET @vNewShipment = SUBSTRING(@SourceBranchCode, 3, 4)
+ @SourceBranchCode
--+ @ToBranchCode
--+ @ServiceType
+ CAST(@vNextShipmentNo AS VARCHAR(7))
+ '00'
+ CAST(@vNextShipmentNo AS VARCHAR(1));
END

IF (@vNextSUBShipmentNo > 10 AND @vNextSUBShipmentNo < 100) BEGIN
SET @vNewShipment = SUBSTRING(@SourceBranchCode, 3, 4)
+ @SourceBranchCode
--+ @ToBranchCode
--+ @ServiceType
+ CAST(@vNextShipmentNo AS VARCHAR(7))
+ '0'
+ CAST(@vNextShipmentNo AS VARCHAR(2));
END

IF (@vNextSUBShipmentNo > 99) BEGIN
SET @vNewShipment = SUBSTRING(@SourceBranchCode, 3, 4)
+ @SourceBranchCode
--+ @ToBranchCode
--+ @ServiceType
+ CAST(@vNextShipmentNo AS VARCHAR(7))
+ CAST(@vNextSUBShipmentNo AS VARCHAR(3));
END
END

SELECT @vNewShipment as NewShipment;
GO


NAV Side: Codeunit

The following code is a C/AL NAV codeunit that executes and reads the stored procedure's result:


//Automations And Constants/Variables

SQLAutomation (Automation): Microsoft SQLDMO Object Library.SQLServer
SQLResponse (Automation): Microsoft SQLDMO Object Library.QueryResults

vSQLDBServer: Text Type Variable
vSQLDBServerUsername: Text Type Variable;
vSQLDBServerPassword: Text Type Variable;
SQLStoredProcedure: Text Type Variable;

vShipmentNo: Code/Text Type Variable;


//CODE: You Must Provide SQL Database Information

vSQLDBServer := <sql_databse_server>;
vSQLDBServerUsername := <sql_databse_server_username>;
vSQLDBServerPassword := <sql_databse_server_password>;

CLEAR(SQLAutomation);
CREATE(SQLAutomation);

SQLAutomation.Connect(vSQLDBServer, vSQLDBServerUsername, vSQLDBServerPassword);

SQLStoredProcedure := STRSUBSTNO('EXEC <sql_database_name>.dbo.TOURLINE_NewShipment ''%1'',''%2'',''%3'''
, <Rec:TourLine>."TourLine Code"
, <Rec:Customer>."C.P."
, <Rec:TourLine>."Service Type"
);

SQLResponse := SQLStoredProcedure.ExecuteWithResults(SQLStoredProcedure);

vShipmentNo := SQLResponse.GetColumnString(1, 1);

IF vShipmentNo = 'ERROR' THEN BEGIN
MESSAGE(SQLResponse.GetColumnString(1, 2));
EXIT;
END;

rmv_RU

Using ADO automation is a better way.