Demystifying Stored Procedures in SQL and PostgreSQL: A Comprehensive Guide

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.

SQL, or Structured Query Language, is commonly used for manipulating data in databases. One common feature of SQL is stored procedures. Stored procedures can greatly improve the efficiency, security, and functionality of your database.

Enhance your SQL performance using Stored Procedures, which are crafted to simplify tasks and improve reusability.

Procedures

Procedures are the subprograms which can be created and saved in the database as database objects. Just as you can in other languages, you can create and drop procedures in SQL as well. In this blog, let’s explore procedures in SQL with syntax and examples.

Diagram depicting how an application interacts with an SQL database through stored procedures and different query languages to manage data effectively.

What is a procedure in SQL?

A procedure in SQL (often referred to as stored procedure), is a reusable unit that encapsulates the specific business logic of the application. A SQL procedure is a  group of SQL statements and logic, compiled and stored together to perform a specific task.

Listed below are key features of the SQL procedures:

• Easy to implement because they use a very simple high-level, strongly-typed language

• Supports three types of parameters, namely, input, output, and input-output parameters.

• More reliable than equivalent external procedures.

• SQL procedures promote reusability and maintainability.

• Supports a simple, but powerful condition and error-handling model.

• Return a status value to a calling procedure or batch to indicate success or failure and the reason for failure.

Syntax of procedures in SQL

CREATE [ OR REPLACE] PROCEDURE procedure_name [

(parameter_name [IN | OUT | IN OUT]  type [ ])]

{IS | AS }

BEGIN [declaration_section]

executable_section

//SQL statement used in the stored procedure

END

GO

Syntax Terminologies

Parameter

A parameter is a variable that holds a value of any valid SQL datatype through which the subprogram can 1`exchange the values with the main code. In other words, parameters are used to pass values to the procedure. There are 3 different types of parameters, which are as follows:

• IN: This is the Default Parameter, which always receives the values from the calling program. It is a read-only variable inside the subprograms and its value cannot be changed inside the subprogram.

• OUT: It is used for getting output from the subprograms.

• IN OUT: This parameter is used for both giving input and for getting output from the subprograms.

Other Terminologies

• procedure-name specifies the name of the procedure. It should be unique.

• [OR REPLACE] option allows the modification of an existing procedure.

• IS | AS Clause, they set the context to execute the stored procedure.  The difference is, the keyword ‘IS’ is used when the procedure is nested into some other blocks and if the procedure is standalone then ‘AS’ is used.

• Code_Block declares the procedural statements that handle all processing within the stored procedure. The content of the code_block depends on the rules and procedural language used by the database.

Example:

CREATE PROCEDURE welcome_msg

(@para1_name VARCHAR(30))

AS

BEGIN

    print ('Hello World! ' + @para1_name);

END;

Execute the stored procedure. A standalone procedure can be called in two ways −

• Using the EXECUTE keyword

• Calling the name of the procedure from a SQL block

The above procedure can be called using Execute keyword as follows:

EXEC welcome_msg 

@para1_name = N'Welcome to Numpy Ninja'

GO

OUTPUT:

Introduction to Stored Procedures

An illustration of how applications use stored procedures, which include SQL statements such as SELECT, INSERT, UPDATE, and DELETE, to interact with an RDBMS database, enabling task execution and procedure reuse.

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. 

SQL Stored Procedure Syntax

CREATE or REPLACE PROCEDURE procedure_name(parameters)

AS

-- declare variables

BEGIN

-- statements

END;

Key Terms:

• CREATE PROCEDURE: This keyword creates the stored procedure with the given name.

• @parameters: These are input parameters that allow you to pass values into the stored procedure.

• BEGIN...END: These keywords define the block of SQL statements that make up the procedure body.

SQL Stored Procedures are a powerful feature in database management systems (DBMS) that allow developers to encapsulate SQL code and business logic. 

Types of SQL Stored Procedures

SQL stored procedures are categorized into different types based on their use case and functionality:

1. System Stored Procedures: These are predefined stored procedures provided by the SQL Server for performing administrative tasks. Examples include sp_help for viewing database object information and sp_rename for renaming database objects.

2. User-Defined Stored Procedures: These are custom stored procedures created by the user to perform specific operations. For example, creating a procedure that calculates the total sales for a particular product category.

3. Extended Stored Procedures: These allow for the execution of external functions, which might be implemented in other languages such as C or C++. Extended procedures are typically used for integrating external tools into SQL Server.

4. CLR Stored Procedures: These are stored procedures written in .NET languages (like C#) and executed within SQL Server. CLR stored procedures are useful when advanced functionality is needed that isn’t easily achievable with T-SQL alone

How stored procedures work

Stored procedures are executed on the server side, which means that they can perform operations much faster than if you were to send multiple queries from the client side. This also reduces network traffic, as only the result of the stored procedure is returned instead of sending back each query individually.

Role in database management

Stored procedures play a crucial role in database management by centralizing logic within the database itself. This means that critical operations are performed consistently, securely, and efficiently. They help to:

• Maintain data integrity.

• Enforce business rules.

• Reduce the complexity of database interactions.

  
  

Common commands used with Stored Procedures

Now let’s look at useful commands that pair with stored procedures.

CREATE PROCEDURE

This command is used to define a new stored procedure in the database.

Here's an example of a stored procedure using this function:

Let's say we have a table called Employees with the following columns:

• EmployeeID

• FirstName

• LastName

• DepartmentID

• Salary

CREATE PROCEDURE GetEmployeesByDepartment

   @DepartmentID INT

AS

BEGIN

   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary

   FROM Employees

   WHERE DepartmentID = @DepartmentID;

END;

EXEC

This command is used to execute a stored procedure. It can also be used to pass input and output parameters.

For our previous example, the `EXEC` command would look like this:

EXEC GetEmployeesByDepartment @DepartmentID = 1;

ALTER PROCEDURE

This command allows you to make changes to an existing stored procedure without deleting and recreating it.

Continuing with the previous example, let's say we want to modify the `GetEmployeesByDepartment` stored procedure to include an additional filter for salary. Specifically, we want to retrieve employees from a specific department who earn above a specified salary.

Here's an example:

ALTER PROCEDURE GetEmployeesByDepartments

   @DepartmentID INT,

   @MinSalary DECIMAL(10, 2)

AS

BEGIN

   SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary

   FROM Employees

   WHERE DepartmentID = @DepartmentID AND Salary > @MinSalary;

END;

DROP PROCEDURE

If you no longer need a stored procedure, you can use this command to delete it from the database.

Here's an example: To delete the stored procedure, use the DROP PROCEDURE command followed by the name of the stored procedure.

DROP PROCEDURE GetEmployeesByDepartment

This drops the stored procedure that we created previously.

Example & How to Create a Stored Procedure in SQL

As part of the example, I am creating sample tables.

CREATE TABLE Products

(ProductID INT, ProductName VARCHAR(100) )

INSERT INTO Products VALUES (680,'HL Road Frame - Black, 59')

,(706,'HL Road Frame - Red, 59')

,(707,'Sport-100 Helmet, Red')

SELECT * FROM Products

CREATE TABLE ProductDescriptions

(ProductID INT, ProductDescription VARCHAR(801) )

INSERT INTO ProductDescriptions VALUES (680,'Replacement mountain wheel for entry-level rider.')

,(706,'Sturdy alloy features a quick-release hub.')

,(707,'Aerodynamic rims for smooth riding.')

SELECT * FROM ProductDescriptions

OUTPUT:

Creating a Simple Stored Procedure

This example shows how to create a stored procedure that joins two tables and returns the results.

CREATE PROCEDURE GetProductsDesc

AS

BEGIN

SET NOCOUNT ON

SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM 

Products P

INNER JOIN ProductDescriptions PD ON P.ProductID=PD.ProductID

END

To execute stored procedures, we can use 'EXEC ProcedureName'. Using the procedure GetProductsDesc, we get the following results.

OUTPUT:

Creating a Stored Procedure with Parameters

In this example, we will develop a stored procedure that accepts input parameters and processes records based on those input parameters.

In the following example, the parameter is accepted by a stored procedure.

CREATE PROCEDURE GetProductsDesc_withparameters

(@PID INT)

AS

BEGIN

SET NOCOUNT ON

SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM 

Products P

INNER JOIN ProductsDescription PD ON P.ProductID=PD.ProductID

WHERE P.ProductID=@PID

END

EXEC GetProductDesc_withparameters 706

The input parameter must be passed to the stored procedure while it is being executed. The result set can be seen in the image below.

OUTPUT:

Creating a Stored Procedure with Output Parameter

In the example below, there is an output parameter for a stored procedure. When an employee is inserted, the EmpID column is an auto-identification column.

CREATE TABLE Employee (EmpID int identity(1,1),EmpName varchar(500))

CREATE PROCEDURE ins_NewEmp_with_outputparamaters

(@Ename varchar(50),

@EId int output)

AS

BEGIN

SET NOCOUNT ON

INSERT INTO Employee (EmpName) VALUES (@Ename)

SELECT @EId= SCOPE_IDENTITY()

END

Using output parameters to execute stored procedures is a bit different. In order to store the value returned by the output parameter, we need to declare a variable.

declare @EmpID INT

EXEC ins_NewEmp_with_outputparamaters 'Andrew', @EmpID OUTPUT

SELECT @EmpID

OUTPUT:

Creating a Temporary Procedure

Temporary procedures can also be created, just like temporary tables. Temporary procedures come in two types, a local temporary stored procedure and a global temporary stored procedure.

In the tempdb database, these procedures are created.

Local temporary Sql Server stored procedures: They are created with # as prefixes and are accessible only in the session where they were created. Closing the connection automatically drops this procedure.

A local temporary procedure is shown in the following example.

CREATE PROCEDURE #Temp

AS b

BEGIN

PRINT 'Local temp procedure'

END

Global temporary SQL Server stored procedure: In addition to being accessed on other sessions, these procedures are created with ## as a prefix. Upon closing the connection used to create this procedure, the procedure is automatically dropped.

A global temporary procedure is shown in the following example

CREATE PROCEDURE ##TEMP

AS

BEGIN

PRINT 'Global temp procedure'

END

Modifying the Stored Procedure

Modify an existing stored procedure with the ALTER PROCEDURE statement. The following example shows how this can be done.

ALTER PROCEDURE GetProductDesc

AS

BEGIN

SET NOCOUNT ON

SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM 

Product P

INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID

END

Renaming the Stored Procedure

The system stored procedure sp_rename can be used to rename a stored procedure using T-SQL. Here is an example of renaming the procedure "GetProductDesc" to "GetProductDesc_new". 

sp_rename 'GetProductDesc','GetProductDesc_new'

OUTPUT:

POSTGRESQL

PostgreSQL allows the users to extend the database functionality with the help of user-defined functions and stored procedures through various procedural language elements, which are often referred to as stored procedures. The store procedures define functions for creating triggers or custom aggregate functions.

PostgreSQL Stored Procedures

Stored procedures provide a means to execute multiple SQL statements in a structured and reusable way. They introduce advanced control structures and allow us to perform complex calculations, making it easier to build complex applications directly within the database. By default, PostgreSQL supports three main procedural languages: SQL, PL/pgSQL, and C. We can also add other languages, such as Perl, Python, and TCL, through extensions.

PostgreSQL divides procedural languages into two main categories:

1. Safe Languages: These can be used by any user and include languages like SQL and PL/pgSQL.

2. Sandboxed Languages: These are restricted to superusers because they can bypass security measures and access external resources. An example of a sandboxed language is C.

Advantages of using PostgreSQL stored procedures

• Reduced network traffic: Stored procedures execute on the database server, so the application sends a single function call instead of multiple SQL statements, reducing the number of database round trips.

• Increased performance: Increase application performance because the user-defined functions and stored procedures are pre-compiled and stored in the PostgreSQL database server.

• Reusability: You can reuse stored procedures across multiple applications, making database management more efficient.

• Centralized logic: With stored procedures, logic is centralized on the database server, which reduces the redundancy of code in different applications.

Disadvantages of using PostgreSQL stored procedures:

• Specialized skills required: Slowness in software development because stored procedure programming requires specialized skills that many developers do not possess.

• Version management: Difficult to manage versions and hard to debug.

• Portability issues: May not be portable to other database management systems e.g., MySQL or Microsoft SQL Server.

What are the data types in stored procedure PostgreSQL?

PostgreSQL stored procedures support a wide range of data types, including INTEGER, VARCHAR, TEXT, BOOLEAN, DATE, TIMESTAMP, and custom types, allowing flexibility in handling data.

Introduction to PostgreSQL CREATE PROCEDURE statement

• A drawback of user-defined functions is that they cannot execute transactions. In other words, inside a user-defined function, you cannot start a transaction, and commit or rollback it.

• PostgreSQL 11 introduced stored procedures that support transactions.

• To define a new stored procedure, you use the create procedure statement with the following syntax

  
  

 CREATE [OR REPLACE] PROCEDURE procedure_name(parameter_list)

LANGUAGE plpgsql

AS $$

DECLARE

   -- Variable declarations (optional)

BEGIN

   -- Procedure body (SQL statements)

END;

$$;

Parameters:

• procedure_name: The name of the stored procedure.

• parameter_list: The parameters for the stored procedure, which can have IN and INOUT modes. OUT mode is not supported.

• LANGUAGE plpgsql: Specifies the procedural language. Other languages like SQL and C can also be used.

• $$: Dollar-quoted string constant syntax to define the body of the stored procedure.

  
  

A stored procedure does not return a value. You cannot use the return statement with a value inside a stored procedure like this

return expression;

However, you can use the return statement without the expression to stop the stored procedure immediately:

return;

If you want to return a value from a stored procedure, you can use parameters with the inout mode.

PostgreSQL CREATE PROCEDURE statement examples

We’ll create a simple stored procedure that transfers funds from one account to another in a banking system.

Step 1: Create an Accounts Table

We will use the following accounts table for the demonstration: First, create a table called accounts with columns for id, name, and balance. This table will serve as the foundation for our stored procedure.

Query:

create table accounts (

    id int generated by default as identity,

    name varchar(100) not null,

    balance dec(15,2) not null,

    primary key(id)

);

insert into accounts(name,balance)

values('Bob',10000);

insert into accounts(name,balance)

values('Alice',10000);

The following query will show the table data:

SELECT * FROM accounts

OUTPUT:

Step 2: Create a Stored Procedure to Transfer Funds

The following example creates a stored procedure named transfer that transfers a specified amount of money from one account to another.

create or replace procedure transfer(

   sender int,

   receiver int,

   amount dec

)

language plpgsql

as $$

begin

    -- subtracting the amount from the sender's account

    update accounts

    set balance = balance - amount

    where id = sender;

    -- adding the amount to the receiver's account

    update accounts

    set balance = balance + amount

    where id = receiver;

    commit;

end;$$;

Step 3: Call the Stored Procedure

To call a stored procedure, you use the CALL statement as follows:

call stored_procedure_name(argument_list);

For example, this statement invokes the transfer stored procedure to transfer $1,000 from Bob’s account to Alice’s account.

call transfer(1,2,1000);

Step 4: Verify the Data

The following statement verifies the data in the accounts table after the transfer:

SELECT * FROM accounts;

Output:

The output shows that the transfer has been successful.

Summary:

• Use create procedure statement to define a new stored procedure.

• Use the call statement to invoke a stored procedure.

  
  

Introduction to PostgreSQL DROP PROCEDURE statement

The drop procedure statement deletes one or more stored procedures from a database.

The following illustrates the syntax of the drop procedure statement:

drop procedure [if exists] procedure_name (argument_list)

[cascade | restrict]

In this syntax:

1. First, specify the name (procedure_name) of the stored procedure that you want to remove after the drop procedure keywords.

2. Second, use the if exists option if you want PostgreSQL to issue a notice instead of an error if you drop a stored procedure that does not exist.

3. Third, specify the argument list of the stored procedure if the stored procedure’s name is not unique in the database. Note that stored procedures that have different argument lists can share the same name. PostgreSQL needs the argument list to determine which stored procedure that you want to remove.

4. Finally, use the cascade option to drop a stored procedure and its dependent objects, the objects that depend on those objects, and so on. The default option is restrict that will reject the removal of the stored procedure in case it has any dependent objects.

To drop multiple stored procedures, you specify a comma-separated list of stored procedure names after the drop procedure keyword like this:

drop procedure [if exists] name1, name2, ...;

Creating sample stored procedures

Let’s create a couple of stored procedures that manage actors so that you can learn how to drop them:

The following insert_actor() stored procedure inserts a new row into the actor table. It accepts two arguments which are the first name and last name of the actor.

create or replace procedure insert_actor(

fname varchar,

lname varchar)

language plpgsql

as $$

begin

insert into actor(first_name, last_name)

values('John','Doe');

end;

$$;

OUTPUT:

The following insert_actor stored procedure also inserts a row into the actor table. However, it accepts one argument which is the full name of the actor. The insert_actor() uses the split_part() function to split the full name into first name and last name before inserting them into the actor table.

create or replace procedure insert_actor(

full_name varchar

)

language plpgsql

as $$

declare

fname varchar;

lname varchar;

begin

-- split the fullname into first & last name

select

split_part(full_name,' ', 1),

split_part(full_name,' ', 2)

into fname,

     lname;

-- insert first & last name into the actor table

insert into actor(first_name, last_name)

values(fname, lname);

end;

$$;

The following stored procedure deletes an actor by id:

create or replace procedure delete_actor(

p_actor_id int

)

language plpgsql

as $$

begin

delete from actor

where actor_id = p_actor_id;

end;

$$;

OUTPUT:

The following stored procedure updates the first name and last name of an actor:

create or replace procedure update_actor(

p_actor_id int,

fname varchar,

lname varchar

)

language plpgsql

as $$

begin

update actor

set first_name = fname,

    last_name = lname

where actor_id = p_actor_id;

end;

$$;

PostgreSQL Drop Procedure examples

First, attempt to drop the insert_actor stored procedure:

drop procedure insert_actor;

PostgreSQL issued the following error:

OUTPUT:

ERROR:  procedure name "insert_actor" is not unique

HINT:  Specify the argument list to select the procedure unambiguously.

SQL state: 42725

Because there are two insert_actor stored procedures, you need to specify the argument list so that PostgreSQL can select the right stored procedure to drop.

Second, drop the insert_actor(varchar) stored procedure that accepts one argument:

drop procedure insert_actor(varchar);

OUTPUT:

Since the insert_actor stored procedure is unique now, you can drop it without specifying the argument list:

drop procedure insert_actor;

It is the same as:

drop procedure insert_actor(varchar,varchar);

Third, remove two stored procedures using a single drop procedure statement:

drop procedure

delete_actor,

update_actor;

Summary

• Use the drop procedure statement to remove a stored procedure.

• Specify a comma-separated list of stored procedure names after the drop procedure keywords to drop multiple stored procedures.

• If the stored procedure name is not unique, use the argument list to specify which stored procedure you want to drop.

  
  

  PostgreSQL Stored Procedure with INOUT Parameters

  
  

  

  
  

  Creating stored procedures with INOUT parameters

  Here’s the basic syntax for creating a stored procedure with INOUT parameters:

  
  

create or replace procedure sp_name(

    inout parameter type, ...

)

as

$$

   -- body

$$

language plpgsql;

Calling stored procedures with INOUT parameters

To call a stored procedure, you use the call statement without providing the INOUT parameters:

call sp_name();

If you call a stored procedure with INOUT parameters in an anonymous block, you need to pass arguments to the stored procedure call as follows:

do

$$

   declare

      v_name1 type;

      v_name2 type;

   begin

      -- call the stored procedure with inout parameters

      call sp_name(v_name1, v_name2);

      -- process v_name1, v_name2

   end;

$$;

PostgreSQL Stored Procedures with INOUT parameter examples

Let’s take some examples of creating stored procedures with INOUT parameters. We’ll use the film table in the sample database for the demonstration:

1) Basic PostgreSQL stored procedures with INOUT parameter example

First, create a stored procedure that counts the number of rows from the film table:

create or replace procedure count_film(

    inout total_film int default 0

)

as

$$

begin

    select count(*) from film

    into total_film;

end;

$$

language plpgsql;

Second, call the stored procedure without providing the total_film parameter:

call count_film();

Output:

Third, call the stored procedure count_film() in an anonymous block:

do

$$

declare

   total_film int = 0;

begin

   call count_film(total_film);

   raise notice 'Total film: %', total_film;

end;

$$;

Output:

2) Creating stored procedures with multiple INOUT parameters

create or replace procedure film_stat(

   inout total_film int default 0,

   inout total_length int default 0,

   inout avg_rental_rate numeric(4,2) default 0

)

as

$$

begin

  select count(*) into total_film

  from film;

  select sum(length) into total_length

  from film;

  select round(avg(rental_rate),2) into avg_rental_rate

  from film;

end;

$$

language plpgsql;

Second, call the stored procedure film_stat():

call film_stat();

OUTPUT:

Since all the parameters in the film_stat() stored procedure are the inout parameters, you don’t need to pass any parameters.

Conclusion

Overall, Stored procedures in SQL offer significant benefits by enhancing code reusability and boosting database performance. They minimize network traffic by reducing the amount of data transmitted, ensuring more efficient communication. Incorporating PostgreSQL stored procedures into your database management allows for the seamless execution of complex tasks, easing server load and improving reusability. By mastering PostgreSQL’s procedure syntax and effectively calling stored procedures, developers can streamline transaction processing and simplify database logic. This functionality not only enhances performance but also provides a robust mechanism for preserving data integrity during critical operations.

Thanks for stopping by and diving into my blog!