{"id":3346,"date":"2019-03-25T17:43:51","date_gmt":"2019-03-25T20:43:51","guid":{"rendered":"http:\/\/xexeu.elipse.com.br\/pt\/structure-query-language-sql-chapter-9-stored-procedures\/"},"modified":"2020-02-12T15:50:58","modified_gmt":"2020-02-12T18:50:58","slug":"structure-query-language-sql-chapter-9-stored-procedures","status":"publish","type":"post","link":"https:\/\/kb.elipse.com.br\/en\/structure-query-language-sql-chapter-9-stored-procedures\/","title":{"rendered":"Structure Query Language (SQL): Chapter 9 – Stored Procedures."},"content":{"rendered":"
\n

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.<\/p>\n

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.<\/p>\n

Creating a stored procedure with an input parameter<\/b><\/p>\n

The first step is to create a table in the database to be used later by the stored procedure:<\/p>\n

CREATE TABLE<\/span> tbl_teste<\/span><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (<\/span>
\nuser_ssn<\/span> int <\/span>not null primary key<\/span>,<\/span>
\nuser_nome<\/span> varchar<\/span>(128) not null,<\/span>
\nuser_email<\/span> varchar<\/span>(128) not null<\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )<\/span>
\nGO<\/span><\/div>\n

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:<\/p>\n

— CREATE STORED PROCEDURE<\/span>
\nCREATE PROCEDURE<\/span> SSNregistry_SELECT<\/span><\/span>
\n— INPUT PARAMETER, IN THIS CASE THE SOCIAL SECURITY NUMBER <\/span>
\n@SSN<\/span> int<\/span><\/span>
\nAS<\/span>
\n— T-SQL COMMAND TO RETRIEVE RESULT <\/span>
\nSELECT <\/span>* FROM <\/span>tbl_test <\/span><\/span>
\nWHERE <\/span>user_ssn = @SSN<\/span><\/span>
\nGO<\/span><\/div>\n

The figure below shows the command to execute the stored procedure. The number informed next to the instruction (998958889) is the input parameter:<\/p>\n

\"\"
\nFigure 1<\/b>: Execute command<\/span><\/div>\n

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:<\/p>\n

\"\"
\nFigure 2<\/b>: Execution via E3<\/span><\/div>\n

This SQL command executes the stored procedure by sending a variable as input parameter (<%input%><\/span>).<\/p>\n

To send an input value and return the value of the query performed by the stored procedure, add the following script to E3:<\/p>\n

\"\"
\nFigure 3<\/b>: Script inserted in E3<\/span><\/div>\n


\nCreating a stored procedure with an input parameter and an output return <\/b><\/p>\n

The first step is to create a table in the database to be used later by the stored procedure:<\/p>\n

CREATE TABLE<\/span> STAFFERS<\/span><\/span>
\n(<\/span>
\nCODE <\/span>varchar<\/span>(5),<\/span>
\nNAME <\/span>varchar<\/span>(80),<\/span>
\nDEPARTMENT <\/span>int<\/span>,<\/span>
\nWAGES <\/span>money<\/span><\/span>
\n)<\/span>
\nGO<\/span><\/div>\n

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.<\/p>\n

CREATE PROCEDURE<\/span> Wages_Dept <\/span><\/span>
\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 (@DEPT<\/span> int<\/span>,<\/span>
\n@SUM<\/span> money OUTPUT<\/span>) AS<\/span><\/span>
\nBEGIN<\/span>
\nSELECT <\/span>@SUM<\/span>=sum<\/span>(WAGES) FROM <\/span>STAFFERS<\/span><\/span>
\nWHERE <\/span>DEPARTMENT = @DEPT<\/span><\/span>
\nEND<\/span><\/div>\n

The stored procedure will be executed as follows:<\/p>\n

— Output variable<\/span>
\nDECLARE <\/span>@TOTAL_WAGES<\/span> Money<\/span><\/span>
\n— Variable for passing input parameter <\/span>
\nDECLARE <\/span>@DEPARTMENT<\/span> int<\/span><\/span>
\nSET <\/span>@DEPARTMENT<\/span> = 5<\/span>
\nEXEC <\/span>Wage_Dept@DEPARTMENT, @TOTAL_WAGE<\/span> OUTPUT<\/span><\/span>
\nPRINT <\/span>@TOTAL_WAGES<\/span><\/span><\/div>\n

Stored procedure sending and receiving parameters via E3:<\/p>\n

\"\"
\nFigure 4:<\/b> Executing the SP via E3 Query <\/span><\/div>\n

Executing the query, you’ll get the following result:<\/p>\n

\"\"
\nFigure 5:<\/b> E3 Query result <\/span><\/div>\n

To execute an Oracle stored procedure from E3, use call <\/i>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 <\/i>method.<\/p>\n<\/div>\n

Related Articles<\/h3>\n
\n