Creating Stored Procedures in SQL Server

What is a Stored Procedure?

A stored procedure is a precompiled collection of one or more SQL statements stored under a name and processed as a unit.

They can accept parameters, perform operations like queries or updates, and return results or status.

• Improves performance by reducing network traffic.
• Enhances security by controlling access to data.
• Simplifies complex operations into reusable modules.

Syntax for Creating Stored Procedures

The basic syntax to create a stored procedure uses the CREATE PROCEDURE statement followed by the procedure name and optional parameters.

The procedure body contains the SQL statements to execute.

• Procedure names should be descriptive and follow naming conventions.
• Parameters can be input, output, or both.
• Use BEGIN and END to define the procedure body clearly.

Example: Creating a Simple Stored Procedure

Here is an example of a stored procedure that retrieves all records from a Customers table.

Example: Stored Procedure with Parameters

Stored procedures can accept parameters to filter or customize the operation. Below is an example that retrieves customers from a specific city.

Executing Stored Procedures

After creating a stored procedure, you execute it using the EXEC or EXECUTE command followed by the procedure name and parameters if any.

• EXEC procedure_name;
• EXEC procedure_name @param1 = value;