SQL in a Nutshell

Name

CREATE PROCEDURE

Synopsis

Stored procedures provide conditional processing and programmatic capabilities in the database-server environment. Stored procedures are capsules of programming code that may accept passed parameters and accomplish complicated tasks. Stored procedures also are very valuable because they are precompiled: they execute their tasks quickly and efficiently because the database optimizer has already built an execution plan for the code.

Vendor	Command
SQL Server	Supported
MySQL	Not supported (see the CREATE FUNCTION command)
Oracle	Supported
PostgreSQL	Not supported

Like many of the other CREATE statements, the vendors have built a great deal of variety into this command.

SQL99 Syntax and Description

CREATE PROCEDURE procedure_name
[parameter data_type attributes ][,...n]
AS
code block

For a more complete listing of the SQL99 syntax, refer to CREATE FUNCTION. The advanced features of CREATE FUNCTION also apply to CREATE PROCEDURE .

Because each vendor has implemented his own procedural extensions to the SQL language, a broad discussion about coding stored procedures is not appropriate for this book. However, the basics of stored-procedure programming is discussed. Other O’Reilly books, such as Transact-SQL Programming, by Kevin Kline, Lee Gould & Andrew Zanevsky (1999), and Oracle PL/SQL Programming, Second Edition, by Steven Feuerstein with Bill Pribyl (1997), provide excellent discussions about their respective programming languages.

Microsoft SQL ...