How To Software SQL Server Stored Procedures Stored Procedures Deliver High Efficiency and Security Benefits Share Pin Email Print woraput chawalitphon/E+/Getty Images Software Databases Documents Spreadsheets Presentations Desktop Publishing Graphic Design Animation & Video by Mike Chapple An IT professional with more than 10 years of experience in the fields of databases and cybersecurity. Updated November 21, 2018 Microsoft SQL Server provides the stored procedure mechanism to simplify the database development process by grouping Transact-SQL statements into manageable blocks. Stored procedures are appreciated by most SQL Server developers who find the efficiency and security benefits they reap are well worth the upfront investment in time. Benefits of Using Stored Procedures Why should a developer use stored procedures? Here are the key benefits of this technology: Precompiled execution: SQL Server compiles each stored procedure once and then re-utilizes the execution plan. This results in tremendous performance boost when stored procedures are called repeatedly.Reduced client/server traffic: If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.Efficient reuse of code and programming abstraction: Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.Enhanced security controls: You can grant users permission to execute a stored procedure independently of underlying table permissions. Stored procedures are similar to user-defined functions, but there are subtle differences. Structure Stored procedures are similar to the constructs seen in other programming languages. They accept data in the form of input parameters that are specified at execution time. These input parameters (if implemented) are utilized in the execution of a series of statements that produce some result. This result is returned to the calling environment through the use of a recordset, output parameters and a return code. That may sound like a mouthful, but you'll find that stored procedures are actually quite simple. Example Let's take a look at a practical example related to the table named inventory shown at the bottom of this page. This information is updated in real time, and warehouse managers are constantly checking the levels of products stored at their warehouse and available for shipment. In the past, each manager would run queries similar to the following: SELECT Product, QuantityFROM InventoryWHERE Warehouse = 'FL' This resulted in inefficient performance at the SQL Server. Each time a warehouse manager executed the query, the database server was forced to recompile the query and execute it from scratch. It also required the warehouse manager to have knowledge of SQL and appropriate permissions to access the table information.Instead, the process can be simplified through the use of a stored procedure. Here's the code for a procedure called sp_GetInventory that retrieves the inventory levels for a given warehouse. CREATE PROCEDURE sp_GetInventory@location varchar(10)ASSELECT Product, QuantityFROM InventoryWHERE Warehouse = @location The Florida warehouse manager can then access inventory levels by issuing the command: EXECUTE sp_GetInventory 'FL' The New York warehouse manager can use the same stored procedure to access that area's inventory: EXECUTE sp_GetInventory 'NY' Granted, this is a simple example, but the benefits of abstraction can be seen here. The warehouse manager does not need to understand SQL or the inner workings of the procedure. From a performance perspective, the stored procedure works wonders. The SQL Server creates an execution plan once and then reutilizes it by plugging in the appropriate parameters at execution time.Now that you've learned the benefits of stored procedures, get out there and use them. Try a few examples and measure the performance enhancements achieved—you'll be amazed! Inventory Table ID Product Warehouse Quantity 142 Green beans NY 100 214 Peas FL 200 825 Corn NY 140 512 Lima beans NY 180 491 Tomatoes FL 80 379 Watermelon FL 85 Continue Reading Here Is a Guide to Using the Command Line Utility in Microsoft SQL Learn About NOT NULL Constraints in Microsoft SQL Server This Tutorial Offers a Great Introduction to SQL Server 2012 To NULL or NOT NULL in SQL: Find Out Test for SQL Injection Vulnerabilities to Avoid Jeopardizing Your Site Control Access Permission to Your Database Using DCL How Do You Use TRY...CATCH to Fix SQL Server Errors? An Easy Guide to Creating Databases and Tables in SQL The Quick Way to Import and Export SQL Server Data Which Edition of Microsoft's SQL Server Is Right For You? What You Need to Do to Secure SQL Databases How to Select Data Within Ranges in SQL Creating a Trace With Profiler in SQL Server 2008 Learn How to Query Your Database The 8 Best SQL Books of 2019 What Is a UNIQUE Constraint in a SQL Server?