Skip to main content
HomeTutorialsSQL

User Defined Stored Procedures in SQL

Learn how to use and apply the user-defined stored procedure along with examining different types of stored procedures in SQL.
Jul 2019  · 5 min read

You will be using Microsoft SQL server as the database. The concept covered in this tutorial is the same for most of the Relational Database Management System's, but syntaxes are different, although the idea used here serves well for all databases.

Stored Procedure is the block of the SQL statement in Relational Database Management System (RDBMS), and it is typically written by the programmer, Database Administrator, Data Analyst, and is saved and re-used for multiple programs. It can also have different types depending on the various RDBMS. However, the two most crucial Stored Procedures found in any RDBMS are as follows.

  • User-defined Stored Procedure
  • System Stored Procedure

Stored Procedure is a prepared compiled code which is stored or cached and then re-used. You can have cached code for re-use which makes maintainability far easier, i.e., it doesn't need to be changed multiple times, which can maintain security.

Creating a Table

You will now create a table named table_Employees as follows.

CREATE TABLE table_Employees
(
    EmployeeId INT PRIMARY KEY NOT NULL,
    EmployeeFirstName VARCHAR(25) NOT NULL,
    EmployeeLastName VARCHAR(25) NOT NULL,
    EmployeeGender VARCHAR(25) NOT NULL,
    EmployeeDepartmentID INT

)

The table consists of information about employees in a particular company and consists of the following columns:

  1. EmployeeId: It is the primary key which is unique and is integer datatype, where the value gets auto-incremented.
  2. EmployeeFirstName: It is the First Name of an employee, which is of character data type and should not be left empty.
  3. EmployeeLast Name: It is the Last Name of an employee, which is of character data type and should not be left empty.
  4. EmployeeGender: It shows the Gender of the employee and is also a character type.
  5. EmployeeDepartmentId: It is the id of the employee of the integer data type.

You can write a query to insert values into the table. The queries to add values to the table is:

    INSERT INTO <TABLE_NAME>(<COLUMNS_OF_TABLE>) VALUES(<CORRESPONDING_VALUES_TO_MATCH_COLUMN>)

After inserting the values into the table, table_Employees is created.

table_Employees

To Create a User Defined Stored Procedure

Stored Procedure let's you write a query once, and the query has a name which can be saved and executed multiple times if needed. By executing the queries, you can have a folder created as Programmabilty->Stored Procedure, and there is the file named dbo.uprocGetEmployees.

The syntax for creating a Stored Procedure is:

CREATE PROCEDURE <<<procedure_name>>>
AS
BEGIN

   '''Required SQL Queries'''

END

You can create a Stored Procedure by using the SQL statement as follows.

  • CREATE PROCEDURE procedure_name
  • CREATE PROC procedure_name

Also, you need to create a Stored Procedure with a naming convention other than "sp_.". The Stored Procedure in the example is created with the name: uprocGetEmployees.

Stored Procedure example

To Execute the Stored Procedure

To execute a Stored procedure, you can use one of three approaches, and run it as follows.

  • EXEC <>
  • EXECUTE <>
  • <>

In the above program, you can use procedure name uprocGetEmployees and select it to execute the query.

Stored Procedure with Parameters

The Stored Procedure can accept single and multiple parameters. You can easily understand single parameters if you first understand multiple parameters.

The syntax for creating a Stored Procedure with multiple parameters is:

CREATE PROCEDURE <<procedure_name>> <<procedure_parameter>>
AS
    BEGIN
            <<sql_query>>
    END

The example shows that there is a change in the folder below.
EMPLOYEE->Programmability->Stored Procedures->dbo.uprocGetEmployessGenderAndDepartment

Stored Procedures with multiple parameters

The concept here is very similar to the function in general programming languages like Python, Java, etc. The program above has the function or procedure name called as "uprocGetEmployeesGenderAndDepartment", which has the parameter name @EmployeeGender and @EmployeeDepartmentId which are "called" by passing the required value to our procedure which can be done by one of two ways and it is explained below.

The parameters in this program are @EmployeeGender and @EmployeeDepartmentId which are "called" by passing the value to the parameter.

The value can be specified by one of two ways. You can use either one of them to execute the Stored Procedure with the multiple parameters.

  • By specifying the parameter names in the query and passing required values:

Specifying value 1

  • By the position that matches the parameter of Stored Procedure with your query and pass the required value to the parameter:

Specifying value 2

Modifying the Stored Procedure

You can also modify the Stored Procedure by using the ALTER PROCEDURE command.

    ALTER PROCEDURE uprocGetEmployeesGenderAndDepartment
@EmployeeGender nvarchar(25),
@EmployeeDepartmentId int
BEGIN
    SELECT EmployeeFirstName,EmployeeGender,EmployeeDepartmentId FROM table_Employees WHERE
    EmployeeGender = @EmployeeGender AND EmployeeDepartmentId = @EmployeeDepartmentId
END

Deleting Stored Procedure

Stored Procedures can be quickly deleted by using the following commands:

DROP PROC <> DROP PROCEDURE <>

Adding Security Through Encryption

The lock symbol indicated in the picture below to the left shows that Stored Procedure is encrypted which ensures only authorized people can access it.

encrypted Stored Procedure

Conclusion

You have just completed learning the basics of User Stored Procedures and the topics covered are conceptually similar to any RDBMS. You can learn more on DataCamp, by taking their Intermediate SQL Server course. Also, the following Stored Procedures tutorial will serve well in your learning.

SQL Courses

Introduction to SQL

BeginnerSkill Level
2 hr
389.2K
Learn how to create and query relational databases using SQL in just two hours.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

The 80 Top SQL Interview Questions and Answers for Beginners & Intermediate Practitioners

This article provides a comprehensive overview of 80 essential SQL questions and answers for job hunters, hiring managers, and recruiters, covering both general topics and technical questions.

Elena Kosourova

33 min

10 Portfolio-Ready SQL Projects for All Levels

Select your first—or next—SQL project to practice your current SQL skills, develop new ones, and create an outstanding professional portfolio.
Elena Kosourova's photo

Elena Kosourova

11 min

How to Use SQL in pandas Using pandasql Queries

Unleash the power of SQL within pandas and learn when and how to use SQL queries in pandas using the pandasql library for seamless integration.
Elena Kosourova's photo

Elena Kosourova

8 min

How to Practice SQL Using any Dataset with Workspace

Learn how DataCamp Workspace optimizes the experience of working with Jupyter notebooks and SQL. Discover how to effortlessly write SQL queries, connect to databases, analyze CSV files, and leverage the power of AI assistance
Richie Cotton's photo

Richie Cotton

9 min

QUALIFY: The SQL Filtering Statement You Never Knew You Needed

Learn about the SQL QUALIFY clause, an essential yet lesser-known filtering method in SQL. Understand its syntax, uses, and how it differs from other SQL filtering methods.
Kurtis Pykes 's photo

Kurtis Pykes

8 min

MySQL Tutorial: A Comprehensive Guide for Beginners

Discover what MySQL is and how to get started in one of the most popular database management systems.
Javier Canales Luna's photo

Javier Canales Luna

15 min

See MoreSee More