Docs Home SQL Server Docs

SQL Server Stored Procedures Reference

This section provides comprehensive reference information for Transact-SQL stored procedures in SQL Server. Stored procedures are a set of Transact-SQL statements grouped together to perform a specific task.

Overview of Stored Procedures

Stored procedures offer several benefits including improved performance, modularity, and enhanced security. They can accept input parameters, return output parameters, and return result sets.

Key concepts include:

Creating and Managing Stored Procedures

CREATE PROCEDURE

Use the CREATE PROCEDURE statement to create a new stored procedure.

CREATE PROCEDURE usp_GetEmployeeByID @EmployeeID INT AS BEGIN SELECT EmployeeID, FirstName, LastName, HireDate FROM Employees WHERE EmployeeID = @EmployeeID; END;

ALTER PROCEDURE

Use the ALTER PROCEDURE statement to modify an existing stored procedure.

ALTER PROCEDURE usp_GetEmployeeByID @EmployeeID INT, @OutputHireDate DATE OUTPUT AS BEGIN SELECT EmployeeID, FirstName, LastName, HireDate FROM Employees WHERE EmployeeID = @EmployeeID; SELECT @OutputHireDate = HireDate FROM Employees WHERE EmployeeID = @EmployeeID; END;

DROP PROCEDURE

Use the DROP PROCEDURE statement to remove a stored procedure.

DROP PROCEDURE usp_GetEmployeeByID;

Executing Stored Procedures

Use the EXECUTE or EXEC command to run a stored procedure.

EXEC usp_GetEmployeeByID @EmployeeID = 101; DECLARE @HireDate DATE; EXEC usp_GetEmployeeByID @EmployeeID = 102, @OutputHireDate = @HireDate OUTPUT; SELECT @HireDate AS EmployeeHireDate;

System Stored Procedures

SQL Server provides a rich set of system stored procedures (often prefixed with sp_) for managing and administering SQL Server. These procedures are typically stored in the master database.

Common System Stored Procedures

For a complete list and detailed descriptions, refer to the System Stored Procedures documentation.

Stored Procedure Parameters

Stored procedures can accept parameters to customize their behavior. Parameters can be input, output, or both.

Parameter Modes

Parameter Syntax

Parameters are declared with a name (starting with @) and a data type.

Parameter Name Data Type Mode Description
@CustomerID INT Input The ID of the customer to retrieve orders for.
@OrderCount INT Output The total number of orders found for the customer.
CREATE PROCEDURE usp_GetCustomerOrderCount @CustomerID INT, @OrderCount INT OUTPUT AS BEGIN SELECT @OrderCount = COUNT(*) FROM Orders WHERE CustomerID = @CustomerID; END;

Error Handling in Stored Procedures

Effective error handling is crucial for robust stored procedures. Use TRY...CATCH blocks for structured error management.

TRY...CATCH Blocks

Code that might raise an error is placed within the TRY block. If an error occurs, control is transferred to the CATCH block.

CREATE PROCEDURE usp_UpdateProductPrice @ProductID INT, @NewPrice DECIMAL(10, 2) AS BEGIN SET NOCOUNT ON; BEGIN TRY UPDATE Products SET UnitPrice = @NewPrice WHERE ProductID = @ProductID; PRINT 'Price updated successfully.'; END TRY BEGIN CATCH -- Get error details DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE(); -- Log the error or re-throw it RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH END;

@@ERROR and RAISERROR

While TRY...CATCH is preferred, older methods involved checking the @@ERROR global variable after each statement and using RAISERROR to signal errors.

Further Reading