This article gives an overview of how to use Caché Stored Procedures in Caché Monitors Query Analyzer.

Although executing a stored procedure can be as easy as listing it on a line by itself in a Caché-SQL command batch, you should make a habit of prefixing all stored procedure calls with the CALL keyword, like this:

CALL myProc()

To create a stored procedure, we can also use the Caché Monitor Query Analyzer. If you have registered Caché Servers you can open a new Query Analyzer by pressing CTRL+O (for more info read here) and connect to the SAMPLES Namespace.

Copy and pase this SELECT Statement into the new Query Analyzer and press F5 to execute the statement.

SELECT
SUM(Cinema.Film.TicketsSold) AS TicketsSold,
Cinema.FilmCategory.CategoryName
FROM Cinema.Film
INNER JOIN Cinema.FilmCategory ON Cinema.FilmCategory.ID = Cinema.Film.Category
GROUP BY Cinema.FilmCategory.CategoryName
ORDER BY Cinema.FilmCategory.CategoryName

The result shoud look like this:

TicketsSold CategoryName
-------------- ---------------
95700 Action
95500 Animation
39000 Comedy
109000 Drama
90000 Thriller

Now copy and paste this SQL Code and press F5 again:

CREATE PROCEDURE testProc()
BEGIN
SELECT
SUM(Cinema.Film.TicketsSold) AS TicketsSold,
Cinema.FilmCategory.CategoryName
FROM Cinema.Film
INNER JOIN Cinema.FilmCategory ON Cinema.FilmCategory.ID = Cinema.Film.Category
GROUP BY Cinema.FilmCategory.CategoryName
ORDER BY Cinema.FilmCategory.CategoryName;
END

Then, to test out the procedure in the query analyzer, we just run it by
enter this

CALL testProc()

... and F5 ;-)