How do you create a stored procedure in SQL?

If you work with databases, you might have heard of stored procedures. They are reusable blocks of SQL code that can perform complex tasks, improve performance, and enhance security. In this article, you will learn how to create a stored procedure in SQL, how to execute it, and how to modify or delete it.

1 What is a stored procedure?

A stored procedure is a named collection of SQL statements that can be invoked by other programs or users. It has the ability to accept input parameters, return output values, and use variables, loops, conditions, and other logic. Additionally, stored procedures can perform transactions, error handling, and logging. There are several advantages to using stored procedures; they reduce network traffic and improve efficiency by executing multiple SQL statements at once, encapsulate business logic to ensure consistency and accuracy across applications and users, and protect the database from unauthorized access and malicious attacks by granting permissions only to the stored procedure itself.

Add your perspective

2 How to create a stored procedure?

To create a stored procedure in SQL, you need to use the CREATE PROCEDURE statement, followed by the name of the procedure, the optional parameters, and the body of the procedure. The body of the procedure contains the SQL statements that define the logic and functionality of the procedure. The syntax of the CREATE PROCEDURE statement may vary slightly depending on the database system you use, but the general format is:

CREATE PROCEDURE procedure_name (parameter1 datatype, parameter2 datatype, ...)
AS
BEGIN
  -- SQL statements
END

For example, the following code creates a stored procedure named get_customers that accepts a country name as a parameter and returns the customer ID, name, and phone number of all customers from that country:

CREATE PROCEDURE get_customers (@country varchar(50))
AS
BEGIN
  SELECT customer_id, customer_name, phone
  FROM customers
  WHERE country = @country;
END

Add your perspective

Mohd Salim

Principal Technical Architect at Hays
Report contribution
To create a stored procedure in SQL, use the CREATE PROCEDURE statement, followed by the procedure logic enclosed in a BEGIN...END block.

Like
Divyanshu Kumar

|| AI/ML Developer || Programmer ||
(edited)
Report contribution
To create a stored procedure, you use the CREATE PROCEDURE statement followed by the procedure name, parameters (if any), and the SQL code inside a BEGIN...END block. Example: CREATE PROCEDURE GetEmployeeDetails @EmpID INT AS BEGIN SELECT Name, Department, Salary FROM Employees WHERE EmployeeID = @EmpID; END;

Like
Mauro Thiago da Silva, MBA

Analista de Sistemas Sênior | Master of Business Administration - MBA | FIAP
Report contribution
To create a stored procedure in SQL: -- Example of creating a stored procedure CREATE PROCEDURE StoredProcedureName AS BEGIN -- Procedure logic here SELECT * FROM YourTable; END; This is a basic example. You can add more complex logic, parameters, and data manipulation as needed. For example, to add a parameter to the procedure: CREATE PROCEDURE StoredProcedureName @Parameter INT AS BEGIN -- Procedure logic using the parameter SELECT * FROM YourTable WHERE Column = @Parameter; END; After creating the stored procedure, you can execute it using: EXEC StoredProcedureName @Parameter = ParameterValue; Remember to adjust the code as needed to meet the specific requirements of your database and business logic.

Like

3 How to execute a stored procedure?

To execute a stored procedure in SQL, you need to use the EXECUTE or EXEC statement, followed by the name of the procedure and the optional arguments. The arguments must match the parameters defined in the CREATE PROCEDURE statement in number, order, and data type. The syntax of the EXECUTE statement is: EXECUTE procedure_name argument1, argument2, ... or EXEC procedure_name argument1, argument2, ... For example, the following code executes the get_customers stored procedure with the argument 'USA': EXECUTE get_customers 'USA' or EXEC get_customers 'USA' This will return the customer ID, name, and phone number of all customers from the USA.

Add your perspective

Divyanshu Kumar

|| AI/ML Developer || Programmer ||
(edited)
Report contribution
To execute a stored procedure, you use the EXEC or EXECUTE keyword followed by the procedure name and any required parameters. Example: EXEC GetEmployeeDetails 101;

Like

4 How to modify a stored procedure?

To modify a stored procedure in SQL, you need to use the ALTER PROCEDURE statement, followed by the name of the procedure, the optional parameters, and the new body of the procedure. The syntax of the ALTER PROCEDURE statement is similar to the CREATE PROCEDURE statement, except that you use the keyword ALTER instead of CREATE. The syntax is:

ALTER PROCEDURE procedure_name (parameter1 datatype, parameter2 datatype, ...)
AS
BEGIN
  -- SQL statements
END

For example, the following code modifies the get_customers stored procedure by adding a new parameter for the minimum order value and a new condition to the WHERE clause:

ALTER PROCEDURE get_customers (@country varchar(50), @min_order decimal(10,2))
AS
BEGIN
  SELECT customer_id, customer_name, phone
  FROM customers
  WHERE country = @country
  AND total_orders >= @min_order;
END

Add your perspective

Divyanshu Kumar

|| AI/ML Developer || Programmer ||
(edited)
Report contribution
To modify a stored procedure, you use the ALTER PROCEDURE statement followed by the procedure name and the updated SQL code. EXAMPLE: ALTER PROCEDURE GetEmployeeDetails @EmpID INT AS BEGIN SELECT Name, Department, Salary, JoinDate FROM Employees WHERE EmployeeID = @EmpID; END;

Like

5 How to delete a stored procedure?

To delete a stored procedure in SQL, you need to use the DROP PROCEDURE statement, followed by the name of the procedure. The syntax of the DROP PROCEDURE statement is: DROP PROCEDURE procedure_name For example, the following code deletes the get_customers stored procedure: DROP PROCEDURE get_customers Note that once you drop a stored procedure, you cannot execute it or alter it anymore. You need to recreate it if you want to use it again.

Add your perspective

Sravan Sai Rahul Nalla

Software Developer III @Paycom | Graduate Student at University of Houston - MSCS
Report contribution
To delete a stored procedure in SQL, you would use a command similar to the following : DROP PROCEDURE IF EXISTS procedure_name; " IF EXISTS" clause is optional but useful as it prevents an error if the stored procedure does not exist. This command will permanently remove the specified stored procedure from the database. Remember, once deleted, the procedure is not recoverable and would need to be recreated if needed again.

Like

6 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?

Add your perspective

How do you create a stored procedure in SQL?

1

2

3

4

5

6

1 What is a stored procedure?

2 How to create a stored procedure?

3 How to execute a stored procedure?

4 How to modify a stored procedure?

5 How to delete a stored procedure?

6 Here’s what else to consider

Computer Science

Rate this article

Thanks for your feedback

More articles on Computer Science

More relevant reading

How do you create a stored procedure in SQL?

1

2

3

4

5

6

1 What is a stored procedure?

2 How to create a stored procedure?

3 How to execute a stored procedure?

4 How to modify a stored procedure?

5 How to delete a stored procedure?

6 Here’s what else to consider

Computer Science

Rate this article

Thanks for your feedback

Explore Other Skills