SQL CREATE PROCEDURE Statement

The CREATE PROCEDURE statement in SQL is used to create stored procedures. Stored procedures are pre-compiled SQL code blocks that you can save and reuse multiple times. They offer several advantages, including improved performance, better code organization, and enhanced security.

CREATE PROCEDURE: Definition and Usage

Instead of repeatedly writing and executing the same SQL query, you can write it once as a stored procedure and then simply call (execute) that procedure whenever you need it. This simplifies your application code and often improves efficiency. Stored procedures can also accept parameters, allowing for more flexible and dynamic database interactions.

Syntax


CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Replace procedure_name with your desired name and sql_statement with the SQL code you want to include in the procedure. The GO statement is a batch separator used in some database systems (like SQL Server).

Example

Creating and Executing a Stored Procedure

This example creates a stored procedure named "SelectAllCustomers" that selects all records from the "Customers" table (assuming a table named 'Customers' already exists).

Creating the Procedure


CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

Output (CREATE PROCEDURE)

The CREATE PROCEDURE statement itself doesn't return data; it simply creates the stored procedure in the database.

To execute the stored procedure, use the EXEC command:

Executing the Procedure


EXEC SelectAllCustomers;

Output (EXEC SelectAllCustomers)


(All rows and columns from the Customers table will be displayed here.)