Creating Stored Procedures

You can create your own stored procedures for use with the AVEVA Historian. All procedure names will be stored in the Runtime database. The stored procedure text will be stored in the Microsoft SQL Server and retrieved at startup (from procedures created in an older session) as well as at creation time (from procedures created in the current session). Temporary procedures will not be supported. No arguments are allowed.

As with Microsoft SQL Server support, support for dynamic stored procedures for the historian will be such that when defining a stored procedure, you can create a stored procedure only in the current database, and the CREATE PROCEDURE statement cannot be combined with other SQL statements in a single batch.

Creating your own stored procedures is useful when you want to execute certain types of queries through a typical ODBC connection. The historian requires a specific ODBC configuration unless you create a stored procedure to execute the query.

For example, the following query creates a stored procedure that returns the timestamp and value for the tag 'ReactLevel" for the last 15 minutes.

CREATE PROCedure MyProc

AS

SELECT DateTime, TagName, Value

FROM History

WHERE TagName = 'ReactLevel'

AND DateTime >= DATEADD(mi, -15, GETDATE())

AND DateTime <= GETDATE()