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 paste this SQL Statement into a 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 the procedure in the query analyzer, we just run it by enter this
CALL testProc()
… and hit F5