O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required