How to Create Stored Procedures in SQL Server

A SQL Server database is a complex system comprising many different objects. In addition to tables, it includes views, indexes, functions, stored procedures, triggers, and more. Among these, stored procedures stand out for their ability to handle complex operations such as logic processing, control flow, and transaction management. They enhance performance, help centralize, and enforce business logic within the database.

Stored procedures are a common feature of major RDBMSs, though each system supports stored procedures in its specific dialect. This article explores stored procedures in SQL Server, including their core purpose, unique features, benefits, and practical applications across various scenarios.

What are stored procedures in SQL Server?

Before diving deeper into stored procedures, let's first address a key question: What is a stored procedure in SQL Server?

Any SQL stored procedure is a prepared collection of one or more SQL statements saved in the database for reuse. Instead of writing the same SQL queries repeatedly to perform a task, you can create a stored procedure once and call it whenever needed. You can also pass parameters to the stored procedure, allowing it to execute according to those parameters.

Users, applications, or even other procedures can invoke SQL Server stored procedures. They are a powerful tool for automating tasks within the database: you can schedule to run at specific times, reducing the need for manual intervention. These aspects make stored procedures especially useful for efficiently handling repetitive or complex tasks.

It's important to distinguish stored procedures from functions. While both functions and stored procedures in SQL are reusable code blocks in SQL Server, their functionality has key differences. Functions are limited to SELECT statements and cannot modify database data. On the other hand, stored procedures can contain any SQL command and perform a wide range of operations, including data modifications.

Now, let's explore the advantages of stored procedures in SQL Server and why they are valuable to software developers.

Benefits of using stored procedures

One of the most obvious advantages of stored procedures is reusing certain queries. In addition to that, they offer many other benefits for database professionals.

Performance improvement

Stored procedures execute directly on the server, reducing client and server traffic. In SQL Server, a stored procedure generates an execution plan during its first run and stores it in the buffer pool. This plan can then be reused for subsequent executions, further improving performance.

Precompiled SQL queries

One of the main reasons for using stored procedures in SQL Server is their precompiled code. Since the execution plan is already optimized, compiling queries each time they run is unnecessary. Additionally, because stored procedures are on the database server, they allow for faster execution.

Code reusability

In SQL Server, a stored procedure is typically written once and can be executed multiple times across different clients and applications. Moreover, any changes to the stored procedure can be made directly in the database without modifying or redeploying the application.

Centralized SQL logic

Encapsulating complex data operations within procedures allows developers to avoid duplicating code across different parts of an application. This approach minimizes redundancy and makes updates and troubleshooting easier.

Security

In SQL Server, stored procedures can be a secure interface between users and the database. You can grant execute permissions on a stored procedure without giving direct access to the underlying tables. This prevents unauthorized users from running queries on sensitive data. Additionally, stored procedures can be encrypted to hide their source code, adding an extra layer of protection.

How to create a stored procedure in SQL Server

To create a stored procedure in SQL Server, you must have the CREATE PROCEDURE permission on the database and ALTER permission on the schema where the procedure will be created.

The following code shows the basic syntax for creating a stored procedure in SQL Server:

CREATE PROCEDURE <ProcedureName>
   @<ParameterName1> <data type>,
   @<ParameterName2> <data type>
AS   
   SET NOCOUNT ON;
   SELECT <your SELECT statement>;
GO

In this code, replace <ProcedureName> with the desired name of your stored procedure. Specify any parameters' names and data types, and include the SQL query you want the procedure to execute. While this example uses a SELECT statement, you can encapsulate any valid SQL query within a stored procedure.

Let's look at an example of an SQL stored procedure. Suppose we want to create a procedure that retrieves the top-selling products from the AdventureWorks2022 database (this database is a default test database for SQL Server, and we'll use it throughout this article for demonstration purposes).

CREATE PROCEDURE dbo.usp_GetTopSellingProducts @TopN INT
AS
BEGIN
  SET NOCOUNT ON;

  SELECT TOP (@TopN)
    p.Name AS ProductName
   ,SUM(s.LineTotal) AS TotalSales
  FROM Sales.SalesOrderDetail s
  INNER JOIN Production.Product p
    ON s.ProductID = p.ProductID
  GROUP BY p.Name
  ORDER BY TotalSales DESC;
END;

Throughout this article, we'll use dbForge Studio for SQL Server to demonstrate how SQL queries work. The Studio is an integrated development environment (IDE) for SQL Server databases, a more powerful alternative to the default SSMS IDE. It enables us to perform all database tasks in SQL Server, whether on-premises or in the cloud.

To execute a stored procedure in SQL Server, enter EXECUTE in a new query window, specify the required procedure, provide values for parameters (if applicable), and click Execute.

Here are some helpful tips on creating SQL Server stored procedures:

• Use meaningful names that indicate the procedure's purpose (usp_GetTopSellingProducts fetches top-selling products).
• Keep procedures modular by focusing each one on a single task or operation.
• Always include the schema prefix (such as dbo.usp_GetTopSellingProducts) to avoid confusion.
• Avoid using SELECT * - explicitly list the necessary columns for better clarity and efficiency.
• Use parameters instead of hard-coded values to make your procedures more flexible and secure.
• Turn on NOCOUNT to suppress "rows affected" messages and reduce unnecessary network traffic.
• Document your code with comments explaining any complex logic or special behavior.

Types of SQL stored procedures

Stored procedures in SQL Server are designed to perform various operations, each tailored to a specific purpose. They vary by origin and capability, ranging from built-in utilities to user-defined scripts and advanced procedures that interact with external systems. Let's explore the different types of stored procedures in SQL Server, their characteristics and typical use cases.

System stored procedures

SQL Server automatically creates predefined system stored procedures during the initial installation. Although developers often overlook these procedures, many are useful for database management, troubleshooting, and system configuration tasks.

Here are some commonly used system stored procedures in SQL Server:

• sp_rename - renames a database object, such as a stored procedure, view, or table.
• sp_changeowner - changes the ownership of a database object.
• sp_help - displays detailed information about a database object.
• sp_helpdb - provides information about the databases defined on the SQL Server instance.
• sp_helptext - returns the text of a stored procedure stored in SQL Server.
• sp_depends - lists all database objects that depend on a specific object.

User-defined stored procedures

User-defined stored procedures are the most commonly used type. They are custom objects created by users to perform specific tasks within a database. Typical examples include processing orders, calculating totals, or generating reports.

These procedures often contain one or more SQL queries that select, update, or delete data. They can accept input parameters and return output values. Additionally, user-defined stored procedures support using both DDL (Data Definition Language) and DML (Data Manipulation Language) commands within the same routine.

Extended stored procedures

SQL extended stored procedures enable the execution of external functions in languages other than SQL, such as C or C++. These procedures enable the integration of SQL Server with external applications, which is commonly used for integrating third-party tools or developing SQL Server-based data-driven applications.

When a client executes an extended stored procedure, the request is transmitted using the Tabular Data Stream (TDS) or Simple Object Access Protocol (SOAP). Upon receiving the request, the SQL Server locates and loads the associated DLL. The extended stored procedure returns result sets and parameters to the SQL Server via the Extended Stored Procedure API.

CLR stored procedures

CLR stored procedures are the recommended replacement for deprecated extended stored procedures in SQL Server. These procedures are written in .NET languages, such as C#, and executed within SQL Server. CLR stored procedures can be faster than T-SQL and offer enhanced functionality for complex tasks, particularly when working with external APIs. When achieving a goal with T-SQL is either impossible or too challenging, a CLR stored procedure can provide an effective solution.

Numbered stored procedures

Numbered stored procedures are SQL Server procedures identified and executed using a number. Typically, they are part of a sequence or serve as references within a specific application or system. Numbers or identifiers are included in procedure names for better tracking, organization, and version control, indicating the procedure's order or version.

A common use case for numbered stored procedures is in sequential execution, where a set of procedures must run in a specific order to perform complex operations. These procedures are executed according to their assigned numbers, ensuring the operations are completed correctly. It is important to note that numbered stored procedures are not a built-in feature of SQL Server.

How to execute a stored procedure in SQL Server

To execute a stored procedure in SQL Server, we can use the EXEC or EXECUTE command:

EXEC procedure_name;

Or

EXECUTE procedure_name;

For instance, we want to get the information about all databases on the server. For that, let our SQL Server execute the stored procedure sp_helpdb - a system stored procedure:

EXECUTE sp_helpdb;

Another option is using the GUI tool.

In dbForge Studio for SQL Server, connect to the target database and expand the Programmability node. You will see the list of procedures residing in the database. Select the necessary procedure, right-click it, and choose Execute from the menu:

The stored procedure execution depends on the presence of parameters.

Execute a stored procedure without parameters

When defining how to run a stored procedure in SQL Server, we need to pay attention to whether this procedure demands passing any input values to it. Most of system stored procedures do not require any parameters to input. For instance, let us execute the sp_help procedure:

Also, we can create a user-defined stored procedure that will retrieve the list of most popular products from the AdventureWorks2022 database.

CREATE PROCEDURE GetMostPopularProducts
AS
BEGIN
    SELECT 
        p.Name AS ProductName,
        SUM(sod.OrderQty) AS TotalQuantitySold,
        SUM(sod.LineTotal) AS TotalSales
    FROM 
        Sales.SalesOrderDetail sod
    INNER JOIN 
        Production.Product p ON sod.ProductID = p.ProductID
    GROUP BY 
        p.Name
    ORDER BY 
        TotalSales DESC;
END;

This stored procedure will not require passing any additional parameters. You only need to execute it.

Execute a stored procedure with parameters

As we mentioned earlier, stored procedures can accept parameters, and their work output will depend on those parameters. When you execute a stored procedure with parameters, you need to provide their values for the procedure to work correctly.

Assume we want to add a new category using the stored procedure.

CREATE PROCEDURE Production.AddNewProductCategory
    @CategoryName NVARCHAR(50)
AS
BEGIN
    -- Check if the category already exists
    IF NOT EXISTS (SELECT 1 FROM Production.ProductCategory WHERE Name = @CategoryName)
    BEGIN
        -- Insert the new category into the ProductCategory table
        INSERT INTO Production.ProductCategory (Name, ModifiedDate)
        VALUES (@CategoryName, GETDATE());
        
        PRINT 'New category added successfully.';
    END
    ELSE
    BEGIN
        PRINT 'Category already exists.';
    END
END
GO

We can use either a T-SQL statement or the GUI in dbForge Studio to achieve that.

The basic SQL query syntax is:

EXEC procedure_name value;

Let us add a new category called Video:

EXEC Production.AddNewProductCategory Video;

If we choose to execute a stored procedure with parameters from within the graphical interface, it will prompt us to provide the value:

Parameters allow us to filter results. We can specify the demands for the returned data set. Therefore, a SQL stored procedure with parameters becomes reusable and flexible.

Now, let us explore the parameters in SQL Server stored procedures.

How to work with parameters in SQL Server stored procedures

A stored procedure in SQL Server can have INPUT and OUTPUT parameters, up to 2100 parameters specified. Each parameter has a name, a data type, and a direction like input, output, or return. The SQL Server stored procedure default parameter is input. To set other parameter types, you need to specify them explicitly.

Input parameters in stored procedures

Input parameters are those we pass to the procedure when calling it. It is the most common approach.

Let us create a stored procedure for the SQL database AdventureWorks2022:

CREATE PROCEDURE GetEmployeeByID
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM HumanResources.Employee WHERE BusinessEntityID = @EmployeeID;
END;

This procedure retrieves the information about the employee according to their ID. Now, let us execute this stored procedure and pass it the parameter:

EXEC GetEmployeeByID @EmployeeID = 15;

Output parameters and return values

The output parameters of the stored procedure return values. Most commonly, they return single integer values like statuses. However, they can return multiple values in other scenarios.

Let's write a procedure that checks if an employee exists and returns 0 if found, or -1 if not.

CREATE PROCEDURE dbo.uspCheckEmployeeExists
    @EmployeeID INT
AS
BEGIN
    IF EXISTS (SELECT 1 FROM HumanResources.Employee WHERE BusinessEntityID = @EmployeeID)
        RETURN 0; -- success
    ELSE
        RETURN -1; -- not found
END;

Let us execute this stored procedure with the following parameters:

DECLARE @Result INT;
DECLARE @FirstName NVARCHAR(50)
       ,@LastName NVARCHAR(50)
       ,@JobTitle NVARCHAR(50);

-- Execute the stored procedure to check if the employee exists
EXEC @Result = dbo.uspCheckEmployeeExists @EmployeeID = 1;

-- If the employee exists, fetch and return their details
IF @Result = 0
BEGIN
  SELECT
    JobTitle, BirthDate, HireDate
  FROM HumanResources.Employee
  WHERE BusinessEntityID = 50;
END

How to modify and delete stored procedures

One of the key advantages of stored procedures is the ability to modify them. This feature allows us to adjust the behavior of a procedure for complex operations without affecting the rest of the database or application logic. This section will explore how to alter a stored procedure in SQL Server.

Additionally, when a stored procedure is no longer needed, we can drop it, so we will also cover how to delete stored procedures.

Modify an existing stored procedure

We use the ALTER PROCEDURE command to edit a stored procedure in SQL Server. This option allows us to change the parameters or the entire procedure body without deleting and recreating it. Note that you must have ALTER permission to perform this task.

The basic syntax for modifying the procedure is below.

ALTER PROCEDURE schema_name.procedure_name
    @param1 datatype,
    @param2 datatype = default_value, -- optional
    @param3 datatype OUTPUT           -- output
AS
BEGIN
    -- New or modified logic goes here
END;

Assume we have a stored procedure that retrieves the list of all employees with all their data.

CREATE PROCEDURE dbo.uspGetAllEmployees
AS
BEGIN
    SELECT * FROM HumanResources.Employee;
END;

We need to modify this procedure and add filters. We only want to obtain the BusinessEntityID, NationalIDNumber, JobTitle, and HireDate values. Therefore, we want to alter the existing stored procedure:

ALTER PROCEDURE dbo.uspGetAllEmployees
    @JobTitle NVARCHAR(50) = NULL
AS
BEGIN
    SELECT BusinessEntityID, NationalIDNumber, JobTitle, HireDate
    FROM HumanResources.Employee;
END;

dbForge Studio for SQL Server also allows you to alter stored procedures in SQL Server more easily. Select the necessary procedure, right-click it, and choose the option to open the procedure in the editor. Edit the code as needed, and click Apply Changes to save the modified stored procedure.

Drop a stored procedure

You can easily delete a stored procedure using the DROP PROCEDURE command. To perform this action, you must have ALTER permission on the schema to which the procedure belongs or CONTROL permission on the procedure itself.

DROP PROCEDURE <ProcedureName>;

If you prefer using GUI tools like SSMS or dbForge Studio, you can visually drop stored procedures in SQL Server. Select the procedure, right-click it, and choose Delete from the menu.

Deleting a procedure can cause dependent objects and scripts to fail if their relationships are not updated after the procedure is removed. However, if you create a new procedure with the same name and parameters, the references will remain intact and the logic will not be affected. Nonetheless, it's recommended that all references be updated to ensure consistency.

Advanced features of stored procedures in SQL Server

In the previous sections, we covered the basics of stored procedures. Now, let's explore advanced techniques that can significantly improve the functionality, performance, and efficiency of your SQL code.

Transactions in stored procedures

Transactions in stored procedures ensure that multiple operations are treated as a single unit - all changes are applied or none. This safeguards data integrity in the event of an error.

The following keywords manage transactions within stored procedures:

• BEGIN TRANSACTION - starts a transaction.
• COMMIT TRANSACTION - saves all changes if the operation completes successfully.
• ROLLBACK - undoes all changes if an error occurs

Uncommitted transactions aren't rolled back automatically when a procedure fails. Always include a ROLLBACK statement in the exception handling block to avoid leaving transactions open.

We want to update an employee's job title and log the change. If either step fails, both should be rolled back.

CREATE PROCEDURE dbo.uspUpdateJobTitle
    @EmployeeID INT,
    @NewJobTitle NVARCHAR(50)
AS
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;

        -- Update the employee's job title
        UPDATE HumanResources.Employee
        SET JobTitle = @NewJobTitle
        WHERE BusinessEntityID = @EmployeeID;

        -- Log the change (assume this table exists)
        INSERT INTO Audit.JobTitleChangeLog (EmployeeID, NewJobTitle, ChangeDate)
        VALUES (@EmployeeID, @NewJobTitle, GETDATE());

        COMMIT TRANSACTION;
        PRINT 'Update successful.';
    END TRY
    BEGIN CATCH
        ROLLBACK;
        PRINT 'Error occurred: ' + ERROR_MESSAGE();
    END CATCH
END;

Error handling in stored procedures

In practical scenarios, handling errors appropriately within stored procedures is necessary. In SQL Server, we can use the TRY...CATCH code block to catch errors and handle them.

The syntax for the code is:

BEGIN TRY
    -- Code that might throw an error
END TRY
BEGIN CATCH
    -- Error handling logic
    -- Use functions like ERROR_MESSAGE(), ERROR_LINE(), etc.
END CATCH

Assume we want to delete an employee record from the database, but we want to catch and handle errors.

CREATE PROCEDURE dbo.uspDeleteEmployee
    @EmployeeID INT
AS
BEGIN
    BEGIN TRY
        DELETE FROM HumanResources.Employee
        WHERE BusinessEntityID = @EmployeeID;

        PRINT 'Employee deleted successfully.';
    END TRY
    BEGIN CATCH
        PRINT 'An error occurred while deleting the employee.';
        PRINT 'Error Message: ' + ERROR_MESSAGE();
        PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR);
        PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR);
    END CATCH
END;

Note the following parameters:

• ERROR_MESSAGE() - returns the text of the error message.
• ERROR_NUMBER() - returns the error number.
• ERROR_SEVERITY() - severity of the error.
• ERROR_LINE() - defines the line number where the error occurred.
• ERROR_PROCEDURE() - defines the name of the procedure with the error.

We try to use the stored procedure to delete an employee:

EXEC dbo.uspDeleteEmployee @EmployeeID = 45;

If the deletion fails, the CATCH block will print a message and log error details.

Returning values from stored procedures

We can define output parameters in a stored procedure to return values. For example, we create a stored procedure to retrieve the product name and the total number of sales for a given ProductID.

CREATE PROCEDURE dbo.usp_GetProductSalesInfo
    @ProductID INT,
    @ProductName NVARCHAR(100) OUTPUT,
    @TotalSales INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- Retrieve the product name
    SELECT @ProductName = Name
    FROM Production.Product
    WHERE ProductID = @ProductID;

    -- Retrieve the total number of sales (sum of OrderQty)
    SELECT @TotalSales = ISNULL(SUM(OrderQty), 0)
    FROM Sales.SalesOrderDetail
    WHERE ProductID = @ProductID;
END;

Let us execute this procedure and retrieve the information for the product ID 25:

DECLARE @Name NVARCHAR(100)
       ,@Sales INT;

EXEC dbo.usp_GetProductSalesInfo @ProductID = 25
                                ,@ProductName = @Name OUTPUT
                                ,@TotalSales = @Sales OUTPUT;

SELECT
  @Name AS ProductName
 ,@Sales AS TotalSales;

In another scenario, we want information about some particular product. Let us create the following stored procedure:

CREATE PROCEDURE dbo.usp_GetProductDetailsByID @ProductID INT
AS
BEGIN
  SELECT
    ProductID
   ,Name
   ,ProductNumber
   ,Color
   ,StandardCost
   ,ListPrice
   ,Size
   ,Weight
   ,SellStartDate
   ,SellEndDate
  FROM Production.Product
  WHERE ProductID = @ProductID;
END;

The SELECT statement ensures that the output of our SQL stored procedure will include all details of the specified product.

EXEC dbo.usp_GetProductDetailsByID 5;

These options for working with stored procedures in SQL Server maximize the effectiveness of this valuable feature.

Conclusion

Stored procedures are powerful tools for database professionals working across all major database management systems, including SQL Server. When properly created and implemented, stored procedures help automate numerous routine tasks, reduce manual effort, enhance security, and guarantee the precise execution of frequent, complex operations.

This article provides essential knowledge about SQL Server stored procedures, their various types, and their practical through both T-SQL and graphical tools. In this context, dbForge Studio for SQL Server stands out as an excellent solution for easily creating and managing stored procedures.

You can explore the capabilities of dbForge Studio for SQL Server through a fully functional 30-day free trial. Test the tool in your work environment and see how it performs under real workload conditions.

FAQ