Creating Stored Procedures

Introduction

Stored Procedures are a pre-compiled collection of SQL statements, which are stored in the database. They are useful for tasks that need to be executed multiple times, and can be called by client applications. This tutorial will walk you through creating and using stored procedures.

Creating a Stored Procedure

Creating a stored procedure involves writing a CREATE PROCEDURE statement, followed by the name you want to assign to the procedure. After the name, you will list any parameters that the procedure should take. Finally, you will write the SQL statements that should be executed when the procedure is called.

Here is the basic syntax for creating a stored procedure:

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

For example, let's create a stored procedure that selects all records from a table named 'Employees':

CREATE PROCEDURE SelectAllEmployees
AS
SELECT * FROM Employees
GO;

Executing a Stored Procedure

To execute a stored procedure, you simply need to write the EXECUTE command followed by the name of the procedure:

EXECUTE procedure_name;

Here is how you would call the 'SelectAllEmployees' procedure:

EXECUTE SelectAllEmployees;

Stored Procedure with Parameters

You can also create stored procedures with parameters. Parameters allow you to pass values to the stored procedure when you call it. Here's an example of a stored procedure with a parameter:

CREATE PROCEDURE SelectEmployee @EmployeeID int
AS
SELECT * FROM Employees WHERE ID = @EmployeeID
GO;

In this example, '@EmployeeID' is a parameter. When you call this procedure, you will need to provide a value for this parameter:

EXECUTE SelectEmployee 123;

Modifying a Stored Procedure

If you need to change the SQL statements in a stored procedure, you can use the ALTER PROCEDURE statement:

ALTER PROCEDURE procedure_name
AS
new_sql_statement
GO;

Here's an example:

ALTER PROCEDURE SelectAllEmployees
AS
SELECT FirstName, LastName FROM Employees
GO;

This modifies the 'SelectAllEmployees' procedure to select only the 'FirstName' and 'LastName' columns from the 'Employees' table.

Deleting a Stored Procedure

If you no longer need a stored procedure, you can delete it using the DROP PROCEDURE statement:

DROP PROCEDURE procedure_name;

Here's how you would delete the 'SelectAllEmployees' procedure:

DROP PROCEDURE SelectAllEmployees;

Conclusion

Stored procedures are a powerful feature of SQL that allow you to simplify your SQL code and improve performance. This tutorial provided an introduction to creating, using, modifying, and deleting stored procedures. Practice creating your own stored procedures to become more comfortable with this feature.