The Stored Procedures are an elegant way to store your code inside a SQL database. You can save your code and reuse it whenever you want to.

What is a Stored Procedure?

A Stored Procedure is a way that most SQL databases have to store an SQL code. It can receive variables and you can call it whenever you want to. After you store your procedure, you can call it to execute it.

Stored Procedures standardization

It is essential to know that there is no standardization in how Stored Procedures are created, every SQL database framework has its own way to create and call them, in this brief article we will try to make the same Stored Procedures in SQL Server and OracleDB but this doesn’t mean that they will work in others SQL databases frameworks.

Parameters in SQL Server

Stored Procedures in SQL Server have two types of parameters, you can also define a variable inside the Stored Procedure itself, but this variable will be part of the process. Instead, the parameter variables are parameters you will define before the stored procedures run.

Type of parameters in Stored Procedures SQL Server

In SQL Server we have only two types of parameters:

INPUT parameters: Defined by default, they enter the procedure but don’t get out of it.
OUTPUT parameters: Needs to be declared as OUTPUT, they enter the procedure and change the variable that enters inside the procedure.

Example of parameters in SQL Server

In SQL Server you would use parameter variables like this:

-- SQL Server
CREATE PROCEDURE INOUTPROC /* PROCEDURE HEADER */
(
    @p_InputVar  nvarchar(20),
    @p_OutputVar INT OUTPUT
)
AS /* PROCEDURE BODY */
BEGIN

    SET @p_OutputVar=1 
    SELECT 
       @p_OutputVar = @p_OutputVar
    SELECT 
       @p_InputVar
END
GO

/* Change the output var inside the procedure */
DECLARE @p_OutputVarEnter int = 10
EXEC INOUTPROC @p_InputVar = 'Hello World', @p_OutputVar = @p_OutputVarEnter OUTPUT
SELECT @p_OutputVarEnter

Let’s try to explain what we did here. We create a procedure called INOUTPROC, and we declare two variables for it, one called @p_InputVar that can be up to 20 characters and another variable called @p_OutputVar which would be an INT that returns a value (As output).

Inside the procedure, we changed the value of @p_OutputVar and then SELECT the value inside the @p_Inputvar.

Then, outside the procedure, we declare a variable called @p_OutputvarEnter, execute the procedure and then SELECT what is inside @p_OutputVarEnter

The output of this?

Why did this happened?

Inside the stored procedure we changed the variable @p_OutputVar, so when we SELECT the variable that we enter in the procedure once outside it, it changes. An OUTPUT variable in SQL Server is an INOUT variable in PL/SQL you can change it inside the procedure. In the case of @p_InputVar, we didn’t set it as OUTPUT variable, so we could only read it inside the procedure.

Parameters in PL/SQL

In PL/SQL and other Databases like Teradata, we have three types of parameters instead.

Type of parameters in PL/SQL

In PL/SQL we have three types of parameters:

IN: This parameter variable will be part of the procedure but won’t get out of it.
OUT: This parameter variable will return a value that the procedure creates.
INOUT: This parameter variable will enter the process and convert inside it pulling out another value.

Example of parameters in PL/SQL

Now let’s try to run the same code in PL/SQL

-- PL/SQL

CREATE OR REPLACE PROCEDURE INOUTPROC (p_InputVar IN VARCHAR2, p_OutputVar IN OUT INT) 
IS
BEGIN
    p_OutputVar:=1;
    dbms_output.put_line(p_InputVar);
    
END;

SET SERVEROUTPUT ON

DECLARE 
    OutputVarEnter INT:=10;
    InputVar varchar2(20) DEFAULT 'Hello World';
BEGIN 
 INOUTPROC (InputVar,OutputVarEnter);
 dbms_output.Put_line(OutputVarEnter);
END;

In this case, we are doing exactly the same in PL/SQL. The variables are defined as IN and IN OUT instead of INPUT and OUTPUT.

Conclusion

As you may see, Stored procedures are a convenient way to store your code, but it will have differences between the different SQL interpreters, there is no standardization in this and every database handles it in its own way. You should be aware that, as you dive into a SQL enterprise language, the difference between them will come more and more obvious.

Here are some resources where you can learn more about Stored Procedures parameters in OracleDB and SQL Server.

Maybe, now that you are learning advanced SQL, you could also get something from this post.

Share on Facebook

How to use Parameters in Stored Procedures?