Executing SQL Stored Procedures

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

Meta