Introduction to Stored Procedures in SQL Server

The SQL Server Stored Procedures saves a collection of one or group of statements stored in the SQL Server. Here, we explain to you How to create, rename, Modify, and delete Stored Procedures in SQL Server with examples. The following are benefits of using the SQL Server stored Procedures

  • All the SQL Server Stored Procedures are pre-compiled, and their execution plan is cached. So, when you execute the same Stored Procedures again, then it will use 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#PythonR, etc.
  • Instead of sending hundreds of lines of code, it is better to use a 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 a more secure and uniform way
  • Network bandwidth conservation (protect from harm or delay)

I suggest you refer the following examples to understand the SQL Server Stored procedures in detail:

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 quickly.
  • @Parameter_Name: Every stored procedure accepts zero or more parameters, 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

The data that we use for this Stored Procedures demonstration : Data 1

Forgot to mention, we can create the stored procedure in two ways:

Create SQL Stored Procedures using SQL Server Management Studio

Before we start creating any procedure, let us see whether our database had any stored procedures. 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 stored procedures.

Right-click on the Stored Procedures folder will open the context menu. Please select the New -> Stored Procedure..option from it.

Stored Procedures in SQL Server 1

Once you click on the New Stored Procedure… option it will open the new Query window with default Stored Procedure Template

Stored Procedures in SQL Server 2

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 show how to create Stored Procedures using the Create Procedure Statement

--Create SQL Stored Procedures
CREATE PROCEDURE SelectingAllRecordsInEmployee 
AS
BEGIN

 SET NOCOUNT ON;
 SELECT [FirstName]
              ,[LastName]
              ,[Education]
              ,[Occupation]
              ,[YearlyIncome]
              ,[Sales]
              ,[HireDate]
 FROM [Employee]
END
GO

SQL Message Output

Messages
--------
Command(s) completed successfully.

As you can see that, the command has completed successfully. The following screenshot shows the Stored Procedure that we created earlier.

Stored Procedures in SQL Server 4

Execute Stored Procedures in SQL Server

We can execute the stored procedure in two ways:

Execute SQL Stored Procedures using SSMS

To execute SQL Server Stored Procedure using the Management Studio (SSMS), Please navigate to the Programmability -> Stored Procedures. Next, select the Stored Procedure that you want to execute (SelectingAllRecordsInEmployee), and right-click on it will open the context menu. Please, choose the Execute Stored Procedure.. option

Stored Procedures in SQL Server 5

Once you select the Execute Stored Procedure.. option, an Execute Procedure window will open. If the procedure has any parameters, we have to assign/pass those values and click OK to execute. Here, our stored procedure does not have any parameters so, click OK to execute the procedure

Stored Procedures in SQL Server 6

Once you click on the OK button, a new query window opened with the following auto-generated code

DECLARE @return_value int

EXEC @return_value = [dbo].[SelectingAllRecordsInEmployee]

SELECT 'Return Value' = @return_value

GO
Stored Procedures in SQL Server 7

Execute Stored Procedures

Execute SQL Server Stored Procedure using the EXEC Command (Execute Command)

EXEC  [dbo].[SelectingAllRecordsInEmployee]
Stored Procedures in SQL Server 8

Rename Stored Procedure in SQL Server

There is no such thing called renaming the Stored procedure. 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 a new name.

-- Renaming SQL Stored Procedures
-- 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

SQL Message output

Messages
-------
Command(s) completed successfully.

From the below screenshot, see we successfully deleted the old stored procedure, and created the new one.

Stored Procedures in SQL Server 10

Modify Stored Procedures in SQL Server

The following examples help you learn the steps to modify the SQL Stored Procedures using the Management Studio (SSMS) and query.

Modify Stored procedures in SQL Server using SSMS

To modify the Stored procedure using the Management Studio, right-click on the stored procedure (SelectingEmployeeRecords) that you want to modify, and select the Modify option

Stored Procedures in SQL Server 11

Once you choose the Modify option, a new query window opened with auto-generated ALTER PROCEDURE code. You can change as per your requirement.

Stored Procedures in SQL Server 12

Use Alter Procedure to Modify Stored Procedures in SQL Server

This example will show you the steps involved in modifying the existing stored procedure using the ALTER PROCEDURE statement. To do so, Click New query and return the following query.

ALTER PROCEDURE [dbo].[SelectingEmployeeRecords] 
AS
BEGIN
 SET NOCOUNT ON;
 SELECT [FirstName]
              ,[LastName]
              ,[Education]
              ,[Occupation] AS Profession
              ,[YearlyIncome]
             ,[Sales]
 FROM [Employee]
END

Run the above alter stored procedure query

Messages
--------
Command(s) completed successfully.

Let us execute the procedure to check whether we successfully modify the procedure or not.

EXEC [dbo].[SelectingEmployeeRecords]
GO
Stored Procedures in SQL Server 14

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 how to delete SQL Stored Procedures using the SSMS (SQL Server Management Studio). To do so, Please navigate to the Stored Procedure that you want to delete. Next, right-click on the procedure name to open the context menu. You can click on the Delete option as shown below

Stored Procedures in SQL Server 15

For this stored procedure demonstration, we are going to delete SelectingEmployeerecords. Once you select the delete option, a Delete Object window will open as shown below. Click on the Show Dependencies button to check the dependencies, and then click OK to delete the stored procedure.

Stored Procedures in SQL Server 16

Delete Stored procedure using Drop Procedure

In this example, we show you how to delete stored procedures in SQL Server using a query.

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 the database or not using IF OBJECT_ID (N’Stored_Procedure_Name’, ‘P’) IS NOT NULL

SQL Stored Procedures Best Practices

The following is a list of suggestions that might help you to improve the SQL Server stored procedures performance.

  • Try to use the Schema Names while you were 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. It 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 specify whether the Column accepts NULLS, or NOT explicitly. It can be done by using NULL, or NOT NULL
  • Rather than Extracting, or Inserting a large amount of data, try to work with less and essential data. It reduces the query processing load, and increase the query performance.
  • Use the SET NOCOUNT ON statement within the SQL server stored procedure. It will turn off the messages that are sent by the SQL Server to the Client. It includes the 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 a large amount of data. It is because the scalar function applied on each row (row basic), so it will affect 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

It will be an ideal way to create a stored procedure

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

Comments are closed.