A stored procedure is a set of instructions developed in T-SQL language (Transact-Sql) that, whenever stored or saved, are inside the server in a pre-compiled way. This procedure is stored in the database (Server) and can be called at any time, either by the database management system (DBMS) or by an application using Elipse E3. Using stored procedures helps improve the applications’ performance.
The goal of this article is to show the basic use of stored procedures in conjunction with Elipse E3. The database used in this example is Microsoft SQL Server 2012.
Creating a stored procedure with an input parameter
The first step is to create a table in the database to be used later by the stored procedure:
(
user_ssn int not null primary key,
user_nome varchar(128) not null,
user_email varchar(128) not null
)
GO
In the example below, we’ll create a stored procedure that will pass parameters, where the Social Security number (SSN) will be informed as an input parameter. The stored procedure will return this result:
CREATE PROCEDURE SSNregistry_SELECT
— INPUT PARAMETER, IN THIS CASE THE SOCIAL SECURITY NUMBER
@SSN int
AS
— T-SQL COMMAND TO RETRIEVE RESULT
SELECT * FROM tbl_test
WHERE user_ssn = @SSN
GO
The figure below shows the command to execute the stored procedure. The number informed next to the instruction (998958889) is the input parameter:
Figure 1: Execute command
After the stored procedure has been created and set up in the database, is must run it via E3. First, add a Query, and then enble SQL’s direct edition by adding the following line:
Figure 2: Execution via E3
This SQL command executes the stored procedure by sending a variable as input parameter (<%input%>).
To send an input value and return the value of the query performed by the stored procedure, add the following script to E3:
Figure 3: Script inserted in E3
Creating a stored procedure with an input parameter and an output return
The first step is to create a table in the database to be used later by the stored procedure:
(
CODE varchar(5),
NAME varchar(80),
DEPARTMENT int,
WAGES money
)
GO
In the example below, we’ll create a stored procedure that will pass input and output parameters; in this case, you will need to inform an OUTPUT variable. This stored procedure sends an input parameter as selection criteria for retrieving the sum of wages in the STAFFERS table via an output parameter.
(@DEPT int,
@SUM money OUTPUT) AS
BEGIN
SELECT @SUM=sum(WAGES) FROM STAFFERS
WHERE DEPARTMENT = @DEPT
END
The stored procedure will be executed as follows:
DECLARE @TOTAL_WAGES Money
— Variable for passing input parameter
DECLARE @DEPARTMENT int
SET @DEPARTMENT = 5
EXEC Wage_Dept@DEPARTMENT, @TOTAL_WAGE OUTPUT
PRINT @TOTAL_WAGES
Stored procedure sending and receiving parameters via E3:
Figure 4: Executing the SP via E3 Query
Executing the query, you’ll get the following result:
Figure 5: E3 Query result
To execute an Oracle stored procedure from E3, use call command in the Query’s SQL code, and then the name of the stored procedure as well as its parameters. To execute the command, use Query’s Execute method.
Related Articles
- Structure Query Language (SQL): Chapter 1 – Manipulating information in the DB.
- Structure Query Language (SQL): Chapter 2 – SQL Server Database’s Files and Logs.
- Structure Query Language (SQL): Chapter 3 – Data Discard and DB Limits.
- Structure Query Language (SQL): Chapter 4 – Backup.
- Structure Query Language (SQL): Chapter 5 – Best practices for setting up Historics and Queries.
- Structure Query Language (SQL): Chapter 6 – SQL Commands.
- Structure Query Language (SQL): Chapter 7 – Views.
- Structure Query Language (SQL): Chapter 8 – Triggers.
- Structure Query Language (SQL): Chapter 9 – Stored Procedures.
