Stored Procedure in SQL – Types, Features, Syntax, & More

About 7 million people are utilizing Structured Query Language (SQL) and its database management capabilities. The crucial feature that many developers overlook is the stored procedures in SQL. In this blog, we will explore the benefits and applications associated with this powerful tool. This should help SQL users to unlock greater potential for enhanced efficiency, security, and maintainability while managing databases.

What is a Stored Procedure in SQL?

A stored procedure is simply a saved set of instructions, written in SQL language. This means that instead of having to rewrite the same code each time you want it to run, you can save it as a ‘stored procedure’ and just call on this instruction whenever needed. You can also add parameters that customize how the query works based on what value(s) are passed into them.

In other words, a stored procedure is a group of one or more pre-programmed SQL statements saved in the database. It has a name, input parameters, and Transact-SQL code that performs some kind of action. When it’s called for the first time, an execution plan is created by the Database Server which stores them in memory so they can be reused quickly when needed again later on. 

This helps speed up performance as well as being able to use different types of languages including Java, Python, and PHP to invoke these procedures from triggers or other applications. You can have a clear understanding of this subject matter from an online SQL course. 

Types of Stored Procedures in SQL

Here are some of the common types of stored procedures:

1. System-Defined Stored Procedure: These procedures are predefined to perform certain actions in the SQL Server database. They begin with the sp_ prefix and exist within the Resource Database. 
2. Extended Procedure: This type of procedure provides an interface to external programs for various maintenance activities, starting with xp_ as its prefix located in Master Data Base.                 
3. User-Defined Stored Procedures: These processes are created by users themselves based on their parameters and logic presented inside a user or system-defined database without any naming restrictions applied
4. CLR Stored Procedure: This is a special type of stored procedure that utilizes Microsoft’s Common Language Runtime (CLR) and can be created with languages like C#, Visual Basic, or F#. It uses the integrated development environment of Visual Studio since 2008 for execution in an MSQL server database management system.

Features of Stored Procedures in SQL Server

Here are some easy-to-understand features of stored procedures in SQL Server:

1.  Reduced Network Traffic: Using a stored procedure means you only need to send the name and parameters instead of multiple queries, so it reduces network traffic.
2. Better Security: Stored procedures manage what processes we can use securely, without needing special levels of permission at each step.
3. Reusability of Your Code: With saved pieces called “stored procedures,” you don’t have to rewrite them every time – they’re ready for reuse.
4. Maintain Easily: Updating or making changes is relatively simple because maintenance doesn’t always require restarting or deploying applications.
5. Get Faster Results: Because execution plans are created quickly after compiling upon the first usage; performing repeatedly is treated more speedily.

SQL Stored Procedure Syntax

The following illustrates the basic syntax of creating a procedure in SQL:

CREATE or REPLACE PROCEDURE procedure_name(parameters)
AS
-- declare variables
BEGIN
-- statements
END;

The syntax requires you to pay attention to the parameters, which come in three varieties: IN, OUT, and IN OUT. 

• Parameters labeled “IN” accept input values from a program by default. 
• Those labeled “OUT” send output values back to the program after processing has been completed on them. 
• Finally, parameters marked “IN-OUT” act as both input and output simultaneously; they can receive data while also returning results afterward.

Example & How to Create a Stored Procedure in SQL

Let’s take an example to illustrate how the above syntax works. For this purpose, we will consider a hypothetical scenario that involves two tables: Employees and Salaries.

To begin with, execute the following command for creating the Employees table:

CREATE TABLE Employee(
EmployeeID INT,
EmployeeName VARCHAR(100),
Salary INT
);

INSERT INTO Employee VALUES (101, 'John Doe', 50000);
INSERT INTO Employee VALUES (201, 'Jane Smith', 60000);
INSERT INTO Employee VALUES (301, 'Michael Johnson', 70000);
INSERT INTO Employee VALUES (401, 'Emily Davis', 55000);
INSERT INTO Employee VALUES (501, 'Robert Wilson', 65000);

SELECT * FROM Employee;

Now, let’s create a second table called “EmployeeDescription”.

CREATE TABLE EmployeeDescription(
EmployeeID INT,
Description VARCHAR(800)
);

INSERT INTO EmployeeDescription VALUES (101,'Experienced employee with a high level of expertise');
INSERT INTO EmployeeDescription VALUES (201,'Skilled employee with advanced technical knowledge');
INSERT INTO EmployeeDescription VALUES (301,'Highly specialized employee in a specific field');
INSERT INTO EmployeeDescription VALUES (401,'Employee with strong leadership and management abilities');
INSERT INTO EmployeeDescription VALUES (501,'Motivated employee with excellent problem-solving skills');

SELECT * FROM EmployeeDescription;

Let’s move on to designing a SQL stored procedure that combines the data from “Employee” and “EmployeeDescription” tables for retrieving employee descriptions.

CREATE PROCEDURE GetEmployeeDescriptions
AS
BEGIN
  SET NOCOUNT ON
  SELECT e.EmployeeID, e.EmployeeName, e.Salary, ed.Description
  FROM Employee e
  JOIN EmployeeDescription ed ON e.EmployeeID = ed.EmployeeID;
END;

Once you’ve created the stored procedure, a message reading “command(s) executed successfully” will appear in Microsoft SQL Server Management Studio. Now that it’s been created, the next step is to run it by using this syntax: EXEC procedure_name 

Now let’s proceed with running the newly-created stored procedure.

EXEC GetEmployeeDescriptions;

When you run this command, it will invoke the stored procedure titled “GetEmployeeDesc,” retrieving a result set that includes information on each employee’s ID number, name, and corresponding descriptions.

This is what the full code should look like in an SQL Server:

CREATE TABLE Employee (
  EmployeeID INT,
  EmployeeName VARCHAR(100),
  Salary INT
);

INSERT INTO Employee VALUES (101, 'John Doe', 50000);
INSERT INTO Employee VALUES (201, 'Jane Smith', 60000);
INSERT INTO Employee VALUES (301, 'Michael Johnson', 70000);
INSERT INTO Employee VALUES (401, 'Emily Davis', 55000);
INSERT INTO Employee VALUES (501, 'Robert Wilson', 65000);

SELECT * FROM Employee;

CREATE TABLE EmployeeDescription (
  EmployeeID INT,
  Description VARCHAR(800)
);

INSERT INTO EmployeeDescription VALUES (101, 'Experienced employee with a high level of expertise');
INSERT INTO EmployeeDescription VALUES (201, 'Skilled employee with advanced technical knowledge');
INSERT INTO EmployeeDescription VALUES (301, 'Highly specialized employee in a specific field');
INSERT INTO EmployeeDescription VALUES (401, 'Employee with strong leadership and management abilities');
INSERT INTO EmployeeDescription VALUES (501, 'Motivated employee with excellent problem-solving skills');

SELECT * FROM EmployeeDescription;

GO

CREATE PROCEDURE GetEmployeeDescriptions
AS
BEGIN
  SET NOCOUNT ON
  SELECT e.EmployeeID, e.EmployeeName, e.Salary, ed.Description
  FROM Employee e
  JOIN EmployeeDescription ed ON e.EmployeeID = ed.EmployeeID;
END;

GO

EXEC GetEmployeeDescriptions;

Creating a Stored Procedure with Parameters

Now, if you do not just want to create a stored procedure but want to create a stored procedure with parameters, here’s a sample code. Note that this procedure with parameters replaces the other procedure already available. 

GO 

CREATE PROCEDURE GetEmployeeDesc_Para
  @EmployeeID INT
AS
BEGIN
  SET NOCOUNT ON;
  SELECT e.EmployeeID, e.EmployeeName, ed.Description
  FROM Employee e
  INNER JOIN EmployeeDescription ed ON e.EmployeeID = ed.EmployeeID
  WHERE e.EmployeeID = @EmployeeID;
END;

GO 
EXEC GetEmployeeDesc_Para @EmployeeID = 201;

The stored procedure code provided uses table aliases “e” and “ed” in the SQL query to refer to tables. These are abbreviated names assigned for convenience, making the query more readable and concise. Their meanings are as follows:

• “E” stands for the “Employee” table; hence it represents columns of that particular entity within this perfective.
•  “Ed” is used instead of the full naming “EmployeeDescription”.

Table aliases come in handy when constructing queries involving complex joins or multiple tables since they help one write shorter yet understandable codes with ease of readability scoring through human-friendly language intuition incorporation into programming functionalities.

Creating a Stored Procedure in SQL with Default Parameters

Another option is to establish a stored procedure with preset parameters. By doing this, you can specify the parameter value at the time of creation and it will use that default value each time you run the procedure unless another value is provided by the user.

 In such cases, it disregards the default configuration and returns results based on user-provided input instead.

-- Create the stored procedure
CREATE PROCEDURE GetEmployeeDesc_DefPara
  @EmployeeID INT = 301
AS
BEGIN
  SET NOCOUNT ON;
  SELECT e.EmployeeID, e.EmployeeName, ed.Description
  FROM Employee e
  INNER JOIN EmployeeDescription ed ON e.EmployeeID = ed.EmployeeID
  WHERE e.EmployeeID = @EmployeeID;
END;

GO

-- Execute the stored procedure without passing any external parameter
EXEC GetEmployeeDesc_DefPara;

GO

-- Execute the stored procedure with an argument (employee ID 201)
EXEC GetEmployeeDesc_DefPara @EmployeeID = 201;

Creating a Stored Procedure in SQL With an Output Parameter

By using an output parameter in SQL, you can create a stored procedure that returns information after it has been executed. This is useful when working with employee-related data and needing to extract specific results, such as the total number of employees within a department or identifying the highest salary among them.

An instance where an output parameter may be employed involves having an Employee table consisting of columns showing details like their name and salary range. To illustrate this concept further, let’s say we require creating a stored procedure for obtaining how many employees fall into any given unique salary bracket:

-- Create a stored procedure with an output parameter
CREATE PROCEDURE GetEmployeeDescriptionWithOutput
  @EmployeeID INT,
  @Description VARCHAR(800) OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  SELECT @Description = ed.Description
  FROM Employee e
  JOIN EmployeeDescription ed ON e.EmployeeID = ed.EmployeeID
  WHERE e.EmployeeID = @EmployeeID;
END;

GO

-- Declare a variable to store the output parameter value
DECLARE @EmployeeDescription VARCHAR(800);

-- Execute the stored procedure and retrieve the output parameter value
EXEC GetEmployeeDescriptionWithOutput @EmployeeID = 201, @Description = @EmployeeDescription OUTPUT;

-- Display the output parameter value
SELECT @EmployeeDescription;

Conclusion

Employing stored procedures in SQL is a great way to store and reuse commands. The advantages of using this approach are evident – there will be reduced traffic on the network, greater security for your data, reusing code gets easier than ever before plus you get better results more quickly. By taking advantage of the syntax provided as well as considering the examples shown here one can create procedures with or without parameters.