Stored Procedures In SQL | An Overview - Enterprise DNA (2022)

In this blog, we’re going to discuss stored procedures in SQL that you can utilize to save a set of code and use it repeatedly whenever you need it. Stored procedures are similar to views. However, you can conduct common table operations such as DROP, TRUNCATE, DELETE, etc. with stored procedures which you can’t perform with views.

Stored procedures are also pre-compiled so that it runs faster than views. It also minimizes the quantity of data sent to the database server.

Syntax For Stored Procedures In SQL: Without Parameters

To create a stored procedure without parameters, you need to start with the CREATE function. Then add the PROC function, type the procedure name right after, and add the AS function.

Stored Procedures In SQL | An Overview - Enterprise DNA (2)

Creating a stored procedure in SQL is similar to creating tables and views. However, the difference is in the process of getting data.

For instance, if we want to get data from a view, we use “SELECT * FROM view_name.” On the other hand, for stored procedures, we use the EXEC which means “execute,” and then type the stored procedure name after.

Stored Procedures In SQL | An Overview - Enterprise DNA (3)

Once we execute a stored procedure, it will also bring up the information based on the instructions or commands that you’ve added.

Syntax For Stored Procedures In SQL: With Parameters

Let’s move to creating stored procedures with input parameters. The process is similar to the previous example that I demonstrated. The only difference is you need to include the parameters before the AS function and also the data type right after.

Stored Procedures In SQL | An Overview - Enterprise DNA (4)

You can also utilize the parameter you’ve declared in the creation of the stored procedure in your commands. This is for you to save time entering multiple values. For instance, instead of manually typing customerID1, customerID2, and so on, we utilized @custid or the parameter to avoid having a long set of code.

Stored Procedures In SQL | An Overview - Enterprise DNA (5)

Please take note that when using or including a parameter, it’s important to use the @ symbol. You can also include as many parameters as you want, not just one. This is how we execute stored procedures with parameters.

Stored Procedures In SQL | An Overview - Enterprise DNA (6)

As you can notice, we also used the parameter with a value equal to 1 in executing the stored procedure. This will bring only the information of custid1.

Sample Scenario Of Stored Procedures In SSMS

Let’s have more examples of stored procedures in SQL. In the first instance, I’m going to create a sample stored procedure by using the highlighted command below.

Stored Procedures In SQL | An Overview - Enterprise DNA (7)

For naming convention in creating a stored procedure, we usually use “usp” or “sp” to indicate that this is a stored procedure. If you’re wondering what “usp” means, it simply stands for user-defined stored procedure.

Next, we’re going to write queries for the stored procedure to process when it’s executed.

Stored Procedures In SQL | An Overview - Enterprise DNA (8)

The queries in the previous example will simply remove the table named dbo.stageOrders. Then it will recreate the dbo.stageOrders with the data from the Sales.SalesOrderHeader table.

Let’s create this stored procedure by highlighting the following code and clicking on the Execute button.

Stored Procedures In SQL | An Overview - Enterprise DNA (9)

After that, you should see a message like this.

Stored Procedures In SQL | An Overview - Enterprise DNA (10)

Then we’ll now execute the stored procedure by running the EXEC usp_TEST.

Stored Procedures In SQL | An Overview - Enterprise DNA (11)

Upon the execution of the usp_TEST stored procedure, the dbo.stageOrders should now have the data from Sales.SalesOrderHeader. Let’s check the content of dbo.stageOrders by running the command below.

Stored Procedures In SQL | An Overview - Enterprise DNA (12)

As a result, these are the data that we created in dbo.stageOrders based on the commands we’ve added to our stored procedure.

Stored Procedures In SQL | An Overview - Enterprise DNA (13)

Second Sample Scenario

Let’s move on to another example. This time I don’t want to drop the dbo.stageOrders table and recreate the same table with data coming from the Sales.SalesOrderHeader table.

Instead, I just want the data from Sales.SalesOrderHeader table to be displayed whenever I execute the “usp_TESTstored procedure.

In order to perform this, I need to make changes to the recently created stored procedure by using the ALTER statement.

Stored Procedures In SQL | An Overview - Enterprise DNA (14)

In this example, we utilized the ALTER statement which is used to modify our stored procedure. You can also apply this to views and tables. Once we run the code in the screenshot above, it should change the queries to be processed by our stored procedure.

This time it won’t recreate any table like we did earlier. If we execute “usp_TEST,” it will just bring the data from the Sales.SalesOrderHeader table.

Stored Procedures In SQL | An Overview - Enterprise DNA (15)
Stored Procedures In SQL | An Overview - Enterprise DNA (16)

Third Sample Scenario

Let’s do another example. This time we’ll reuse the dbo.stageOrders table that we had in our first example of creating a stored procedure in SSMS. We’ll start with this example by selecting the dbo.stageOrders table.

Stored Procedures In SQL | An Overview - Enterprise DNA (17)

The results upon selecting all data from dbo.stageOrders should be like this.

Stored Procedures In SQL | An Overview - Enterprise DNA (18)

Now let’s empty the dbo.stageOrders table by executing usp_TEST. We can do that by adding the following command.

Stored Procedures In SQL | An Overview - Enterprise DNA (19)

After adding the TRUNCATE TABLE command, we need to update usp_TEST.

Stored Procedures In SQL | An Overview - Enterprise DNA (20)

After that, a message saying Commands are completed successfully should be displayed. Then we’ll execute usp_TEST again.

Stored Procedures In SQL | An Overview - Enterprise DNA (21)

Upon executing usp_TEST, it will show all the records from Sales.SalesOrderHeader and also empty the dbo.stageOrders table.

Stored Procedures In SQL | An Overview - Enterprise DNA (22)

To check if the dbo.stageOrders table is empty, we need to select it by using the following command and execute.

Stored Procedures In SQL | An Overview - Enterprise DNA (23)

Upon running the code above, we can see the dbo.stageOrders table is now empty. This is due to the TRUNCATE TABLE command that we’ve used to update usp_TEST.

Stored Procedures In SQL | An Overview - Enterprise DNA (24)

Location Of Saved Stored Procedures In SQL

If you want to see where the stored procedure in SQL is being saved, go to the Object Explorer panel on the left side and click the “+” icon before the database that you’re working on.

Stored Procedures In SQL | An Overview - Enterprise DNA (25)

Then right-click on Programmability and select Refresh.

Stored Procedures In SQL | An Overview - Enterprise DNA (26)
Stored Procedures In SQL | An Overview - Enterprise DNA (27)

Expand the Programmability folder or group by clicking on the “+” icon. Then expand the Stored Procedures group by doing the same step. Inside the Stored Procedures group, you should see the dbo.usp_TEST.

Stored Procedures In SQL | An Overview - Enterprise DNA (28)
Stored Procedures In SQL | An Overview - Enterprise DNA (29)

If you want to check what commands or queries a specific stored procedure in SQL is performing, you can right-click on a stored procedure and follow the steps in the screenshot below.

Stored Procedures In SQL | An Overview - Enterprise DNA (30)

After that, it will open the stored procedure in a different tab where you can see the commands provided in it. This is how dbo.usp_TEST looks like upon opening it.

Stored Procedures In SQL | An Overview - Enterprise DNA (31)

As you can see, there are some default commands before the CREATE statement. You can simply delete that if you want to.

Stored Procedures In SQL | An Overview - Enterprise DNA (32)

Now you know how to check what commands a stored procedure in SQL is performing.

Creating A Stored Procedure In SQL With Parameters

Next, we’re going to create a stored procedure with parameters. For instance, we’re going to use the following code to create a new stored procedure.

Stored Procedures In SQL | An Overview - Enterprise DNA (33)

In the example code, I used the same process of creating a stored procedure named usp_GetCustomer. Then I added a parameter which is @CustomerID with an input type of INT.

Take note that once you’ve added a parameter in creating a stored procedure, you should always provide a parameter whenever you’re going to execute a command.

Let’s see what will happen if we execute usp_GetCustomer without providing a parameter.

Stored Procedures In SQL | An Overview - Enterprise DNA (34)

Upon executing usp_GetCustomer without a parameter, an error message came up. This is how it would look if we execute usp_GetCustomer with a parameter.

Stored Procedures In SQL | An Overview - Enterprise DNA (35)

With a parameter provided, we’re able to get a proper result in executing our stored procedure.

Creating A Stored Procedure With A Default Value

If you want to avoid receiving an error message in executing a stored procedure with a parameter, you can set a default value that will serve as a default parameter.

For instance, we’re going to create a stored procedure named usp_GetOrdersByYear.

Then I’m going to add a parameter @OrderYear with input type of “INT” and a default value that is equal to 2011.

Stored Procedures In SQL | An Overview - Enterprise DNA (36)

If we execute usp_GetOrdersByYear without a parameter provided, it will display records with the year 2011.

Stored Procedures In SQL | An Overview - Enterprise DNA (37)
Stored Procedures In SQL | An Overview - Enterprise DNA (38)

On the other hand, if we execute usp_GetOrdersByYear with 2014 as a given parameter, it should display records with the year 2014.

Stored Procedures In SQL | An Overview - Enterprise DNA (39)
Stored Procedures In SQL | An Overview - Enterprise DNA (40)

This is how you utilize stored procedures in your daily data management tasks.

***** Related Links *****
HAVING Clause In SQL Aggregate FunctionsISNULL SQL Function In Case Statements
SQL Data Extraction Using OFFSET And FETCH

Conclusion

Taking everything into account, you’ve learned what a stored procedure in SQL is and its purpose. We’ve also discussed the ALTER statement that is used to make changes or updates in a present stored procedure.

In addition, you’ve learned that there are different methods to create stored procedures in SQL and learned how to avoid receiving errors when executing a stored procedure by providing a default value.

Most importantly, you’ve learned to utilize stored procedures for storing sets of commands to avoid running long sets of code repeatedly. As a final reminder, don’t forget to use the “@” symbol when providing a parameter.

All the best,

Hafiz

***** Learning Power BI? *****
FREE COURSE - Ultimate Beginners Guide To Power BI
FREE COURSE - Ultimate Beginners Guide To DAX
FREE - 60 Page DAX Reference Guide Download
FREE - Power BI Resource
Enterprise DNA On-Demand
Enterprise DNA Platform Access
Enterprise DNA Events

Top Articles

You might also like

Latest Posts

Article information

Author: Trent Wehner

Last Updated: 12/24/2022

Views: 5862

Rating: 4.6 / 5 (76 voted)

Reviews: 83% of readers found this page helpful

Author information

Name: Trent Wehner

Birthday: 1993-03-14

Address: 872 Kevin Squares, New Codyville, AK 01785-0416

Phone: +18698800304764

Job: Senior Farming Developer

Hobby: Paintball, Calligraphy, Hunting, Flying disc, Lapidary, Rafting, Inline skating

Introduction: My name is Trent Wehner, I am a talented, brainy, zealous, light, funny, gleaming, attractive person who loves writing and wants to share my knowledge and understanding with you.