SQL Stored Procedures: Creating and Using

Stored procedures SQL is an executable program module that can be stored in a database as various objects. In other words, it is an object that contains SQL statements. These stored procedures can be executed in the application client to get good performance. In addition, such objects are often called from other scenarios or even from some other section.

Introduction

Many believe that they are similar to the procedures of various high-level programming languages (respectively, except for MS SQL). Perhaps, it is really so. They have similar parameters, they can give similar values. Moreover, in a number of cases they are in contact. For example, they are combined with databases DDL and DML, as well as with user functions (code name - UDF).

In fact, SQL stored procedures have a wide range of advantages that distinguish them from similar processes. Security, variability of programming, productivity - all this attracts users working with databases, more and more. The peak of the popularity of procedures occurred in 2005-2010, when Microsoft released a program called SQL Server Management Studio. With its help, working with databases has become much easier, more practical and more convenient. From year to year this method of information transfer gained popularity among programmers. Today, MS SQL Server is absolutely familiar program, which for users who "communicate" with databases, has risen along with "Excel".

When a procedure is called, it is instantly processed by the server itself, without unnecessary processes and user intervention. After this, you can perform any actions with information: deletion, execution, change. For all this is the responsibility of the DDL operator, which alone performs the most complex processing of objects. And 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 work with information, there are several programming languages. These include, for example, PL / SQL from the Oracle database management system , PSQL in InterBase and Firebird systems, as well as the classic "Microsoft Transact-SQL". All of them are designed to create and execute stored procedures, which allows using large algorithms in large database handlers. It is also necessary for those who manage such information to protect all objects from unauthorized access by third parties and, accordingly, create, change or delete certain data.

Productivity

These database objects can be programmed in various ways. This allows users to choose the type of method used, which will be most appropriate, which saves time and effort. In addition, the procedure itself is processed, which avoids the huge time spent on exchanging between the server and the user. Also, the module can be reprogrammed and changed in the desired direction at any time. Especially it is worth noting the speed with which SQL stored procedure starts: this process is faster than others, similar to it, which makes it convenient and versatile.

Security

This type of information processing differs from similar processes in that it guarantees increased security. This is ensured by the fact that the access of other users to procedures can be excluded entirely and completely. This will allow the administrator to conduct 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 need to transfer data to the stored procedure, however this information (mainly on the user's request) and processed for SQL. After the stored procedure has completed its work, it sends the data packets back (but, again, if desired) to the application that called it, using various methods that can be used as a call to the stored procedure SQL, and return, for example:

- data transfer using the Output parameter;

- data transfer using the return operator;

- data transfer using the selection operator.

Now let's see how this process looks 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 transferred to a programmable database node in which the creation procedure is performed by the operator. To execute, SQL stored procedures use an EXEC process that contains the name of the object itself.

When creating a procedure, its name appears first, after which one or more parameters assigned to it are produced. Parameters can be optional. After the parameter (s), that is, the body of the procedure, are written, you need to perform some necessary operations.

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

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

The procedure body can have any kind of SQL statements, for example, such as creating a table, inserting one or more table rows, setting the type and nature of the database, and so on. Nevertheless, the body of the procedure restricts the execution of certain operations in 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 idea of the object;

- The body should not create any triggers.

2. Setting a variable in the body of the procedure

You can make the variables local to the body procedure, and then they will be located exclusively inside the body of the procedure. A good practice is to create variables at the beginning of the body of the stored procedure. But also you can set variables anywhere in the body of this object.

Sometimes you will notice that several variables are set in one line, and each variable parameter is separated by a comma. Also note that the variable has the @ prefix. In the body of the procedure, you can set a variable where you want. For example, the variable @ NAME1 can be declared closer to the end of the procedure body. To assign the value of the declared variable, a set of personal data is used. In contrast to the situation when more than one variable is declared in one line, only one set of personal data is used in this situation.

Often, users ask the question: "How to assign multiple values in one operator in the body of the procedure?" Well. The question is interesting, but it's much easier to do than you think. Answer: using pairs such as "Select Var = value". You can use these pairs, separating them with a comma.

3. Creating a SQL Stored Procedure

In a variety of examples, people show how to create a simple stored procedure and execute it. However, the procedure can take such parameters that the calling process will have values that are close to it (but not always). If they coincide, then the corresponding processes begin inside the body. For example, if you create a procedure that will take a city and region from the caller and return data about how many authors are related to the relevant city and region. The procedure will query the database authors tables, for example, Pubs, to perform this author count. To get these databases, for example, Google loads the SQL script from the SQL2005 page.

In the previous example, the procedure takes two parameters, which in English will be conventionally called @State and @City. The data type corresponds to the type defined in the application. The body of the procedure has internal variables @TotalAuthors (all authors), and this variable is used to display their number. Then there is a section for selecting the query, which all counts. Finally, the calculated value is displayed in the output window using the print operator.

How to execute a stored procedure in SQL

There are two ways to perform the procedure. The first path shows passing parameters, as a comma-separated list is executed after the procedure name. Suppose we have two values (as in the previous example). These values are collected using the variables of the @State and @City procedure parameters. Order is important in this way of passing parameters. This method is called ordinal transfer of arguments. 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 the transfer of named arguments.

The procedure can deviate somewhat from the typical. Everything is the same as in the previous example, but only here the parameters are shifted. That is, the @City parameter is stored first, and @State is stored next to the default value. The default setting is usually separate. Stored SQL procedures pass as simple parameters. In this case, under the condition, 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 the default value of "CA". Experienced programmers advise that all variables by default are located closer to the end of the list of parameters. Otherwise, execution is not possible, and then you must work with the transfer of named arguments, which is longer and more difficult.

4. SQL Server Stored Procedures: Return Methods

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

- return the value of the stored procedure;

- output of the stored procedure parameter;

- Select one of the stored procedures.

4.1 Returning values of SQL stored procedures

In this technique, the procedure assigns a value to a local variable and returns it. The 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 to the previous ones, you can see that the value for printing is replaced by the opposite one.

Now let's see how to execute the procedure and output the value returned to it. The execution of the procedure requires setting the variable and printing, which is carried out after all this process. Note that instead of the print statement, you can use the Select operator, for example, Select @RetValue, and also OutputValue.

4.2 Exit the SQL stored procedure parameter

The response value can be used to return one variable, which we saw in the previous example. Using the Output parameter allows the procedure to send one or more variable values to the calling party. The output parameter is designated as this time by this keyword "Output" when creating the procedure. If the parameter is specified as an output parameter, then the procedure object must assign a value to it. Stored SQL procedures, examples of which can be seen below, are then returned with summary information.

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

In addition, in the previous scenario, two variables are declared to see the values that set the stored procedures of MS SQL Server in the output parameter. Then the procedure is performed by supplying the normal value of the parameter "CA". The following parameters are output and, therefore, the declared variables are transmitted in the established order. Note that when passing variables, the output keyword is also specified here. After the procedure is completed successfully, the values returned by the output parameters are displayed on the message window.

4.3 Select 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, a SQL stored procedure with @AuthID parameters queries the Authors table by filtering the returned records using this @AuthId parameter. The Select operator decides what should be returned to the calling stored procedure. When executing a stored procedure, AuthId is passed back. Such a procedure here always returns only one record or none at all. But the stored procedure does not have any restrictions on the return of more than one record. It is often possible to find examples in which the return of data using the selected parameters with the participation of the computed variables occurs by providing several summary values.

Finally

A stored procedure is a fairly serious software module that returns or transmits, and also establishes the necessary variables through the client application. Because the stored procedure is executed on the server itself, data exchange in huge volumes between the server and the client application (for some calculations) can be avoided. This allows you to reduce the load on the SQL server, which, of course, goes hand in hand to their holders. One of the subspecies is T SQL stored procedures, however, they need to be studied by those who are creating impressive databases. There is also a large, even a huge number of nuances that can be useful in studying stored procedures, but this is more for those who plan to engage in programming, including professionally.