Stored Procedures and Navision

If you are executing a specific SQL query very often, it would be usefully to have it as Stored Procedure on the SQL Server.

Stored Procedures is a collection of SQL statements.

There are 2 ways to create a store procedure:

1. In the SQL Server Management Studio – you can right click on Stored Procedures and chose to create a new Stored Procedure. It will then create a template for you, which you just have to fill out and execute
2. The second way is to build a script by yourself

A script to create a Stored Procedure could look like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: Ingrid Byllemos
— Create date:
— Description: Test of SP
— =============================================
CREATE PROCEDURE Count_Calls
— Add the parameters for the stored procedure here
@FromDate DateTime,
@ToDate DateTime
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

— Insert statements for procedure here
SELECT COUNT(*) FROM [Call]
WHERE [Start Date] BETWEEN @FromDate and @ToDate

END
GO

When the script first is created, then simply execute it and a stored procedure is created on the SQL server.

Let us now take a look on how to use the stored procedure from Navision. Here we will be using ADO as described in an earlier article.

It is a bit difference from earlier described. First you have to open the connection to the SQL server:

CREATE(ADOConnection);

ConnectionString := 'PROVIDER=SQLOLEDB;SERVER=SQLServer;DATABASE=SQLDatabase;'+
………..'UID=SQLUserID;PWD=SQLPwd';
ADOConnection.ConnectionString(ConnectionString);
ADOConnection.Open;

This you should already have known how to do. Now we have to setup and open a ADO Command.

First we create the ADO Command and sets the ActiveConnection. Notice we use a Variant to transfer the connection from ADOConnection to ADOCommand.

CREATE(ADOCommand);

ActiveConnection := ADOConnection;
//ActiveConnection is type Variant

ADOCommand.ActiveConnection := ActiveConnection;

Next we have to setup the actual stored procedure, which has to be executed:

ADOCommand.CommandText := ‘[Count_Calls]’;
ADOCommand.CommandType := 4;
ADOCommand.CommandTimeout := 0;

And the add Parameters to the command:

ADOParameter := ADOCommand.CreateParameter(‘@FromDate’,7,1,0,’05-01-2007′);
ADOCommand.Parameters.Append(ADOParameter);

ADOParameter := ADOCommand.CreateParameter(‘@ToDate’,7,1,0,’13-01-2007′);
//(name = @ToDate,type = 7 (date), direction = 1 (in), size = 0 (default), value = ’13-01-2007′)

ADOCommand.Parameters.Append(ADOParameter);

Notice we are using CreateParameter to add the parameters to the command.

Now execute the command and fetch the query result into a recordset:

ADOCommand.Execute;

CREATE(ADORecordset);
ADORecordset.ActiveConnection := ADOConnection;
ADORecordset.Open(ADOCommand);
ADORecordset.MoveFirst;

MESSAGE(FORMAT(ADORecordset.Fields.Item(0).Value));

Finally close the connection:

ADORecordset.Close;
ADOConnection.Close;
CLEAR(ADOConnection);

In the example we have been using the following automations:

• ‘Microsoft ActiveX Data Objects 2.8 Library’.Connection
• ‘Microsoft ActiveX Data Objects 2.8 Library’.Command
• ‘Microsoft ActiveX Data Objects 2.8 Library’.Parameter
• ‘Microsoft ActiveX Data Objects Recordset 2.8 Library’.Recordset