Microsoft Learn

Documentation for SQL Server Database Engine

Stored Procedures

Stored procedures are a set of Transact-SQL statements that are compiled and stored on the server. They can be executed by users or applications. Stored procedures offer advantages such as:

Improved performance through pre-compilation.
Enhanced security by controlling access to underlying tables.
Reduced network traffic by sending only the procedure call instead of the entire SQL statement.
Reusability and modularity of code.

Creating a Stored Procedure

You can create a stored procedure using the CREATE PROCEDURE statement.

CREATE PROCEDURE usp_GetCustomerName
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT CustomerName FROM Customers WHERE CustomerID = @CustomerID;
END
            

Executing a Stored Procedure

Stored procedures can be executed using the EXECUTE or EXEC statement.

EXECUTE usp_GetCustomerName @CustomerID = 10;

Or with the short form:

EXEC usp_GetCustomerName 10;

Modifying a Stored Procedure

To modify an existing stored procedure, use the ALTER PROCEDURE statement.

ALTER PROCEDURE usp_GetCustomerName
    @CustomerID INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT CustomerName, Email FROM Customers WHERE CustomerID = @CustomerID;
END
            

Dropping a Stored Procedure

Use DROP PROCEDURE to remove a stored procedure from the database.

DROP PROCEDURE usp_GetCustomerName;