Creating stored procedures in MS SQL Server is as easy as taking a walk in a park, all that you need is a park map to do that. This SQL Server Stored Procedure Tutorial provides you with the map that you will need to create your own stored procedures. In addition to that, you will also get optimized routes that will enable you to easily get from one point to another. If you would like to be serenaded with the virtues and vices of stored procedures then I recommend you read this article Advantages and Disadvantages of stored procedures. This article will cover the following topics.
  • SQL Stored procedures best practices (SQL Server)
  • How to create a SQL Stored procedure
  • SELECT stored procedure
  • INSERT stored procedure
  • UPDATE stored procedure
  • DELETE stored procedure
  • Summary

Top 10 SQL stored procedures best practices

  1. Avoid prefixing your stored procedure names with sp_ - SQL Server uses sp_ to identify stored procedures. It is recommended to use the prefix up for user procedure. Others use sp without the underscore. Whatever prefixing method you adopt, use it consistently
  2. The maximum number of characters in your procedure name should not exceed 128 characters.
  3. For search stored procedures, consider the use of optional parameters - if you have more than one search field instead of creating separate procedures for each
  4. Use transaction processing when executing more than one query that affects multiple tables - when updating, creating or deleting. If an error occurs, you can use rollback feature of transactions. This will maintain the integrity goal of data/information
  5. Always use SET NOCOUNT ON especially when dealing with SELECT queries. This will prevent other results from interfering with your result sets.
  6. Always use the BEGIN {statements} END block. This will explicitly use transactions
  7. Optimize your SQL stored procedures by; (1) avoiding using SELECT *… use explicit column names and (2) use schema names when referencing database objects
  8. Use the try and catch blocks to handle errors when something goes wrong
  9. Comment your stored procedures when necessary
  10. Prefix all your stored procedures with table names after the prefix such as up. This will group related stored procedures together.
sql server stored procedures

How to create a SQL Stored procedure

The following code is used to create SQL stored procedures [sql] -- ============================================= -- Author:                            <Rodrick Kazembe> -- Create date: <06-03-2014> -- Description:            <Brief description of the stored procedure> -- ============================================= CREATE PROCEDURE upProcedureName [@parameter DataType] AS BEGIN [SET NOCOUNT ON;] -- {SELECT|INSERT|UPDATE|DELETE} statements END [/sql] HERE,
  • “-- sentences” is used to comment code. It is optional but recommended
  • “CREATE PROCEDURE upProcedureName” specifies the name of the stored procedure
  • “@parameter DataType” is optional, can be u ed to specify parameters to be used in the WHERE clause or INSERT, DELETE, and UPDATE statements
  • “BEGIN {body} END” explicitly makes the stored procedure use transactions
  • “SET NOCOUNT ON;” is optional but strongly recommended for SELECT queries

Customers ordering (real world example) stored procedures

Check this article for the ERD and data dictionary for a customer ordering database. The ERD was designed in MySQL but the data dictionary is DBMS agnostic so you can use it to create the database in SQL Server. The diagram below shows the ERD in Management Studio sql server order db erd CodeBlog has already done that for you. You can download the SQL Server Database scripts and database backup for SQL Server 2012 below for a $0 cost price of only sharing on Facebook or tweeting. We thank you for your support in advance.

[viraldownloader id=272]

How to create SELECT SQL stored procedure

Suppose you want to create a stored procedure that will look up the all products from the database and return the id, product name, descript and quantity at hand, you can use the following code to do that. For simplicity’s sake, we have skipped the commenting section but you should include it in production code [sql] CREATE PROCEDURE upProductsLookUp AS BEGIN SET NOCOUNT ON; SELECT product_id,product_name,description,quantity_at_hand FROM products; END [/sql] Executing the stored procedure in SQL Server management studio [sql] EXEC OrdersDB.[dbo].[upProductsLookUp] [/sql] Executing the above command in SQL server will give you the following results select_stored_procedure

How to create INSERT SQL stored procedure

Suppose you would like to add a new product to the database using a stored procedure, you can use the following code to create the INSERT stored procedure. We will limit the procedure to adding only the product name and description for simplicity’s sake [sql] CREATE PROCEDURE [dbo].[upProductsAddNew] @product_name varchar(145), @description varchar(250) AS BEGIN INSERT INTO products (product_name,description) VALUES (@product_name,@description); END [/sql] HERE, The insert stored procedure accepts two input parameters product name and description of varchar data types with lengths of 145 and 250 respectively. These parameters are used in the regular INSERT statement Execute the following code [sql] EXEC  [dbo].[ upProductsAddNew] @product_name = N'Beats Ear Phones', @description = N'Superb ear phones by doctor Dre.' [/sql] Run the upProductsLookUp procedure to see the effects of the above query

How to create an UPDATE SQL stored procedure

The above code did not supply the product quantity at hand, in real world application; quantities are updated when the supply invoice / goods received note is processed into the system. We will write a stored procedure that accepts the product id and quantity to be incremented to the existing stock. [sql] CREATE PROCEDURE [dbo].[upProductsUpdateQuantity] @product_id int, @quantity int = 0 AS BEGIN UPDATE products SET quantity_at_hand = quantity_at_hand + @quantity WHERE product_id = @product_id; END [/sql] HERE,
  • “@quantity int = 0” initializes our variable to zero (0). This will help maintain data integrity when the code executing the code supplies a DB NULL value.
Run the following code to increment the quantity of product id 3 with 21 [sql] EXEC @return_value = [dbo].[upProductsUpdateQuantity] @product_id = 3, @quantity = 21 [/sql] Use upProductsLookUp to see the effects of the update stored procedure

How to create a DELETE stored procedure

In real world applications; we usually archive records and mark them as deleted for history purposes. In this tutorial however, we will actually remove the record from the database. Our stored procedure will accept the product id as an input parameter. [sql] CREATE PROCEDURE [dbo].[upProductsDelete] @product_id int = 0 AS BEGIN DELETE FROM products WHERE product_id = @product_id; END [/sql] We will delete the product with id number 2 [sql] EXEC @return_value = [dbo].[upProductsDelete] @product_id = 2 [/sql]

Summary

Creating stored procedures in SQL server is extremely easy. You can create stored procedures for SELECT, INSERT, UPDATE, and DELETE statements. Following best practices will help you get the most out of your stored procedures. Our examples all prefixed the stored procedures with up followed by relational table name Products then the action to be performed. Doing this will help group procedures manipulating a table together. The next series of tutorials will cover SQL stored procedures advanced topics such as;
  • Transaction processing
  • Errors handling using try… catch blocks
  • Using variables inside stored procedures
  • Passing optional parameters
  • And many more tips
Subscribe to our newsletter to ensure you never miss an update from us. Remember to white list us by adding us to your contacts when you subscribe.