CREATE PROCEDURE Statement

Creates a stored procedure (procedure: A sequence of declarations and statements in a module that are executed as a unit. Procedures in a Visual Basic for Applications (VBA) module include both Sub and Function procedures.).

 Note   The Microsoft Access database engine does not support the use of CREATE PROCEDURE, or any of the DDL (Data Definition Language (DDL): The language used to describe database attributes, especially tables, fields, indexes, and storage strategy. ANSI defines this to have the tokens CREATE, DROP, and ALTER. DDL is a subset of structured query language (SQL).) statements, with non-Microsoft Access database engine databases.

Syntax

CREATE PROCEDURE procedure (param1 datatype[, param2 datatype][, ...]) AS sqlstatement

The CREATE PROCEDURE statement has these parts:

Part Description
procedure A name for the procedure.
param1, param2 From one to 255 field names or parameters (parameter: A value that is assigned to a variable at the beginning of an operation or before an expression is evaluated by a program. A parameter can be text, a number, or an argument name assigned to another value.).
datatype One of the primary Microsoft Access SQL Data Types or their synonyms.
sqlstatement An SQL statement such as SELECT, UPDATE, DELETE, INSERT, CREATE TABLE, DROP TABLE, and so on.
Example
CREATE PROCEDURE Sales_By_CountryRegion ([Beginning Date] DateTime, [Ending Date] DateTime) AS SELECT Customer, [Ship Address] WHERE [Shipped Date] Between [Beginning Date] And [Ending Date]

Remarks

An SQL procedure consists of a PROCEDURE clause that specifies the name of the procedure, an optional list of parameter definitions, and a single SQL statement (SQL string/statement: An expression that defines an SQL command, such as SELECT, UPDATE, or DELETE, and includes clauses such as WHERE and ORDER BY. SQL strings/statements are typically used in queries and in aggregate functions.).

A procedure name cannot be the same as the name of an existing table.

 
 
Applies to:
Access 2007