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
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 @ToDateEND
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 VariantADOCommand.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
Be the first to comment