How to call Store Procedure from Navision

Today, I try to create simple testing to call store procedure from navision using ADO. In fact, we can do a lot of thing by using ADO and store procedure in SQL. This testing only for sample ( I use Cronus Company for testing). This testing will show salesperson name after we send salesperson code parameter to the store procedure.

1. Create Store Procedure in SQL

CREATE PROCEDURE SP_Test @SName as Varchar(20) OUTPUT
AS
SELECT [CRONUS International Ltd_$Salesperson_Purchaser].Name as Nama from
[CRONUS International Ltd_$Salesperson_Purchaser] where
[CRONUS International Ltd_$Salesperson_Purchaser].Code = @SName

GO

2. Call Store Procedure from Navision

Variables:
ADOConnection : 'Microsoft ActiveX Data Objects 2.8 Library'.Connection
ADOCommand : 'Microsoft ActiveX Data Objects 2.8 Library'.Command
ADOParameter : 'Microsoft ActiveX Data Objects 2.8 Library'.Parameter
ADORecSet : 'Microsoft ActiveX Data Objects 2.8 Library'.Recordset
VarActiveConnection : Variant

IF ISCLEAR(ADOConnection) THEN
CREATE(ADOConnection);

ADOConnection.ConnectionString:=
'Driver={SQL Server};'
+ 'Server='+'cronus'+';'
+ 'Database='+'cronus'+';'
+ 'Uid='+'sa'+';'
+ 'Pwd='+'sa'+';';

ADOConnection.Open;

IF ISCLEAR(ADOCommand) THEN
CREATE(ADOCommand);

VarActiveConnection := ADOConnection;
ADOCommand.ActiveConnection := VarActiveConnection;

// CommandType property
ADOCommand.CommandText := 'SP_Test';
ADOCommand.CommandType := 4;
ADOCommand.CommandTimeout := 0;

//CreateParameter method
ADOParameter:=ADOCommand.CreateParameter('@SalesCode', 200, 1, 30,'AH');
ADOCommand.Parameters.Append(ADOParameter);

ADOCommand.Execute;

IF ISCLEAR(ADORecSet) THEN
CREATE(ADORecSet);

ADORecSet.ActiveConnection := VarActiveConnection;
ADORecSet.Open(ADOCommand);

WHILE NOT ADORecSet.EOF DO BEGIN
MESSAGE(FORMAT(ADORecSet.Fields.Item('Nama').Value));
ADORecSet.MoveNext;
END;

ADOConnection.Close;
CLEAR(ADOConnection);