iConsole Search Definition Reference Guide › Stored Procedures (SPs) › Example SPs

Example SPs

These examples define a simple search for e-mail events whose title contains a specific word or phrase. The SP takes one parameter—the string that you want to match—and returns the event ID and title.

Example: Stored Function for Oracle

CREATE OR REPLACE FUNCTION MyEmailSearch_V2_1(MyMatch VARCHAR2)
RETURN SYS_REFCURSOR IS
     MyCursor SYS_REFCURSOR;
BEGIN
     OPEN MyCursor FOR
     SELECT event.eventUID AS "event_uid", event.EventText2 AS "title"
          FROM Wgn_V_Event_2 event
     WHERE event.EventText2 LIKE '%' || MyMatch || '%'
          AND (event.EventMajorType=2 AND event.EventMinorType<>18);
     RETURN MyCursor;
END;

Note the following:

• The MyMatch parameter passes the required phrase to the stored function.
• The WHERE clause also includes the constraint on event major and minor types to ensure the search only yields e-mail events.
• SYS_REFCURSOR is used to return the result.

Example: Stored Procedure for SQL Server

CREATE PROCEDURE MyEmailSearch_V2_1(@MyMatch NVARCHAR(255))
AS
BEGIN
     SET NOCOUNT ON
     SELECT event.eventUID AS "event_uid", event.EventText2 AS "title"
          FROM Wgn_V_Event_2 event
     WHERE event.EventText2 LIKE '%' + @MyMatch + '%'
          AND (event.EventMajorType=2 AND event.EventMinorType<>18)
END

Note the following:

• The @MyMatch parameter passes the required phrase to the SP.
• SPs for SQL Server must include the SET NOCOUNT ON directive.