Working with a Stored Procedure / SPROC is considered an advanced skill in SQL development. Sometimes a stored procedure is abbreviated to sproc. Stored Procedures / SPROCs are precompiled SQL statements that can be executed in a controlled manner. They can accept input parameters, output parameters and a single return value. They are similar to functions but are wider in scope. A function may perform a single operation like splitting an address into its individual components. A Stored procedure would instead encapsulate a number of functions and operations to perform a complete procedure. Stored procedures can help improve the efficiency and security of your database operations. In this article, we will explore the basics of writing SQL-stored procedures. We will look at how to create a stored procedure in SQL, calling the stored procedure with the exec SQL stored procedure command, how to alter and then finally how to drop a stored procedure.
Keywords:
sproc, create procedure sql, create stored procedure sql, sql server stored procedure, calling stored procedure, exec sql stored procedure
Stored procedure / SPROC table of contents
Learn SQL online
What is a Stored Procedure?
A stored procedure is a set of SQL statements that are stored in the database and can be executed as a single unit. It is typically used for complex database operations that need to be performed frequently. Stored procedures offer several advantages:
- Code Reusability: You can reuse the same code for various applications or queries, reducing duplication and ensuring consistency.
- Performance Optimization: Stored procedures are precompiled and stored in the database, which can lead to improved performance as they are executed faster than ad-hoc queries.
- Security: Stored procedures can restrict direct access to tables and provide controlled access to the data, enhancing security.
- Transaction Management: You can encapsulate multiple SQL statements within a single stored procedure, allowing you to manage transactions effectively.
- Maintenance: Changes to a stored procedure can be made centrally, simplifying maintenance and reducing the need to update code in multiple places.
Created Stored Procedure SQL syntax
To write a basic SQL stored procedure, you’ll need to define its name, parameters, and the SQL statements it will contain. Let’s start with a simple example:
CREATE PROCEDURE GetEmployeeByID
@EmployeeID INT
AS
BEGIN
SELECT * FROM Employees
WHERE EmployeeID = @EmployeeID
END
In this example:
CREATE PROCEDUREis the SQL statement used to create a stored procedure / SPROC.- You then specify the name of the procedure. In the above example we have called it: GetEmployeeByID
- After this you specify the input and output parameters. By default each parameter you specify is an input parameter and notice that the parameters have an @ symbol in from of them. You also need to specify the data type that is placed after the parameter name. In this case we have created a parameter
@EmployeeIDwith data type integer. Parameter values can the be passed to the stored procedure when you call it. ASsignals the start of the procedure’s code block. The stored procedure is encapsulated with the BEGIN and END keywords this is equivalent to using brackets in other languages.- Inside the procedure, you can include any valid SQL statements. This includes INSERT, DROP, DELETE and UPDATE statements. In this case, we’re retrieving an employee’s data based on their ID. We could then proceed this with many other operations.
Stored procedure with output parameters
To execute a stored procedure with an output parameter then add the word OUTPUT after the data type declaration. In the example below you can see there are two parameters one input and then an output. Each of the parameters is separated by a comma. The value of the output parameter is then assigned in either the SELECT statement or by using a SET @VariableName = ‘A Value’. In the example below the result of the COUNT of records is assigned to @EmployeeCount.
CREATE PROCEDURE GetEmployeeCountByDepartment
@DepartmentID INT,
@EmployeeCount INT OUTPUT
AS
BEGIN
SELECT @EmployeeCount = COUNT(*)
FROM Employees
WHERE DepartmentID = @DepartmentID
END
EXEC SQL stored procedure / SPROC
To execute a stored procedure, you can use the EXEC statement. This is the command to use when calling a stored procedure. Specify the name of the stored procedure you want to execute followed by the parameters that you want to pass to the stored procedure.
EXEC GetEmployeeByID @EmployeeID = 123
Let’s not look at how to execute a stored procedure with output parameters. In the below example, we first define two parameters with the keyword DECLARE. These are defined as integers and the @DepartmentID is assigned a value of 123.
Next, we execute the EXEC command to execute the stored procedure / SPROC, we pass the parameters @DepartmentID and @TotalEmployees but notice that this time we put the keyword OUTPUT after the @TotalEmployees parameter. When the stored procedure is run the @TotalEmployees variable will be populated with the value from the stored procedure.
Finally, the output value can be seen by running a SELECT statement that will return the value in the @TotalEmployees parameter.
DECLARE @DepartmentID INT = 123
DECLARE @TotalEmployees INT
-- Execute the stored procedure, passing the DepartmentID and output parameter
EXEC GetEmployeeCountByDepartment @DepartmentID, @TotalEmployees OUTPUT
-- The @TotalEmployees variable now holds the employee count
SELECT @TotalEmployees AS 'Total Employees in Department 123'
Modifying Stored Procedures
You can modify a stored procedure by using the ALTER PROCEDURE statement. You write the code for your stored procedure / SPROC in exactly the same way you do for creating it. Just change the keyword CREATE to ALTER. For example, if you want to add another parameter to the GetEmployeeByID stored procedure / SPROC, you can do the following:
ALTER PROCEDURE GetEmployeeByID
@EmployeeID INT,
@DepartmentID INT
AS
BEGIN
SELECT * FROM Employees
WHERE EmployeeID = @EmployeeID AND DepartmentID = @DepartmentID
END
Dropping a Stored Procedure
If you want to remove a stored procedure, you can use the DROP PROCEDURE statement:
DROP PROCEDURE GetEmployeeByID
This will permanently delete the GetEmployeeByID stored procedure from the database.
Best Practices for Writing Stored Procedures
When writing stored procedures, it’s important to follow best practices to ensure efficiency, maintainability, and security:
Use Meaningful Names: Choose descriptive names for your stored procedures to make their purpose clear.
Parameterize Inputs: Use parameters to make your stored procedures flexible and safe from SQL injection.
Error Handling: Implement error handling within your stored procedures to gracefully handle exceptions.
Comment Your Code: Add comments to explain the purpose and usage of your stored procedures.
Keep It Simple: Avoid complex logic in stored procedures. If a procedure becomes too complex, consider breaking it into smaller procedures.
Testing: Test your stored procedures thoroughly to ensure they work as expected.
Documentation: Maintain documentation for all your stored procedures, including their inputs and outputs.
Conclusion
SQL stored procedures are a valuable tool for managing and optimizing database operations. By encapsulating SQL statements, they provide code reusability, performance improvements, security, and more. As you gain experience in writing stored procedures, you’ll be better equipped to handle complex database tasks efficiently and securely. Remember to follow best practices and maintain proper documentation to ensure that your stored procedures remain a useful asset in your database management toolkit.
Please see the other SQL learning articles below as part of this learning SQL online course.
If you would like an instructor lead SQL course that goes into more detail with worked examples then we also provide a video course to learn SQL online hosted on UDEMY.
