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:
- 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
- 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
SET QUOTED_IDENTIFIER ON
— Author: Ingrid Byllemos
— Create date:
— Description: Test of SP
CREATE PROCEDURE Count_Calls
— Add the parameters for the stored procedure here
— 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
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:
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.
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′);
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′)
Notice we are using CreateParameter to add the parameters to the command.
Now execute the command and fetch the query result into a recordset:
ADORecordset.ActiveConnection := ADOConnection;
Finally close the connection:
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