SQL stored procedures are an executable program module that can be stored in a database as various objects. In other words, this 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 scripts or even from some other section.
Introduction
Many people think that they are similar to the procedures of various high-level programming languages (respectively, except for MS SQL). Perhaps this is indeed so. They have similar parameters, they can produce similar values. Moreover, in some cases they are in contact. For example, they are combined with DDL and DML databases, as well as with user functions (code name - UDF).
In reality, SQL stored procedures have a wide range of benefits that set them apart from similar processes. Security, programming variability, productivity - all this attracts users working with databases, more and more. The peak of the popularity of procedures came 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 more convenient. From year to year, this method of transmitting information has gained popularity among programmers. Today, MS SQL Server is an absolutely familiar program that, 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 and user intervention. After that, you can carry out any actions with information: deletion, execution, change. The DDL operator is responsible for all this, which alone performs the most complex actions for processing objects. And all this happens very quickly, and the server is actually not loaded. This speed and performance allows you to 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 the Oracle, PSQL database management system 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 of the database to use their own algorithms. This is necessary so that those who manage such information can protect all objects from unauthorized access by third parties and, accordingly, create, modify 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 the most suitable, which saves time and effort. In addition, the procedure itself is processed, which avoids the enormous time spent on exchanges between the server and the user. Also, the module can be reprogrammed and changed in the right direction at any time. Particularly noteworthy is the speed with which the stored SQL procedure is launched: this process is faster than others similar to it, which makes it convenient and universal.
Security
This type of information processing differs from similar processes in that it guarantees increased security. This is ensured by the fact that other users' access to procedures can be completely and completely excluded. 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 have to pass data to the stored procedure, but this information (mainly at the request of the user) is processed for SQL. After the stored procedure has completed its work, it sends data packets back (but, again, if desired) to the application that called it, using various methods that can be used to either call the SQL stored procedure or return, for example:
- data transfer using a parameter of type Output;
- data transfer using the return operator;
- data transfer using the selection operator.
Now let's see how this process looks from the inside.
1. Creating 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 database node, in which the creation procedure is performed by the operator. To execute stored SQL procedures, they 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 are assigned to it. Parameters may be optional. After the parameter (s), that is, the body of the procedure, are written, it is necessary to carry out some necessary operations.
The fact is that the body can have local variables located in it, and these variables are local also with respect to the procedures. In other words, they can only be viewed inside the body of a Microsoft SQL Server procedure. Stored procedures are then considered local.
Thus, to create a procedure, we need the name of the procedure and at least one parameter as the body of the procedure. Note that an excellent option in this case is to create and execute a procedure with the name of the circuit in the classifier.
The body of the procedure can be any kind of SQL statement, for example, such as creating a table, inserting one or more rows of the table, establishing the type and nature of the database, and so on. Nevertheless, the body of the procedure limits 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 about the object;
- the body should not create any triggers.
2. Setting a variable to the procedure body
You can make variables local to the body of the procedure, and then they will be exclusively inside the body of the procedure. It is good practice 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 may notice that several variables are set on the same 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 the variable where you want. For example, the variable @ NAME1 may be declared closer to the end of the body of the procedure. In order to assign a value to a declared variable, a personal data set is used. Unlike a situation where more than one variable is declared on one line, in this situation only one set of personal data is used.
Often users ask the question: "How to assign multiple values in one statement in the body of the procedure?" Well. The question is interesting, but it is 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.
3. Creating a SQL stored procedure
In a wide variety of examples, people show how to create a simple stored procedure and execute it. However, the procedure can take such parameters that the process calling it will have values 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 the city and region from the caller and return information about how many authors belong to the corresponding city and region. The procedure will query the table of authors of the database, for example, 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 in English will be conditionally called @State and @City. The data type corresponds to the type defined in the application. The body of the procedure has internal variables @TotalAuthors (total authors), and this variable is used to display their number. Next, a query selection section appears that counts everything. Finally, the calculated value is displayed 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 path shows, passing parameters, how the 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. In this method of passing parameters, order is important. This method is called sequential argument passing. In the second method, parameters are already directly assigned, in which case the order is not important. This second method is known as passing named arguments.
The procedure may 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 highlighted separately. SQL stored procedures pass as simple parameters. In this case, provided the “UT” parameter replaces the default value “CA”. In the second execution, only one argument value passes for the @City parameter, and the @State parameter accepts the default value “CA”. Experienced programmers recommend that all variables be located near the end of the parameter list by default. Otherwise, execution is not possible, and then you must work with passing named arguments, which is longer and more complicated.
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 the stored procedure;
- parameter output of stored procedures;
- selection of one of the stored procedures.
4.1 Returning SQL Stored Procedure Values
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 value for printing is replaced by the opposite.
Now let's see how to execute the procedure and print the value returned to it. The execution of the procedure requires the establishment of a variable and printing, which is carried out after the whole process. Note that instead of the print statement, you can use the Select statement, for example, Select @RetValue, as well as OutputValue.
4.2 SQL Stored Procedure Parameter Output
The response value can be used to return a single variable, as we saw in the previous example. Using the Output parameter allows the procedure to send one or more variable values to the caller. The output parameter is indicated exactly by this keyword “Output” when creating the procedure. If the parameter is specified as the 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 are returned with the summary information.
In our example, there will be two output names: @TotalAuthors and @TotalNoContract. They are indicated in the parameter list. These variables assign values within the body of the procedure. When we use the output parameters, the caller can see the value set inside the body of the procedure.
In addition, 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 parameter “CA”. The following parameters are output and, therefore, declared variables are passed in the established order. Note that when passing variables, the output keyword is also set here. After the procedure is successful, the values returned by the output parameters are displayed in the message box.
4.3 Choosing one of the SQL stored procedures
This technique is used to return a data set (RecordSet) to the calling stored procedure. In this SQL example, a stored procedure with the @AuthID parameters queries the Authors table by filtering the returned records using this @AuthId parameter. The Select statement decides what should be returned to the caller of the stored procedure. When the stored procedure is executed, 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 returning more than one record. You can often find examples in which the return of data using selected parameters with the participation of calculated variables occurs by providing several totals.
Finally
A stored procedure is a fairly serious software module that returns or passes, as well as sets the necessary variables thanks to the client application. Since the stored procedure is executed on the server itself, 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 the SQL server, which, of course, is in the hands of their holders. One of the subspecies is the T SQL stored procedures, but those who need to create impressive databases need to learn them. There is 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 work closely on programming, including professionally.