Stored procedures and their use. Use special requests. Intolerance of stored functions

Stored procedures

The subject of this chapter is one of the most powerful tools offered to developers of InterBase database applications for implementing business logic. Stored procedures (English, stoied proceduies) allow you to implement a significant part of the application logic at the database level and thus increase the performance of the entire application and centralize data processing and reduce the amount of code required to complete the tasks. Almost anything is enough complex application Databases are not complete without the use of stored procedures.
In addition to these well-known advantages of using stored procedures, common to most relational DBMSs, InterBase stored procedures can act as almost complete data sets, allowing the results they return to be used in ordinary SQL queries.
Often, novice developers imagine stored procedures simply as a set of specific SQL queries that do something inside the database, and there is an opinion that working with stored procedures is much more difficult than implementing the same functionality in a client application in the language high level
So what are stored procedures in InterBase?
A stored procedure (SP) is a part of the database metadata, which is a subroutine compiled into the internal representation of InterBase, written in a special language, the compiler of which is built into the core of the InteiBase server
A stored procedure can be called from client applications, from triggers, and from other stored procedures. The stored procedure runs inside the server process and can manipulate data in the database, as well as return the results of its execution to the client that called it (i.e. trigger, HP, application)
basis powerful features, embedded in HP, is a procedural programming language that includes both modified statements of regular SQL, such as INSERT, UPDATE and SELECT, as well as tools for organizing branches and loops (IF, WHILE), as well as means for handling errors and exceptions The language of stored procedures allows you to implement complex algorithms for working with data, and due to its focus on working with relational data, HP is much more compact than similar procedures in traditional languages.
It should be noted that the same programming language is used for triggers, with the exception of a number of features and limitations. The differences between the subset of the language used in triggers and the HP language are discussed in detail in the chapter "Triggers" (part 1).

Example of a simple stored procedure

It's time to create your first stored procedure and use it as an example to learn the process of creating stored procedures. But first, we should say a few words about how to work with stored procedures. The fact is that HP owes its reputation as an obscure and inconvenient tool to extremely poor standard tools for developing and debugging stored procedures. The InterBase documentation recommends creating procedures using SQL script files containing HP text, which are supplied as input to the isql interpreter, and thus creating and modifying HP If in this SQL script at the stage of compiling the text of the procedure in BLR (about BLR, see Chapter "InterBase Database Structure" (Part 4)) if an error occurs, isql will display a message about which line of the SQL script file this error occurred on. Correct the mistake and do it all over again. There is no talk at all about debugging in the modern sense of the word, i.e. about execution tracing, with the ability to view intermediate values ​​of variables. Obviously, this approach does not contribute to the growth of the attractiveness of stored procedures in the eyes of the developer
However, in addition to the standard minimalist approach to HP development<_\ществ\ют также инструменты сторонних разработчиков, которые делают работу с хранимыми процедурами весьма удобной Большинство универсальных продуктов для работы с InterBase, перечисленных в приложении "Инструменты администратора и разработчика InterBase", предоставляют удобный инструментарий для работы с ХП. Мы рекомендуем обязательно воспользоваться одним из этих инструментов для работы с хранимыми процедурами и изложение материала будем вести в предположении, что у вас имеется удобный GUI-инструмент, избавляющий от написания традиционных SQL-скриптов
The syntax of stored procedures is described as follows:

CREATE PROCEDURE name
[ (param datatype [, param datatype ...]) ]
)]
AS
;
< procedure_body> = []
< block>
< vanable_declaration_list> =
DECLARE VARIABLE var datatype;

=
BEGIN
< compound_statement>
[< compound_statement> ...]
END
< compound_statement> = (statement;)

It looks quite voluminous and may even be cumbersome, but in fact everything is very simple. In order to gradually master the syntax, let's look at gradually more complex examples.
So, here is an example of a very simple stored procedure that takes two numbers as input, adds them and returns the result:

CREATE PROCEDURE SP_Add(first_arg DOUBLE PRECISION,
second_arg DOUBLE PRECISION)
RETURNS (Result DOUBLE PRECISION)
AS
BEGIN
Result=first_arg+second_arg;
SUSPEND;
END

As you can see, everything is simple: after the CREATE PROCEDURE command, the name of the newly created procedure is indicated (which must be unique within the database) - in this case SP_Add, then the HP input parameters - first_arg and second_arg - are listed in parentheses, separated by commas, indicating their types.
The list of input parameters is an optional part of the CREATE PROCEDURE statement - there are cases when a procedure receives all the data for its work through queries to tables inside the body of the procedure.

Stored procedures use any scalar data types InteiBase It does not allow for the use of arrays and user-defined types - domains

Next comes keyword RETURNS, after which the returned parameters are listed in parentheses, indicating their types - in this case, only one - Result.
If the procedure should not return parameters, then the word RETURNS and the list of returned parameters are missing.
After RETURNSQ the keyword AS is specified. Before the keyword AS goes title, and after that - techo procedures.
The body of a stored procedure is a list of descriptions of its internal (local) variables (if they exist, we will look at them in more detail below), separated by a semicolon (;), and a block of statements enclosed in operator brackets BEGIN END. In this case, the body of the HP is very simple - we ask to add two input arguments and assign their result to the output, and then call the SUSPEND command. A little later we will explain the essence of the action of this command, but for now we will only note that it is needed to transfer the return parameters to where the stored procedure was called from.

Delimiters in stored procedures

Note that a statement within a procedure ends with a semicolon (;). As you know, the semicolon is a standard command separator in SQL - it is a signal to the SQL interpreter that the command text has been entered in full and should begin processing it. Wouldn't it turn out that if the SQL interpreter finds a semicolon in the middle of the HP, it will consider that the command has been entered in full and will try to execute part of the stored procedure? This assumption is not without merit. Indeed, if you create a file in which to write the above example, add a connection command from the database and try to execute this SQL script using the isql interpreter, an error will be returned due to the unexpected, in the interpreter’s opinion, ending of the stored procedure creation command. If you create stored procedures using SQL script files, without using specialized InterBase developer tools, then before each HP creation command (the same applies to triggers) you need to change the script command separator to another character other than a semicolon, and after text HP to restore it back. The isql command that changes the SQL clause separator looks like this:

SET TERM

For a typical case of creating a stored procedure, it looks like this:

SET TERM^;
CREATE PROCEDURE some_procedure
... . .
END
^
SET TERM ;^

Calling a Stored Procedure

But let's return to our stored procedure. Now that it has been created, you need to call it somehow, pass parameters to it and get the results returned. This is very easy to do - just write an SQL query like this:

SELECT *
FROM Sp_add(181.35, 23.09)

This query will return us one line containing just one Result field, which will contain the sum of the numbers 181.35 and 23.09, i.e. 204.44.
Thus, our procedure can be used in ordinary SQL queries executed both in client programs and in other HP or triggers. This use of our procedure is made possible by using the SUSPEND command at the end of the stored procedure.
The fact is that in InterBase (and in all its clones) there are two types of stored procedures: selectable procedures and executable procedures. The difference in the operation of these two types of HP is that sampling procedures usually return many sets of output parameters, grouped line by line, which look like a set of data, while executable procedures could either not return parameters at all, or return only one set of output parameters , listed in Returns, where one line of parameters. Select procedures are called in SELECT queries, and executable procedures are called using the EXECUTE PROCEDURE command.
Both types of stored procedures have the same creation syntax and are formally no different, so any executable procedure can be called in a SELECT query and any selection procedure can be called using EXECUTE PROCEDURE. The question is how HP will behave when different types call. In other words, the difference lies in designing the procedure for a particular type of call. That is, the select procedure is specifically created to be called from a SELECT query, and the executable procedure is specifically created to be called using EXECUTE PROCEDURE. Let's look at what the differences are in the design of these two types of HP.
In order to understand how the sampling procedure works, you will have to delve a little deeper into the theory. Let's imagine a regular SQL query like SELECT ID, NAME FROM Table_example. As a result of its execution, we get a table consisting of two columns (ID and NAME) and a certain number of rows (equal to the number of rows in the Table_example table). The table returned from this query is also called a set SQL data Let's think about how the data set is formed during the execution of this query. The server, having received the query, determines which tables it refers to, then finds out which subset of records from these tables needs to be included in the query result. Next, the server reads each record that satisfies the query results and selects from it required fields(in our case these are ID and NAME) and sends them to the client. Then the process is repeated again - and so on for each selected record.
All this digression is necessary so that the dear reader understands that all SQL data sets are generated row by row, including in stored procedures! And the main difference between fetch procedures and executable procedures is that the former are designed to return many rows, while the latter are designed to return only one. That's why they are used differently: the select procedure is called using the SELECT command, which "requires" the procedure to give up all the records that it can return. The executable procedure is called using EXECUTE PROCEDURE, which “takes out” only one line from the HP, and ignores the rest (even if they exist!).
Let's look at an example of a sampling procedure to make it clearer. For > forgiveness, let's create a stored procedure that works exactly like a SELECT ID, NAME FROM Table_Example query, that is, it simply selects the ID and NAME fields from the entire table. Here is this example:

CREATE PROCEDURE Simple_Select_SP
RETURNS (
procID INTEGER,
procNAME VARCHAR(80))
AS
BEGIN
FOR
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
DO
BEGIN
SUSPEND;
END
END

Let's look at the steps of this procedure, called Simple_Select_SP. As you can see, it has no input parameters and has two output parameters - ID and NAME. The most interesting thing, of course, lies in the body of the procedure. The FOR SELECT construct is used here:

FOR
SELECT ID, NAME FROM table_example
INTO:procID, :procNAME
DO
BEGIN

/*do something with the variables procID and procName*/

END

This piece of code means the following: for each row selected from the Table_example table, put the selected values ​​in the procID and procName variables, and then do something with these variables.
You might make a surprised face and ask, "Variables? What other variables? 9" It's kind of the surprise of this chapter that we can use variables in stored procedures. In the HP language, you can declare both your own local variables inside a procedure and use input and output parameters as variables.
In order to declare a local variable in a stored procedure, you must place its description after the AS keyword and before the first word BEGIN. The local variable description looks like this:

DECLARE VARIABLE ;

For example, to declare an integer local variable Mylnt, you need to insert the following declaration between AS and BEGIN

DECLARE VARIABLE Mylnt INTEGER;

The variables in our example begin with a colon. This is done because they are accessed within the FOR SELECT SQL command, so to distinguish between fields in tables that are used in SELECT and variables, the latter must be preceded by a colon. After all, variables can have exactly the same name as fields in tables!
But the colon before a variable name should only be used inside SQL queries. Outside of texts, a variable is referred to without a colon, for example:

procName="Some name";

But let's return to the body of our procedure. The FOR SELECT clause returns data not as a table - a set of data, but one row at a time. Each returned field must be placed in its own variable: ID => procID, NAME => procName. In the DO part, these variables are sent to the client that called the procedure using the SUSPEND command
Thus, the FOR SELECT...DO command loops through the records selected in the SELECT part of the command. In the body of the loop formed by the DO part, the next generated record is transferred to the client using the SUSPEND command.
So, the selection procedure is designed to return one or more rows, for which a loop is organized inside the HP body that fills the resulting variable parameters. And at the end of the body of this loop there is always a SUSPEND command, which will return the next row of data to the client.

Loops and branch statements

In addition to the FOR SELECT...DO command, which organizes a loop through the records of a selection, there is another type of loop - WHILE...DO, which allows you to organize a loop based on checking any conditions. Here is an example of HP using the WHILE..DO loop. This procedure returns the squares of integers from 0 to 99:

CREATE PROCEDJRE QUAD
RETURNS (QUADRAT INTEGER)
AS
DECLARE VARIABLE I INTEGER;
BEGIN
I = 1;
WHILE(i<100) DO
BEGIN
QUADRAT= I*I;
I=I+1;
SUSPEND;
END
END

As a result of executing the SELECT FROM QUAD query, we will receive a table containing one QUADRAT column, which will contain the squares of integers from 1 to 99
In addition to iterating through the results of a SQL sample and a classic loop, the stored procedure language uses the IF...THEN..ELSE operator, which allows you to organize branching depending on the execution of any conditions. Its syntax is similar to most branching operators in high-level programming languages, like Pascal and C.
Let's look at a more complex example of a stored procedure that does the following.

1. Calculates the average price in the table Table_example (see chapter "Tables Primary Keys and Generators")
2. Next, for each entry in the table, it makes the following check: if the existing price (PRICE) is greater than the average price, then it sets a price equal to the average price, plus a specified fixed percentage
3. If the existing price is less than or equal to the average price, then sets a price equal to the previous price, plus half the difference between the previous and average price.
4. Returns all modified rows in the table.

First, let's define the name of the HP, as well as input and output parameters. All this is written in the header of the stored procedure.

CREATE PROCEDURE IncreasePrices (
Percent2lncrease DOUBLE PRECISION)
RETURNS (ID INTEGER, NAME VARCHAR(SO), new_price DOUBLE
PRECISION AS

The procedure will be called IncreasePrices, it has one input parameter Peiceni21nciease of type DOUBLE PRECISION, and 3 output parameters - ID, NAME and new_pnce. Note that the first two output parameters have the same names as the fields in the table Table_example that we are going to work with. This is allowed by the rules of the stored procedure language.
Now we need to declare a local variable that will be used to store the average value. The declaration will look like this:

DECLARE VARIABLE avg_price DOUBLE PRECISION;

Now let's move on to the body of the stored procedure. Open the body of the HP keyword BEGIN.
First we need to perform the first step of our algorithm - calculate the average price. To do this, we will use the following type of query:

SELECT AVG(Price_l)
FROM Table_Example
INTO:avg_price,-

This query uses the AVG aggregate function, which returns the average of the PRICE_1 field among the selected query rows—in our case, the average of the PRICE_1 across the entire Table_example table. The value returned by the request is placed in the avg_price variable. Note that the avg_pnce variable is preceded by a colon to distinguish it from the fields used in the request.
Feature of this request is that it always returns exactly one single record. Such queries are called singleton queries. And only such selections can be used in stored procedures. If a query returns more than one row, then it must be formatted as a FOR SELECT...DO construct, which organizes a loop to process each returned row
So, we got the average price. Now you need to go through the entire table, compare the price value in each entry with the average price and take appropriate actions
From the beginning, we organize the search for each record from the Table_example table

FOR
SELECT ID, NAME, PRICE_1
FROM Table_Example
INTO:ID, :NAME, :new_price
DO
BEGIN
/*_here we describe each entry*/
END

When this construction is executed, data will be extracted from the Table_example table row by row and the field values ​​in each row will be assigned to the variables ID, NAME and new_pnce. You, of course, remember that these variables are declared as output parameters, but there is no need to worry that the selected data will be returned as results: the fact that the output parameters are assigned something does not mean that the client calling the HP will immediately receive these values ! Parameters are passed only when the SUSPEND command is executed, and before that we can use the output parameters as ordinary variables - in our example we do just that with the new_price parameter.
So, inside the body of the BEGIN... END loop we can process the values ​​of each row. As you remember, we need to figure out how the existing price compares to the average and take appropriate action. We implement this comparison procedure using the IF statement:

IF (new_price > avg_price) THEN /*if the existing price is greater than the average price*/
BEGIN
/*then we will set a new price equal to the average price, plus a fixed percentage */
new_price = (avg_price + avg_price*(Percent2Increase/100));
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID = :ID;
END
ELSE
BEGIN
/* If the existing price is less than or equal to the average price, then set a price equal to the previous price, plus half the difference between the previous and average price */
new_price = (new_pnce + ((avg_pnce new_price)/2)) ;
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID = .ID;
END

As you can see, the result is a fairly large IF construct, which would be difficult to understand if not for the comments enclosed in the /**/ symbols.
In order to change the price according to the calculated difference, we will use the UPDATE statement, which allows us to modify existing records- one or more. In order to unambiguously indicate in which record the price needs to be changed, we use the primary key field in the WHERE condition, comparing it with the value of the variable that stores the ID value for the current record: ID=:ID. Note that the ID variable is preceded by a colon.
After executing the IF...THEN...ELSE construct, the ID, NAME and new_price variables contain data that we must return to the client that called the procedure. To do this, after IF, you need to insert the SUSPEND command, which will send the data to where the HP was called from. During the transfer, the procedure will be suspended, and when the HP is required new entry, then it will be continued again - and this will continue until FOR SELECT...DO iterates through all the records of its query.
It should be noted that in addition to the SUSPEND command, which only suspends the stored procedure, there is an EXIT command that terminates the stored procedure after passing the string. However, the EXIT command is used quite rarely, since it is needed mainly to interrupt the loop when a condition is reached
However, in the case where the procedure was called with a SELECT statement and completed with EXIT, the last row retrieved will not be returned. That is, if you need to interrupt the procedure and still >get this string, you need to use the sequence

SUSPEND;
EXIT;

The main purpose of EXIT is to receive singleton data sets, returned parameters by calling EXECUTE PROCEDURE. In this case, the values ​​of the output parameters are set, but the SQL data set is not generated from them, and the execution of the procedure ends.
Let's write down the text of our stored procedure in its entirety so that we can capture its logic at a glance:

CREATE PROCEDURE IncreasePrices (
Percent2Increase DOUBLE PRECISION)
RETURNS (ID INTEGER, NAME VARCHAR(80),
new_price DOUBLE PRECISION) AS
DECLARE VARIABLE avg_price DOUBLE PRECISION;
BEGIN
SELECT AVG(Price_l)
FROM Table_Example
INTO:avg_price;
FOR
SELECT ID, NAME, PRICE_1
FROM Table_Example
INTO:ID, :NAME, :new_price
DO
BEGIN
/*process each record here*/
IF (new_pnce > avg_price) THEN /*if the existing price is greater than the average price*/
BEGIN
/*set a new price equal to the average price plus a fixed percentage */
new_price = (avg_price + avg_price*(Percent2lncrease/100));
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID = :ID;
END
ELSE
BEGIN
/* If the existing price is less than or equal to the average price, then sets a price equal to the previous price plus half the difference between the previous and average price */
new_price = (new_price + ((avg_price - new_price)/2));
UPDATE Table_example
SET PRICE_1 = :new_price
WHERE ID = :ID;
END
SUSPEND;
END
END

This stored procedure example illustrates the use of basic stored procedure language constructs and triggers. Next, we'll look at ways to use stored procedures to solve some common problems.

Recursive Stored Procedures

InterBase stored procedures can be recursive. This means that a stored procedure can call itself. Up to 1000 levels of nesting of stored procedures is allowed, but we must remember that free resources on the server may run out before the maximum nesting of HP is reached.
One common use of stored procedures is to process tree structures stored in a database. Trees are often used in product composition, warehouse, personnel, and other common applications.
Let's look at an example of a stored procedure that selects all products of a certain type, starting from a certain nesting level.
Let us have the following formulation of the problem: we have a directory of goods with a hierarchical structure of this type:

Goods
- Household appliances
- Refrigerators
- Three-chamber
- Double chamber
- Single chamber
- Washing machines
- Vertical
- Frontal
- Classic
- Narrow
- Computer technology
....

This structure of the product category directory can have branches of varying depths. and also increase over time. Our task is to ensure a selection of all finite elements from the directory with "expanding the full name", starting from any node. For example, if we select the “Washing machines” node, then we need to get the following categories:

Washing machines - Vertical
Washing machines - Front Classic
Washing machines - Front Narrow

Let's define the table structure for storing product directory information. We use a simplified scheme to organize the tree in one table:

CREATE TABLE GoodsTree
(ID_GOOD INTEGER NOT NULL,
ID_PARENT_GOOD INTEGER,
GOOD_NAME VARCHAR(80),
constraint pkGooci primary key (ID_GOOD));

We create one table GoodsTree, in which there are only 3 fields: ID_GOOD - the smart identifier of the category, ID_PARENT_GOOD - the identifier of the parent company for this category and GOOD_NAME - the name of the category. To ensure the integrity of the data in this table, we will impose a foreign key constraint on this table:

ALTER TABLE GoodsTree
ADD CONSTRAINT FK_goodstree
FOREIGN KEY (ID_PARENT_GOOD)
REFERENCES GOODSTPEE (ID__GOOD)

The table refers to itself and this foreign key keeps track of that. so that the table does not contain references to non-existent parents, and also prevents attempts to delete product categories that have children.
Let's enter the following data into our table:

Now that we have a place to store the data, we can start creating a stored procedure that will output all the "final" product categories in an "expanded" form - for example, for the category "Three-Chamber", the full category name would be "Household Appliances Refrigerators" Three-chamber".
Stored procedures that process tree structures have their own terminology. Each element of the tree is called a node; and the relationship between nodes referencing each other is called a parent-child relationship. Nodes that are at the very end of the tree and have no children are called "leaves".
For this stored procedure, the input parameter will be the category identifier, from which we will have to start the drilldown. The stored procedure will look like this:

CREATE PROCEDURE GETFULLNAME (ID_GOOD2SHOW INTEGER)
RETURNS (FULL_GOODS_NAME VARCHAR(1000),
ID_CHILD_GOOD INTEGER)
AS
DECLARE VARIABLE CURR_CHILD_NAME VARCHAR(80);
BEGIN
/*0organize external FOR loop SELECT on immediate descendants of the product with ID_GOOD=ID_GOOD2SHOW */
FOR SELECT gtl.id_good, gtl.good_name
FROM GoodsTree gtl
WHERE gtl.id_parent_good=:ID_good2show
INTO:ID_CHILD_GOOD, :full_goods_name
DO
BEGIN
/"Check using the EXISTS function, which returns TRUE if the query in brackets returns at least one row. If the found node with ID_PARENT_GOOD = ID_CHILD_GOOD has no children, then it is a “leaf” of the tree and is included in the results */
IF (NOT EXISTS(
SELECT * FROM GoodsTree
WHERE GoodsTree.id_parent_good=:id_child_good))
THEN
BEGIN
/* Pass the “leaf” of the tree to the results */
SUSPEND;
END
ELSE
/* For nodes that have children*/
BEGIN
/*save the name of the parent node in a temporary variable */
CURR_CHILD_NAME=full_goods_name;
/* run this procedure recursively */
FOR
SELECT ID_CHILD_GOOD, full_goods_name
FROM GETFULLNAME (:ID_CHILD_GOOD)
INTO:ID_CHILD_GOOD, :full_goods_name
DO BEGIN
/*add the name of the parent node to the found child name using the string concatenation operation || */
full_goods_name=CURR_CHILD_NAME| " " | f ull_goods_name,-
SUSPEND; /* return the full name of the product*/
END
END
END
END

If we execute this procedure with the input parameter ID_GOOD2SHOW= 1, we will get the following:

As you can see, using a recursive stored procedure, we went through the entire category tree and displayed the full name of the “leaf” categories that are located at the very tips of the branches.

Conclusion

This concludes our consideration of the main features of the stored procedure language. Obviously, it is impossible to fully master stored procedure development in just one chapter, but here we have tried to introduce and explain the basic concepts associated with stored procedures. The described designs and techniques for designing HP can be applied in most database applications
Some of the important issues related to the development of stored procedures will be covered in the next chapter - “Advanced capabilities of the InterBase stored procedure language”, which is devoted to exception handling, resolving error situations in stored procedures, and working with arrays.

Stored procedure - a database object, which is a set of SQL instructions that is compiled once and stored on the server. Stored procedures are very similar to ordinary high-level language procedures, they can have input and output parameters and local variables, they can perform numeric calculations and operations on character data, the results of which can be assigned to variables and parameters. Stored procedures can perform standard database operations (both DDL and DML). In addition, stored procedures allow loops and branches, that is, they can use instructions to control the execution process.

Stored procedures are similar to user-defined functions (UDFs). The main difference is that user-defined functions can be used like any other expression in a SQL statement, while stored procedures must be called using the CALL function:

CALL procedure(…)

EXECUTE procedure(…)

Stored procedures can return multiple results, that is, the results of a SELECT query. Such result sets can be processed using cursors, other stored procedures that return a result set pointer, or applications. Stored procedures can also contain declared variables for processing data and cursors, which allow you to loop over multiple rows in a table. The SQL standard provides IF, LOOP, REPEAT, CASE, and many others to work with. Stored procedures can accept variables, return results, or modify variables and return them, depending on where the variable is declared.

The implementation of stored procedures varies from one DBMS to another. Most major database vendors support them in one form or another. Depending on the DBMS, stored procedures can be implemented in various programming languages, such as SQL, Java, C or C++. Stored procedures not written in SQL may or may not execute SQL queries on their own.

For

Sharing logic with other applications. Stored procedures encapsulate functionality; this provides connectivity for accessing and managing data across different applications.

Isolating users from database tables. This allows you to give access to stored procedures, but not to the table data itself.

Provides a protection mechanism. As per the previous point, if you can only access data through stored procedures, no one else can erase your data through the SQL DELETE command.

Improved execution as a result of reduced network traffic. Using stored procedures, multiple queries can be combined.

Against

Increased load on the database server due to the fact that most of the work is performed on the server side, and less on the client side.

You'll have to learn a lot. You will need to learn MySQL expression syntax to write your stored procedures.

You are duplicating your application logic in two places: server code and code for stored procedures, thereby complicating the process of data manipulation.

Migration from one DBMS to another (DB2, SQL Server, etc.) can lead to problems.

Purpose and Benefits of Stored Procedures

Stored procedures improve performance, enhance programming capabilities, and support data security features.

Instead of storing a frequently used query, clients can reference the corresponding stored procedure. When a stored procedure is called, its contents are immediately processed by the server.

In addition to actually executing the query, stored procedures also allow you to perform calculations and manipulate data - changing, deleting, executing DDL statements (not in all DBMSs!) and calling other stored procedures, and performing complex transactional logic. A single statement allows you to call a complex script contained in a stored procedure, avoiding sending hundreds of commands across the network and, in particular, the need to transfer large amounts of data from the client to the server.

In most DBMSs, the first time a stored procedure is run, it is compiled (parsed and a data access plan is generated). In the future, its processing is faster. The Oracle DBMS interprets stored procedural code stored in the data dictionary. Starting with Oracle 10g, the so-called native compilation of stored procedural code in C and then into the machine code of the target machine is supported, after which, when a stored procedure is called, its compiled object code is directly executed.

Programming capabilities

The created stored procedure can be called at any time, which provides modularity and encourages code reuse. The latter makes the database easier to maintain, as it becomes isolated from changing business rules. You can modify a stored procedure in accordance with the new rules at any time. After this, all applications using it will automatically come into compliance with the new business rules without direct modification.

Safety

The use of stored procedures allows you to limit or completely eliminate direct user access to database tables, leaving users with only permissions to execute stored procedures that provide indirect and strictly regulated access to data. In addition, some DBMSs support text encryption (wrapping) of a stored procedure.

These security features allow the database structure to be isolated from the user, ensuring database integrity and reliability.

The likelihood of actions such as SQL injection is reduced because well-written stored procedures additionally check input parameters before passing the query to the DBMS.

Implementing Stored Procedures

Stored procedures are typically created using SQL language or its specific implementation in the selected DBMS. For example, for these purposes in the DBMS Microsoft SQL Server there is a Transact-SQL language, in Oracle - PL/SQL, in InterBase and Firebird - PSQL, in PostgreSQL - PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, in IBM DB2 - SQL/PL (eng. ), in Informix - SPL. MySQL follows the SQL:2003 standard quite closely, its language is similar to SQL/PL.

Some DBMSs allow the use of stored procedures written in any programming language that can create independent executable files, for example, C++ or Delphi. In Microsoft SQL Server terminology, such procedures are called extended stored procedures and are simply functions contained in a Win32 DLL. And, for example, in Interbase and Firebird, functions called from DLL/SO have a different name - UDF (User Defined Function). MS SQL 2005 introduced the ability to write stored procedures in any .NET language, and extended stored procedures are planned to be abandoned in the future. The Oracle DBMS, in turn, allows writing stored procedures in Java language. In IBM DB2, writing stored procedures and functions in conventional programming languages ​​is a traditional way, supported from the very beginning, and the SQL procedural extension was added to this DBMS only in fairly late versions, after its inclusion in the ANSI standard. Informix also supports procedures in Java and C.

In the Oracle DBMS, stored procedures can be combined into so-called packages. A package consists of two parts - a package specification, which specifies the definition of a stored procedure, and a package body, which contains its implementation. Thus, Oracle allows you to separate the interface of the program code from its implementation.

In the IBM DB2 DBMS, stored procedures can be combined into modules.

Syntax

CREATE PROCEDURE `p2`()

SQL SECURITY DEFINER

COMMENT "A procedure"

SELECT "Hello World !";

The first part of the code creates a stored procedure. The next one contains optional parameters. Then comes the name and, finally, the body of the procedure itself.

4 characteristics of a stored procedure:

Language: For portability purposes, the default is SQL.

Deterministic: if the procedure always returns the same result and takes the same input parameters. This is for the replication and registration process. The default value is NOT DETERMINISTIC.

SQL Security: user rights are checked during the call. INVOKER is the user calling the stored procedure. DEFINER is the “creator” of the procedure. The default value is DEFINER.

Comment: For documentation purposes, the default value is ""

Calling a Stored Procedure

CALL stored_procedure_name (param1, param2, ....)

CALL procedure1(10 , "string parameter" , @parameter_var);

Modifying a Stored Procedure

MySQL has an ALTER PROCEDURE statement for changing procedures, but it is only suitable for changing certain characteristics. If you need to change the parameters or body of a procedure, you should delete and recreate it.

Removalstoredprocedures

DROP PROCEDURE IF EXISTS p2;

This is a simple command. The IF EXISTS statement catches an error if such a procedure does not exist.

Options

CREATE PROCEDURE proc1(): empty parameter list

CREATE PROCEDURE proc1 (IN varname DATA-TYPE): one input parameter. The word IN is optional because the default parameters are IN (in).

CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): one parameter returned.

CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): one parameter, both input and return.

The variable declaration syntax looks like this:

DECLARE varname DATA-TYPE DEFAULT defaultvalue;

In the previous article in this series, we looked at how you can extract data from tables, modify their structure, create, modify and delete databases and the objects they contain. In this article we will talk in more detail about objects typical of server DBMSs: views, triggers and stored procedures.

In the first article of this series, published in issue 3’2000 of our magazine, we noted that most modern server DBMSs support views, triggers and stored procedures. Views are also supported by many desktop DBMSs, for example Access, dBase, Clipper.

It should be noted that triggers and stored procedures are usually written in programming languages ​​that are procedural extensions of the SQL language. These extensions contain operators that allow you to describe algorithms, for example do...while, if...then...else, that are not present in the SQL language itself (if you remember, SQL is a non-procedural language, and you can formulate a task in it, but you cannot describe algorithms for its execution). Unlike the standard-compliant SQL language, its procedural extensions are not standardized in any way, and different DBMSs use different syntax constructs to implement the same algorithmic constructs, but a discussion of differences in the syntax of SQL extensions for different DBMSs is beyond the scope of this article.

To illustrate how you can use views, triggers, and stored procedures, we chose Microsoft SQL Server 7.0 and the NorthWind database included with this DBMS.

Before you run the examples, please note that the implementation and storage of triggers and stored procedures in the DBMS you use may differ from those shown in this article. Additionally, you must have the appropriate permissions granted by your database administrator to create server objects.

Note also that some ODBC drivers do not support calling stored procedures from client applications, even if they are supported by the DBMS itself. However, in this case, stored procedures can still be called from triggers.

Let's start with views, then discuss stored procedures, and end the chapter with an overview of triggers.

Submissions

A view is a virtual table, usually containing a set of columns from one or more tables. In reality, the view does not contain data, but only an SQL query of the SELECT type, indicating which data and from which tables should be taken when accessing this view. From this point of view, a view is a stored query.

In most cases, views are used to ensure data security. For example, some categories of users may have access to a view, but not to the tables whose data forms it; Additionally, the SQL query may contain a USER parameter (the name under which the user is logged in), in which case the data available when accessing the view will depend on the user name.

The following are the main characteristics of views:

• views behave like tables;
• views do not contain data;
• views can use data from more than one table.

We can use the SQL CREATE VIEW statement to create a view, the ALTER VIEW statement to modify it, and the DROP VIEW statement to delete it.

We'll start with the CREATE VIEW statement, which allows us to create a view for the current database.

CREATE VIEW clause

The syntax for creating a view resembles an SQL SELECT statement with a few additional keywords. Below is its simplified syntax:

CREATE VIEW view_name AS select_statement

The view_name argument specifies the name of the view. The keyword, used in Microsoft SQL Server, allows you to hide the original text of the CREATE VIEW statement in the syscomments table.

The AS keyword specifies which SELECT query will actually be executed when accessing the view. Note that this query cannot contain the ORDER BY, COMPUTE or COMPUTE BY, INTO keywords and cannot reference a temporary table.

To modify a previously created view, use the ALTER VIEW clause, briefly described in the next section.

DROP VIEW offer

This clause is used to remove a view from the database. Note that when you delete a table from the database, all views that reference it are also deleted. Using this clause, we must specify the name of the view to be deleted. After a view is deleted, all information about it is deleted from the system tables.

Another case where a view needs to be deleted is if the structure of the tables on which it is based has changed since the view was created. In this case, you can delete the view and then recreate it using the CREATE VIEW clause.

Creating and using views

The CREATE VIEW clause is used to create views that allow you to retrieve data that meets specific requirements. The view is created in the current database and stored as a separate database object.

The best way to create a view is to create a SELECT statement and, after testing it, add the missing part of the CREATE VIEW statement. Let's look at the source code for the Products by Category view in the NorthWind database (Listing 1).

First line highlighted in bold, represents how the SQL statement for creating a view differs from a regular SELECT query that does the work of selecting data. The SELECT clause contained in this view selects fields from two tables - the CategoryName field from the CATEGORIES table and the ProductName, QuantityPerUnit, UnitsInStock, Discontinued fields from the PRODUCTS table. The data from the two tables is then linked using the CategoryID field, and only those products that are still in stock (see the criterion after the WHERE keyword) are included in the resulting data set. The result of accessing this view is shown in Fig. 1.

Now let's create a view showing all the territories in the eastern region. This view is based on the following query (Listing 2).

Once we're confident that the SELECT clause returns the results we want, we add a CREATE VIEW statement and name the view we're creating EASTTERR (Listing 3).

Instead of manually creating the presentation text, you can use visual tools that are typically included with the DBMS. In Fig. Figure 2 shows how the same view can be created using the View Designer tool, which is integral part Enterprise Manager included with Microsoft SQL Server.

The top portion of the View Designer allows you to specify how tables are related and which fields will appear in the view. Below you can specify aliases for tables and fields, restrictions on their values, and display method. Below are the source text of the view and the results of its execution.

Before we finish brief overview ideas, let's talk a little about how to get additional information about them. In Microsoft SQL Server 7.0 we can use the following system stored procedures:

• You can use the sp_help system stored procedure to obtain view information. For example, sp_help EastTerr will return information about the newly created view;
• You can use the sp_helptext stored procedure to obtain the source text of the view;
• You can use the sp_depends system stored procedure to find the list of tables on which a view depends;
• You can use the sp_rename system stored procedure to rename a view.

In this section, we looked at how to use views to retrieve data that meets certain criteria. However, let's return to the last example. There are four regions in the NorthWind database, and to list the territories of all regions we need four different views. This task could be made easier if we could pass the RegionID value as a parameter. This can be done using a stored procedure, which we'll talk about in the next section.

Stored procedures

A stored procedure is a compiled set of SQL statements stored in a database as a named object and executed as a single piece of code. Stored procedures can accept and return parameters. When a user creates a stored procedure, the server compiles it and places it in a shared cache, after which the compiled code can be used by multiple users. When an application uses a stored procedure, it passes parameters to it if any are required, and the server executes the procedure without recompilation.

Stored procedures can improve application performance. First, compared to regular SQL queries sent from a client application, they require less time to prepare for execution because they are already compiled and stored. Secondly, the network traffic in this case is also less than in the case of transmitting an SQL query, since less data is transmitted over the network. Rice. 3 illustrates a stored procedure call client application.

Stored procedures are automatically recompiled if any changes are made to the objects they affect; in other words, they are always relevant. As mentioned above, stored procedures can accept parameters, allowing different applications to use the same procedure using different sets of input data.

Stored procedures are typically used to maintain referential integrity of data and implement business rules. The latter provides additional flexibility because if business rules change, only the procedure text can be changed without changing the client applications.

To create, change and delete procedures, there are special SQL statements - CREATE PROCEDURE, ALTER PROCEDURE and DROP PROCEDURE. We'll look at them in the next section.

CREATE PROCEDURE clause

The CREATE PROCEDURE clause is used to create a stored procedure. It has the following simplified syntax:

CREATE PROC proc_name [ (@parameter data_type) [= default] ] [...] AS sql_statements

The proc_name argument specifies the name of the stored procedure, which must be unique within the current database. The @parameter argument specifies a procedure parameter. You can specify one or more parameters in the CREATE PROCEDURE clause. If a parameter does not have a default value, it must be passed by the user (or the client application) when calling the procedure. In Microsoft SQL Server 7.0, the number of parameters to a stored procedure should not exceed 1024; by default they can have NULL values.

Note, however, that some generic data access mechanisms may impose additional restrictions by the number of parameters of stored procedures. For example, the BDE driver for Oracle 8 can only work with procedures whose number of parameters does not exceed 10.

The data_type argument specifies the data type for the parameter. The default keyword can be used to set default values ​​- this can be a constant or NULL. If a default value is specified, the procedure can be called without specifying a parameter value. If a procedure uses a parameter with the LIKE keyword, its default value may contain wildcards (%, _, and [^]).

The OUTPUT keyword indicates that this is a return parameter.

The AS keyword specifies the action that the procedure should perform, in the form of any number of SQL statements and statements in a server-specific SQL procedural extension.

The procedure created using the CREATE PROCEDURE clause will be saved in the current database. In Microsoft SQL Server, procedure names are contained in the sysobjects system table, and source text is contained in the syscomments table.

To modify a previously created stored procedure, you must use the ALTER PROCEDURE clause, which is briefly described in the next section.

DROP PROCEDURE offer

This clause is used to remove stored procedures from the database. The DROP PROCEDURE clause takes one argument, the name of the procedure to be dropped.

When you delete a stored procedure, information about it is removed from the sysobjects and syscomments system tables.

Creating and Using Stored Procedures

In the section on views, we pointed out that it would be convenient if we could pass a parameter to the view containing a RegionID value to select one of the four regions in the NorthWind database. Let's take another look at a query that returns a list of territories in a region:

SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = 1

To select a different region, we need to change the condition in the WHERE clause in the last line of the query. Therefore, if we use a variable (let's call it RegID), we can select one of the four regions without changing other parts of the query.

There are four regions in the NorthWind database, numbered 1 through 4. This means that the RegID variable must be an integer type. The stored procedure code is given below:

CREATE PROCEDURE ShowRegion @RegID int AS SELECT Territories.TerritoryDescription, Region.RegionDescription FROM Territories INNER JOIN Region ON Territories.RegionID = Region.RegionID WHERE Territories.RegionID = @RegID

Notice that we left almost all of the SELECT query text intact (it's in italics) and only added a CREATE PROCEDURE clause with the name of the newly created stored procedure (on the first line), a parameter declaration (on the second line), and the AS keyword indicating at the beginning of sentences that actually perform actions.

The result of executing the created procedure in SQL Server Query Analyzer for RegID =2 is shown in Fig. 3.

Obviously, we can use stored procedures for more than just implementing advanced versions of views or smart SELECT queries. Stored procedures provide mechanisms that allow you to automate many routine tasks.

In Microsoft SQL Server 7.0 we can also use system stored procedures to work with regular stored procedures:

• sp_stored_procedures - shows a list of stored procedures;
• sp_helptext - shows the source text of the stored procedure;
• sp_depends - shows information about the dependencies of stored procedures;
• sp_procoption - sets or sets options for stored procedures;
• sp_recompile - recompiles the procedure at the time of its next call;
• sp_rename - changes the name of the procedure.

System Stored Procedures

Since we are talking about Microsoft SQL Server, it is worth noting the huge number of system stored procedures implemented in it. System stored procedure names begin with SP_ or XP_ and are stored in the master database. We have already described above some of the commonly used system stored procedures.

Please note that triggers should not return data to the user.

There are two special tables that can be used in the CREATE TRIGGER clause. For example, the deleted and inserted tables have the same structure as the table on which the trigger is defined, and contain the old and new values ​​of records modified by the user. For example, we can use the following SQL statement to find deleted records:

SELECT * FROM deleted

In table Figure 3 shows the contents of the deleted and inserted tables for all possible data changes.

To change an existing trigger, use the ALTER TRIGGER clause. We'll talk about it in the next section.

First, we need to add two new fields to the table that will contain this information. Let's call them UpdatedBy (the name of the manager who last updated the record) and UpdatedWhen (the time when the record was changed). Next, let's create a trigger called KeepTrack. Here is his code:

CREATE TRIGGER KeepTrack ON Customers FOR INSERT, UPDATE AS UPDATE Customers SET Customers.UpdatedBy = USER_NAME(), Customers.UpdatedWhen = GETDATE() FROM inserted, Customers WHERE inserted.CustomerID = Customers.CustomerID

As you can see from the trigger's source code, it is executed after every INSERT and UPDATE operation on the Customers table. This trigger will store the name of the manager (database user) in the Customers.UpdatedBy field and the date and time of the change in the Customers.UpdatedWhen field. This data is retrieved from the inserted temporary table.

As you can see, this trigger allows you to monitor changes and insertion of new records in the table.

Before we finish this brief overview of triggers, we should tell you where you can find information about the available triggers. The sysobjects table stores information about triggers and their types, and the syscomments table contains their source text.

Conclusion

In this part, we looked at several types of database objects - stored procedures, views and triggers. We learned the following:

• A view is a virtual table, typically created as a subset of columns from one or more tables. The CREATE VIEW clause is used to create a view, the ALTER VIEW clause is used to modify it, and the DROP VIEW clause is used to delete it.
• A stored procedure is a compiled set of SQL statements stored in a database as a named object and executed as a single piece of code. The CREATE PROCEDURE clause is used to create a stored procedure, ALTER PROCEDURE is used to modify it, and DROP PROCEDURE is used to delete it.
• A trigger is a special type of stored procedure that is automatically called when data in a particular table is added, deleted, or modified using an INSERT, DELETE, or UPDATE SQL statement. Triggers are created using the CREATE TRIGGER clause. To change a trigger, use the ALTER TRIGGER clause, and to delete it, use the DROP TRIGGER clause.

ComputerPress 12"2000

Stored SQL procedures are an executable software module that can be stored in the form of various objects. In other words, it is an object that contains SQL statements. These stored procedures can be executed in the application client to obtain good performance. In addition, such objects are often called from other scripts or even from some other section.

Introduction

Many people believe that they are similar to various procedures (respectively, except MS SQL). Perhaps this is true. They have similar parameters and can produce similar values. Moreover, in some cases they touch. For example, they are combined with DDL and DML databases, as well as user functions (codenamed UDF).

In reality, SQL stored procedures have wide range advantages that set them apart from similar processes. Security, programming flexibility, productivity - all this attracts more and more users working with databases. The peak popularity of procedures occurred in 2005-2010, when a program from Microsoft called “SQL Server Management Studio” was released. With its help, working with databases has become much easier, more practical and convenient. From year to year, this one gained popularity among programmers. Today it is an absolutely familiar program, which for users who “communicate” with databases is on a par with Excel.

When a procedure is called, it is instantly processed by the server itself without unnecessary processes or user intervention. After this, you can carry out any deletion, execution, or modification. The DDL operator is responsible for all this, who alone performs the most complex actions to process objects. Moreover, all this happens very quickly, and the server is not actually loaded. This speed and performance allows you to very quickly transfer large amounts of information from the user to the server and vice versa.

To implement this technology for working with information, there are several programming languages. These include, for example, PL/SQL from Oracle, PSQL in the InterBase and Firebird systems, as well as the classic Microsoft Transact-SQL. All of them are designed for creating and executing stored procedures, which allows large database processors to use their own algorithms. This is also necessary so that those who manage such information can protect all objects from unauthorized access by third parties and, accordingly, the creation, modification or deletion of certain data.

Productivity

These database objects can be programmed in a variety of ways. This allows users to choose the type of method used that is most suitable, saving effort and time. In addition, the procedure is processed itself, which avoids the huge time spent on communication between the server and the user. Also, the module can be reprogrammed and changed in the desired direction at absolutely any time. It is especially worth noting the speed with which the SQL stored procedure is launched: this process occurs faster than others similar to it, which makes it convenient and universal.

Safety

This type of information processing differs from similar processes in that it guarantees increased security. This is ensured by the fact that access to procedures by other users can be completely excluded. This will allow the administrator to carry out operations with them independently, without fear of interception of information or unauthorized access to the database.

Data transfer

The relationship between the SQL stored procedure and the client application is the use of parameters and return values. The latter does not have to pass the data to the stored procedure, but this information (mainly at the user's request) is processed for SQL. After the stored procedure has completed its work, it sends data packets back (but, again, optionally) to the application that called it, using various methods, with the help of which both a call to a stored SQL procedure and a return can be made, for example:

Transferring data using an Output type parameter;

Passing data using the return operator;

Passing data using the select operator.

Now let’s figure out what this process looks like from the inside.

1. Create an EXEC stored procedure in SQL

You can create a procedure in MS SQL (Managment Studio). After the procedure is created, it will be listed in the programmable node of the database, in which the creation procedure is executed by the operator. To execute, SQL stored procedures use an EXEC process that contains the name of the object itself.

When you create a procedure, its name appears first, followed by one or more parameters assigned to it. Parameters may be optional. After the parameter(s), that is, the body of the procedure, have been written, some necessary operations need to be performed.

The point is that a body can have local variables located in it, and these variables are also local in relation to procedures. In other words, they can only be viewed within the body of a Microsoft SQL Server procedure. Stored procedures in this case are considered local.

So, to create a procedure, we need the name of the procedure and at least one parameter as the body of the procedure. Note that a great option in this case is to create and execute a procedure with the schema name in the classifier.

The body of the procedure can be of any kind, such as creating a table, inserting one or more rows of a table, establishing the type and nature of the database, and so on. However, the procedure body restricts certain operations from being performed within it. Some of the important limitations are listed below:

The body should not create any other stored procedure;

The body should not create a false impression of the object;

The body should not create any triggers.

2. Setting a variable in the body of the procedure

You can make variables local to the body of the procedure, and then they will reside exclusively within the body of the procedure. It is good practice to create variables at the beginning of the stored procedure body. But you can also set variables anywhere in the body of a given object.

Sometimes you may notice that several variables are set on one line, and each variable parameter is separated by a comma. Also note that the variable is prefixed with @. In the body of the procedure you can set the variable to wherever you want. For example, the @NAME1 variable may be declared near the end of the procedure body. To assign a value to a declared variable, a set of personal data is used. Unlike the situation where more than one variable is declared on the same line, in this situation only one set of personal data is used.

Users often ask the question: “How to assign multiple values ​​in one statement in the body of a procedure?” Well. It's an interesting question, but it's much easier to do than you think. Answer: Using pairs such as "Select Var = value". You can use these pairs by separating them with a comma.

In the most various examples people show creating a simple stored procedure and executing it. However, a procedure can accept parameters such that the process calling it will have values ​​close to it (but not always). If they coincide, then corresponding processes begin inside the body. For example, if you create a procedure that will accept a city and region from the caller and return data about how many authors belong to the corresponding city and region. The procedure will query the database's author tables, such as Pubs, to perform this author count. To get these databases, for example, Google downloads the SQL script from the SQL2005 page.

In the previous example, the procedure takes two parameters, which English conditionally will be called @State and @City. The data type matches the type defined in the application. The body of the procedure has internal variables @TotalAuthors, and this variable is used to display the number of authors. Next comes the query selection section, which calculates everything. Finally, the calculated value is printed in the output window using the print statement.

How to execute a stored procedure in SQL

There are two ways to perform the procedure. The first way shows, by passing parameters, how a comma-separated list is executed after the procedure name. Let's say we have two values ​​(as in the previous example). These values ​​are collected using the @State and @City procedure parameter variables. In this method of passing parameters, order is important. This method is called ordinal argument passing. In the second method, the parameters are already directly assigned, and in this case the order is not important. This second method is known as passing named arguments.

The procedure may deviate slightly from the typical one. Everything is the same as in the previous example, but only here the parameters are shifted. That is, @City is stored first, and @State is stored next to the default value. The default parameter is usually highlighted separately. SQL stored procedures are passed as just parameters. In this case, provided, the parameter "UT" replaces the default value "CA". In the second execution, only one argument value is passed for the @City parameter, and the @State parameter takes on the default value of "CA". Experienced programmers advise that all variables should be located towards the end of the parameter list by default. Otherwise execution is not possible, and then you have to work with passing named arguments, which is longer and more complex.

4. SQL Server Stored Procedures: Return Methods

There are three important ways to send data in a called stored procedure. They are listed below:

Return the value of a stored procedure;

Stored procedure parameter output;

Selecting one of the stored procedures.

4.1 Returning values ​​from SQL stored procedures

In this technique, a procedure assigns a value to a local variable and returns it. A procedure can also directly return a constant value. In the following example, we created a procedure that returns the total number of authors. If you compare this procedure with the previous ones, you can see that the print value is reversed.

Now let's see how to execute a procedure and print its return value. Executing the procedure requires setting a variable and printing, which is carried out after this entire process. Note that instead of a print statement, you can use a Select statement, such as Select @RetValue as well as OutputValue.

4.2 SQL Stored Procedure Parameter Output

The response value can be used to return a single variable, which is what we saw in the previous example. Using the Output parameter allows a procedure to send one or more variable values ​​to the caller. The output parameter is designated precisely by this keyword “Output” when creating a procedure. If a parameter is given as an output parameter, then the procedure object must assign a value to it. SQL stored procedures, examples of which can be seen below, in this case return with summary information.

In our example there will be two output names: @TotalAuthors and @TotalNoContract. They are indicated in the list of parameters. These variables assign values ​​within the body of the procedure. When we use output parameters, the caller can see the value set inside the body of the procedure.

Also, in the previous scenario, two variables are declared to see the values ​​that the MS SQL Server stored procedures set in the output parameter. Then the procedure is performed by supplying the normal value of the “CA” parameter. The following parameters are output parameters and hence the declared variables are passed in the specified order. Note that when passing variables, the output keyword is also set here. After the procedure is completed successfully, the values ​​returned by the output parameters are displayed in the message box.

4.3 Selecting one of the SQL stored procedures

This technique is used to return a set of values ​​as a data table (RecordSet) to the calling stored procedure. In this example, the SQL stored procedure with @AuthID parameters queries the Authors table by filtering the records returned using that @AuthId parameter. The Select statement decides what should be returned to the caller of the stored procedure. When the stored procedure is executed, the AuthId is passed back. This procedure here always returns only one record or none at all. But a stored procedure does not have any restrictions on returning more than one record. It is not uncommon to see examples where returning data using selected parameters involving calculated variables occurs by providing multiple totals.

In conclusion

A stored procedure is a fairly serious program module that returns or passes, and also sets the necessary variables thanks to the client application. Because the stored procedure runs itself on the server, huge amounts of data exchange between the server and the client application (for some calculations) can be avoided. This allows you to reduce the load on SQL server, which, of course, benefits their holders. One of the subtypes is T SQL stored procedures, but their study is necessary for those who create impressive databases. There are also a large, even huge number of nuances that can be useful when studying stored procedures, but this is needed more for those who plan to get involved in programming, including professionally.

1. Include a line in your procedures - SET NOCOUNT ON: With each DML expression, SQL server carefully returns us a message containing the number of processed records. This information It may be useful to us while debugging the code, but after that it will be completely useless. By writing SET NOCOUNT ON, we disable this function. For stored procedures containing several expressions or\or loops this action can give a significant performance boost because the amount of traffic will be significantly reduced.

CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Here is the procedure code
SELECT column1 FROM dbo.TblTable1
--Toggle SET NOCOUNT to initial state
SET NOCOUNT OFF;
GO

2. Use the schema name with the object name: Well, I think it’s clear. This operation tells the server where to look for objects and instead of randomly rummaging through its bins, it will immediately know where it needs to go and what to take. With a large number of databases, tables and stored procedures, it can significantly save our time and nerves.

SELECT * FROM dbo.MyTable --This is good to do
-- Instead of
SELECT * FROM MyTable --And doing this is bad
--Procedure call
EXEC dbo.MyProc --Good again
--Instead of
EXEC MyProc --Bad!

3. Do not use the “sp_” prefix in the name of your stored procedures: If our procedure name starts with "sp_", SQL Server will look in its main database first. The point is that given prefix used for private internal server stored procedures. Therefore, its use may lead to additional costs and even incorrect results if a procedure with the same name as yours is found in its database.

4. Use IF EXISTS (SELECT 1) instead of IF EXISTS (SELECT *): To check for the existence of a record in another table, we use the IF EXISTS statement. This expression returns true if at least one value is returned from the internal expression, it doesn’t matter “1”, all columns or a table. The returned data is basically not used in any way. Thus, to compress traffic during data transmission, it is more logical to use “1”, as shown below:

IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = "MyTable" AND type = "U")

5. Use TRY-Catch to catch errors: Before 2005 servers, after each request, a huge number of error checks were written in the procedure. More code always consumes more resources and more time. With 2005 SQL Server, a more correct and convenient way solutions to this problem:

BEGIN TRY
--code
END TRY
BEGIN CATCH
--error catching code
END CATCH

Conclusion
Basically, that's all I have for today. I repeat once again that here are only those techniques that I personally used in my practice, and I can vouch for their effectiveness.

P.S.
My first post, don't judge too harshly.