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.
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.
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.
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.
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.
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.
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.
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.
After that, you should see a message like this.
Then we’ll now execute the stored procedure by running the EXEC usp_TEST.
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.
As a result, these are the data that we created in dbo.stageOrders based on the commands we’ve added to our stored procedure.
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_TEST” stored procedure.
In order to perform this, I need to make changes to the recently created stored procedure by using the ALTER statement.
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.
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.
The results upon selecting all data from dbo.stageOrders should be like this.
Now let’s empty the dbo.stageOrders table by executing usp_TEST. We can do that by adding the following command.
After adding the TRUNCATE TABLE command, we need to update usp_TEST.
After that, a message saying Commands are completed successfully should be displayed. Then we’ll execute usp_TEST again.
Upon executing usp_TEST, it will show all the records from Sales.SalesOrderHeader and also empty the dbo.stageOrders table.
To check if the dbo.stageOrders table is empty, we need to select it by using the following command and execute.
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.
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.
Then right-click on Programmability and select Refresh.
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.
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.
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.
As you can see, there are some default commands before the CREATE statement. You can simply delete that if you want to.
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.
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.
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.
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.
If we execute usp_GetOrdersByYear without a parameter provided, it will display records with the year 2011.
On the other hand, if we execute usp_GetOrdersByYear with 2014 as a given parameter, it should display records with the year 2014.
This is how you utilize stored procedures in your daily data management tasks.
***** Related Links *****
HAVING Clause In SQL Aggregate Functions
ISNULL 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
