The Stored Procedures in SQL Server are saved collections of one, or group of statements stored in the SQL Server. In this article we will explain you, How to create, rename, Modify, and delete Stored Procedures in SQL Server with examples.
I suggest you to refer following examples to understand the Sql Server Stored procedures in detail:
- SELECT Stored Procedure in SQL Server to understand, How to write the SELECT Statements inside the Stored Procedure
- INSERT Stored Procedure article to understand the steps involved in writing the INSERT Statements inside the Stored Procedure
- UPDATE Stored Procedure to write the UPDATE Statements inside the Stored Procedure
- Input Parameters in Stored Procedures to declare and use the Input Parameters and
- Output Parameters in Stored procedures to output the values
- Return Values in Stored Procedure
Uses of Stored Procedures in SQL Server
Following are benefits of using the SQL Server stored Procedures
- All the Stored Procedures are pre-compiled, and their execution plan is cached. So, when you execute the same Stored Procedures again, then it will used the cache.
- It will help you to encapsulate the business rules and policies. For example, database admin will create a procedure, and multiple users will access the procedure from JAVA, C#, C++, Python, R etc.
- Instead of sending hundreds of lines of code, it is better to use stored procedure. So that we can call the single statement (stored procedure name), rather than writing the complex statement or sending it over the network.
- The SQL server stored procedures helps you to access the database objects in more secured and uniform way
- Network bandwidth conservation (protect from harm or delay)
Before we get into the practical example, let us see the syntax of a stored procedure.
SQL Stored Procedures Syntax
The basic syntax of SQL Server stored procedure is as shown below:
-- Create Stored procedures in SQL Server CREATE [OR ALTER] PROCEDURE [Schema_Name].Procedure_Name @Parameter_Name Data_type, .... @Parameter_Name Data_type AS BEGIN -- Procedure or SQL Query -- SELECT, Insert, Update, Or Delete Statements -- You can Use CTE also END
Let me explain the syntax:
- Schema_name: Please specify the schema name. For example, dbo, or Humanresource etc.
- Procedure_Name: You can specify any name you wish to give other than the system reserved keywords. Please try to use meaningful names so that you can identify them easily.
- @Parameter_Name: Every stored procedure accepts zero or more parameters, it’s completely depends upon the user requirements. While declaring the parameters don’t forget the appropriate data type. For example (@FullName VARCHAR(50), @Age INT)
Create Stored Procedures in SQL Server
We are going to use the below shown data for this demonstration
Forgot to mention, we can create the stored procedure in two ways:
Create SQL Stored Procedures using SQL Server Management Studio
In this example we will show you the steps involved in create a Stored procedure using the Management Studio. Before we start creating any procedure, let us see whether our database had any stored procedures.
In order to view the existing Stored Procedures, Please select the Database -> Programmability -> Stored procedures. From the below screenshot you can observe that, our [SQL Tutorial] database has no procedures.
Right click on the Stored Procedures folder will open the context menu. Please select the New -> Stored Procedure.. option from the context menu as shown below.
Once you click on the New Stored Procedure… option it will open the new Query window with default Stored Procedure Template as we shown below
Here, you can add the Procedure name, Parameters (if required), and the Procedure (or SQL Query) you want to use.
Create SQL Stored Procedures using Query
In this example we will show you, How to create Stored Procedures using the Create Procedure Statement
--Create SQL Stored Procedures USE [SQL Tutorial] GO CREATE PROCEDURE SelectingAllRecordsInEmployee AS BEGIN SET NOCOUNT ON; SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,[HireDate] FROM [Employee] END GO
As you can see that, the command has completed successfully. The following screenshot will show you the Stored Procedure that we created now.
Execute Stored Procedures in SQL Server
We can execute the stored procedure in two ways:
Execute SQL Stored Procedures using SSMS
In this example we will show you, How to execute a Stored Procedure in SQL Server using the SQL Server Management Studio (SSMS).
In order to excute, Please navigate to the Programmability -> Stored Procedures, and then select the Stored Procedure that you want to execute (SelectingAllRecordsInEmployee) and right-click on it will open the context menu. Here, select the Execute Stored Procedure.. option as shown below
Once you select the Execute Stored Procedure.. option, a Execute Procedure window will be opened as shown below. If the procedure has any parameters then we have to assign / pass those values, and then click OK to execute. Here, our stored procedure does not have any parameters so, click OK to execute the procedure
Once you click on the OK button, a new query window will be opened with the following auto generated code
USE [SQL Tutorial] GO DECLARE @return_value int EXEC @return_value = [dbo].[SelectingAllRecordsInEmployee] SELECT 'Return Value' = @return_value GO
Execute Stored Procedures
This example will show you, How to execute a Sql Server Stored Procedure using the EXEC Command (Execute Command)
USE [SQL Tutorial] GO EXEC [dbo].[SelectingAllRecordsInEmployee]
Rename Stored Procedure in SQL Server
There is no such thing called renaming the Stored procedure. This is because, renaming a stored procedure will not change its corresponding object name within the sys.sql_modules. So, if you want to rename any existing stored procedure, then DROP and recreate it with new name.
-- Renaming SQL Stored Procedures USE [SQL Tutorial] GO -- Check whether there is a Stored Procedure with the Following Name IF OBJECT_ID ( 'SelectingAllRecordsInEmployee', 'P' ) IS NOT NULL -- If so, Drop that Procedure DROP PROCEDURE SelectingAllRecordsInEmployee; GO -- Creating Stored Procedure with New name CREATE PROCEDURE SelectingEmployeeRecords AS BEGIN SET NOCOUNT ON; SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,[HireDate] FROM [Employee] END GO
From the below screenshot you can see that, we successfully deleted the old stored procedure, and created the new one.
Modify Stored Procedures in SQL Server
The following examples will help you understand the steps involved in modifying the SQL Stored Procedures using the Microsoft SQL Server Management Studio (SSMS), and Transact-SQL Query.
Modify Stored procedures in SQL Server using SSMS
In order to modify the Stored procedures using the Management Studio, Please navigate to the stored procedure that you want to modify (SelectingEmployeeRecords), and right-click on it will open the context menu. Here, select the Modify option as shown below
Once you select the Modify option, a new query window will be opened with auto generated ALTER PROCEDURE code. You can modify as per your requirement.
Use Query to Modify Stored Procedures in SQL Server
This example will show you, the steps involved in modifying the existing stored procedure using ALTER PROCEDURE statement. To do so, Click New query and return the following query.
USE [SQL Tutorial] GO ALTER PROCEDURE [dbo].[SelectingEmployeeRecords] AS BEGIN SET NOCOUNT ON; SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] AS Profession ,[YearlyIncome] ,[Sales] FROM [Employee] END
Let us execute the procedure to check whether we successfully modifies the procedure or not.
USE [SQL Tutorial] GO EXEC [dbo].[SelectingEmployeeRecords] GO
Delete Stored Procedures in SQL Server
The following examples will show you, How to delete or drop the SQL Stored Procedures using the Transact-SQL Query, and Microsoft SQL Server Management Studio (SSMS).
Delete SQL Stored Procedures using SQL Server Management Studio
In this example we will show you, How to delete a SQL Stored Procedures using the SSMS (Sql Server Management Studio). In order to do so, Please navigate to the Stored Procedure that you want to delete, and right click on the procedure name to open the context menu. You can click on the Delete option as shown below
For this stored procedure demonstration, we are going to delete SelectingEmployeerecords. Once you select the delete option, a Delete Object window will be opened as shown below. Click on the show Dependencies button to check the dependencies, and then click OK to delete the stored procedure.
Delete Stored procedure using Query
In this example we will show you, How to delete stored procedures in Sql Server using a query.
USE [SQL Tutorial] GO IF OBJECT_ID ( 'SelectingEmployeeRecords', 'P' ) IS NOT NULL DROP PROCEDURE SelectingEmployeeRecords; GO
TIP: It is good practice to check, whether the stored procedure exists in database or not using IF OBJECT_ID (N’Stored_Procedure_Name’, ‘P’) IS NOT NULL
SQL Stored Procedures Best Practices
Following are the list of suggestions that might help you to improve the Sql Server stored procedures performance.
- Try to use the Schema Names while you creating, or referencing any database object. It will decrease the database engine processing time.
- Always specify the required column names within the SELECT Statement. And avoid the SELECT * Statement
- While Creating, or altering a table using (CREATE TABLE or ALTER TABLE), use the DEFAULT Keyword to assign the default values to the Columns. This will prevent the NULL values, and assign those default values to the column data.
- When you are creating Temporary Tables inside the stored procedure, You have to explicitly specify whether the Column accepts NULLS, or NOT. It can done by using NULL, or NOT NULL
- Rather than Extracting, or Inserting large amount of data, try to work with less, and essential data. This will reduce the query processing load, and increase the query performance.
- Use the SET NOCOUNT ON statement within the sql server stored procedure. This will turn off the messages that are sent by the SQL Server to the Client. It includes number of rows updated, deleted etc.
- Try to replace the UNION Operator, or OR Operator with the UNION ALL Operator, unless you are looking for distinct values.
- If possible, avoid using the SCALAR Functions in the SELECT Statement that returns large amount of data. This is because, scalar function is applied on each row (row basic), so it will the query performance.
- To handle the errors, SQL allows us to use the TRY CATCH feature inside the Sql Server Stored Procedures. So, try to use the TRY CATCH feature
- Always use the BEGIN..COMMIT TRANSACTION within the Stored Procedure. Remember that, the transaction should be as short as possible, otherwise there is a danger in either deadlock or longer locking.
Best Approach to create Stored Procedures in SQL
This will be ideal way to create a stored procedure
USE [SQL Tutorial] GO IF OBJECT_ID ( 'SelectingEmployeeRecords', 'P' ) IS NOT NULL DROP PROCEDURE SelectingEmployeeRecords; GO CREATE PROCEDURE SelectingEmployeeRecords AS BEGIN SET NOCOUNT ON; SELECT [FirstName] ,[LastName] ,[Education] ,[Occupation] ,[YearlyIncome] ,[Sales] ,[HireDate] FROM [Employee] END GO
Thank You for Visiting Our Blog