How to Use SQL Stored Procedures

By Allen G. Taylor

Stored procedures reside in the database on the server rather than execute on the client — where all procedures were located before SQL/PSM. After you define a stored procedure, you can invoke it with a CALL statement. Keeping the procedure on the server rather than on the client reduces network traffic, thus speeding performance. The only traffic that needs to pass from the client to the server is the CALL statement.

You can create this procedure in the following manner:

EXEC SQL
 CREATE PROCEDURE ChessMatchScore
( INscore CHAR (3),
  OUT result CHAR (10) )
 BEGIN ATOMIC
  CASE score
   WHEN '1-0' THEN
   SET result = 'whitewins' ;
   WHEN '0-1' THEN
   SET result = 'blackwins' ;
   ELSE
   SET result = 'draw' ;
  END CASE
END ;

After you have created a stored procedure like the one in this example, you can invoke it with a CALL statement similar to the following statement:

CALL ChessMatchScore ('1-0', :Outcome) ;

The first argumentis an input parameter that is fed to the ChessMatchScore procedure. The second argument is an embedded variable that accepts the value assigned to the output parameter that the ChessMatchScore procedure uses to return its result to the calling routine. In this case, it returns ‘white wins’.

SQL:2011 has added a couple of enhancements to stored procedures. The first of these is the introduction of named arguments.

Here’s the equivalent of the preceding call, with named arguments:

CALL ChessMatchScore (result => :Outcome,score =>'1-0');

Because the arguments are named, they can be written in any order without a danger of them being confused.

The second enhancement added in SQL:2011 is the addition of default input arguments. You can specify a default argument for the input parameter. After you do that, you don’t need to specify an input value in the CALL statement; the default value is assumed. (Of course, you would want to do this only if the default value were in fact the value you wanted to send to the procedure.)

Here’s an example of that usage:

EXEC SQL
 CREATE PROCEDURE ChessMatchScore
( INscore CHAR (3)DEFAULT '1-0',
  OUT result CHAR (10) )
 BEGIN ATOMIC
  CASE score
   WHEN '1-0' THEN
   SET result = 'whitewins' ;
   WHEN '0-1' THEN
   SET result = 'blackwins' ;
   ELSE
   SET result = 'draw' ;
  END CASE
END ;

You can now call this procedure thusly with the default value:

CALL ChessMatchScore (:Outcome) ;

Of course, you would only want to do this if the default value was in fact the value you wanted to send to the procedure.