PROCEDURE Clause

Description

Defines a name and optional parameters for a query.

Syntax

PROCEDURE name [param1 datatype[, param2 datatype[, ...]]

The PROCEDURE clause has these parts.

Part	Description

name	A name for the procedure. It must follow standard naming conventions.
param1, param2	One or more field names or parameters. For example:
	PROCEDURE Sales_By_Country [Beginning Date] DateTime, [Ending Date] DateTime;
	For more information on parameters, see PARAMETERS.
datatype	One of the primary Microsoft Jet SQL data types or their synonyms.

Remarks

An SQL procedure consists of a PROCEDURE clause (which specifies the name of the procedure), an optional list of parameter definitions, and a single SQL statement. For example, the procedure Get_Part_Number might run a query that retrieves a specified part number.

Notes

If the clause includes more than one field definition (that is, param-datatype pairs), separate them with commas.
The PROCEDURE clause must be followed by an SQL statement (for example, a SELECT or UPDATE statement).

Specifics (Microsoft Access)

When you copy a PROCEDURE clause into SQL view, Microsoft Access removes the clause from the SQL statement when you switch to another view. Removing the PROCEDURE clause doesn't affect the result of the query. If the PROCEDURE clause includes parameters, Microsoft Access replaces PROCEDURE and name with the PARAMETERS reserved word. The defined parameters aren't removed.

Example

This example names the query "Category_List."

PROCEDURE Category_List; SELECT DISTINCTROW CategoryName, CategoryID FROM Categories ORDER BY CategoryName;

This example names the query "Summary" and includes two parameters. The PROCEDURE clause is changed to the following PARAMETERS declaration:

PARAMETERS [Beginning Date] DateTime,
[Ending Date] DateTime;

PROCEDURE Summary [Beginning Date] DATETIME, [Ending Date] DATETIME; SELECT DISTINCTROW ShippedDate, OrderID, EmployeeID, 
Format(ShippedDate, "yyyy") AS Year FROM Orders 
WHERE ShippedDate Between [Beginning Date] And [Ending Date];