Save procedures in SQL Server. Procedures are saved. The creation of the vikonannya procedures that are saved

MySQL 5 has a lot of new features, some of the most common procedures to choose from. In this lesson, I will tell you about those that stinks are, and also about those that stinks can make your life easier.

Entry

The procedure is chosen - the way of encapsulation that is repeated. In the procedures that are saved, you can make changes, manage data flows, and also stop other programming techniques.

The reason for the creation is clear and confirmed by some of the victors. From the other side, if you talk to those who work with them irregularly, then your thoughts will be divided into two opposite flanks. Don't forget about tse.

Behind

• Podіl logics with other programs. Saving procedures encapsulate functionality; tse secure access to data and manage them between different programs.
• Isolation of coristuvachiv in the data base tables. This allows you to give access to the procedures that are saved, but not to the tables themselves.
• Secure the defense mechanism. As far as the previous point, although you can only access your data through the procedures you save, no one else can erase your data through the SQL DELETE command.
• Polypshennya vykonannya as a legacy of short-lived traffic. For additional procedures that are saved, impersonal drinks can be combined.

Proti

• Moving forward to the database server in connection with the fact that most of the work is on the server side, and less - on the client side.
• Bring a lot of things to learn. You need to understand MySQL's syntax in order to write your own routines, which are saved.
• You duplicate the logic of your programs in two places: the server code and the code for the procedures that you save, thereby simplifying the process of manipulating data.
• Migrating from one DBMS to another (DB2, SQL Server, etc.) can lead to problems.

The tool I use is called MySQL Query Browser, which is a standard tool for interacting with databases. The MySQL command line tool is yet another magic choice. I tell you about these reasons why phpMyAdmin does not support all the procedures that are saved.

Before the speech, I have a vicarious elementary structure of the table, so that it would be easy for you to understand this topic. Andje, I tell you about the procedures that are saved, and the stench is done in folding, so that you can understand the bulky structure of the table.

Krok 1: We put the intermediate

The spacer is a character or a string of characters that is chosen to be displayed to the MySQL client so that you have completed writing the SQL script. Tsіlu vіchnіst obzhuvachem buv symbol of specks with a clod. Tim is not less, you can blame problems, shards in the procedures that you save, you can have a small amount of viraziv, you are guilty of such a thing. At this uroch, I have a vicorist row “//” as a mezhuvach.

Krok 2: How to practice with the procedures that are saved

Creation of procedures that are taken

DELIMITER // CREATE PROCEDURE `p2` () LANGUAGE SQL DETERMINISTIC SQL SECURITY DEFINER COMMENT "A procedure" BEGIN SELECT "Hello World!"; END//

The first part of the code creates a procedure that is saved. Nastupna - to avenge neobov'yazkovі parametres. Let's name it somewhere, nareshti, the body of the procedure itself.

Name the procedures to be saved, sensitive to register. You are also not allowed to create a bunch of procedures with the same name. All the procedures that are saved cannot be changed, which change the database itself.

4 characteristics of the procedure to be taken into account:

• Language: For portability reasons, SQL is indicated for abbreviations.
• Deterministic: so the procedure consistently rotates the same result and accepts the same parameters. Price for replication and registration process. Lock value is NOT DETERMINISTIC.
• sql security INVOKER - tse koristuvach, scho call the procedure, scho be saved. DEFINER is the “creator” of the procedure. Promotion value - DEFINER.
• Comment: with the documenting method, the meaning for the locking is ""

Weekly saving procedures

In order to call out the procedure that is being saved, it is necessary to charge keyword CALL, and then name the procedure, and in the arms, specify the parameters (change or change values). Obov'yazkovі temples.

CALL stored_procedure_name (param1, param2, ....) CALL procedure1(10 , "string parameter", @parameter_var);

Changing the procedures that are taken

MySQL can use ALTER PROCEDURE to change procedures, but ALTER PROCEDURE can be used to change more than one characteristic. If you need to change the parameters or the body of the procedure, then delete it and create it again.

Vidalennya procedures that are taken

DROP PROCEDURE IF EXISTS p2;

Tse is a simple team. Viraz IF EXISTS grants a pardon, as there is no such procedure.

Krok 3: Parameters

Let's see how it is possible to pass parameters to the procedure, which are saved.

• CREATE PROCEDURE proc1 (): empty parameter list
• CREATE PROCEDURE proc1 (IN varname DATA-TYPE): One input parameter. The word IN is neobov'yazkove, to that the parameters for locking - IN (input).
• CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): one parameter to rotate.
• CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): one parameter to enter and turn around at the same time.

Naturally, you can set a number of parameters in different types.

Butt parameter IN

DELIMITER // CREATE PROCEDURE `proc_IN` (IN var1 INT) BEGIN SELECT var1 + 2 AS result; END//

OUT parameter butt

DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "Tim є"; END //

INOUT parameter butt

DELIMITER // CREATE PROCEDURE `proc_INOUT` (OUT var1 INT) BEGIN SET var1 = var1 * 2; END //

Croc 4: Changes

At once I will teach you how to change and save your middle procedures. You are responsible to voice them explicitly on the cob to the BEGIN / END block, together with the data types. As soon as you voiced the change, you can twist it there, de change sessions, literals or column names.

The syntax of the voiceless change looks like this:

DECLARE varname DATA-TYPE DEFAULT defaultvalue;

Let's voice the sprat of change:

DECLARE a, b INT DEFAULT 5; DECLARE str. VARCHAR(50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;

Work with change

Just as you have announced the change, you can insert the values ​​behind the help of the SET or SELECT commands:

DELIMITER // CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20)) BEGIN DECLARE a, b INT DEFAULT 5; DECLARE str. VARCHAR(50); DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INSERT INTO table1 VALUES(a); SET str = "I am a string"; SELECT CONCAT(str, paramstr), today FROM table2 WHERE b >= 5; END //

Krok 5: Structures of keruvannya by streams

MySQL supports IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs for flow control within a saved procedure. We can look at how victorious IF, CASE and WHILE, shards of the stink are most victorious.

IF construction

For additional constructions IF we can win over the task, what to take revenge on:

DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; IF variable1 = 0 THEN SELECT variable1; END IF; IF param1 = 0 THEN SELECT "Parameter value = 0"; ELSE SELECT "Parameter value<>0"; END IF; END //

CASE design

CASE is yet another method of mind-checking and selection of an appropriate solution. Tse guiding way replace impersonal structures IF. The construction can be described in two ways, giving flexibility in the control of the impersonal intelligence of the mind.

DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE variable1 WHEN 0 THEN INSERT INTO table1 VALUES (param1); WHEN 1 THEN INSERT INTO table1 VALUES(variable1); ELSE INSERT INTO table1 VALUES(99); END CASE; END //

DELIMITER // CREATE PROCEDURE `proc_CASE` (IN param1 INT) BEGIN DECLARE variable1 INT; SET variable1 = param1 + 1; CASE WHEN variable1 = 0 THEN INSERT INTO table1 VALUES(param1); WHEN variable1 = 1 THEN INSERT INTO table1 VALUES(variable1); ELSE INSERT INTO table1 VALUES(99); END CASE; END //

WHILE construction

Technically, there are three types of loops: the WHILE loop, the LOOP loop and the REPEAT loop. You can also organize a cycle for the help of the “Darth Vader” programming technique: Viraz GOTO. Axis butt loop:

DELIMITER // CREATE PROCEDURE `proc_WHILE` (IN param1 INT) BEGIN DECLARE variable1, variable2 INT; SET variable1 = 0; WHILE variable1< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

Krok 6: Cursori

Cursors are selected for navigating through the set of rows rotated by the input, as well as the skin row wrapping.

MySQL honors cursors in procedures that are saved. Axis is a short syntax to match the cursor.

DECLARE cursor-name CURSOR FOR SELECT...; /*Handling the cursor for that one */ DECLARE CONTINUE HANDLER FOR NOT FOUND /*What work if there are no more records*/ OPEN cursor-name; /*Find cursor*/ FETCH cursor-name INTO variable [, variable]; /*Set a change value that is better than the threaded value of the column*/ CLOSE cursor-name; /*Close cursor*/

For this application, we will carry out some simple operations with the selected cursor:

DELIMITER // CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSOR FOR SELECT col1 FROM table1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; OPEN cur1; SET b = 0; SET c = 0; WHILE b = 0 DO FETCH cur1 INTO a; IF b = 0 THEN SET c = c + a; END IF; END WHILE; CLOSE cur1; SET param1 = c; END //

Cursors have three powers, which you need to understand in order to avoid unfavorable results:

• Not sensitive: the cursor, which was displayed once, does not display changes in the table, which was changed later. Actually, MySQL does not guarantee that the cursor will update, so don't rely on it.
• Readable: Cursors cannot be changed.
• Without rewinding: the building cursor passes only in one straight line - forward, you can not skip rows without selecting them.

Visnovok

In this lesson, I have taught you the basics of work, the procedures that are taken care of, and some specific powers that apply to her. Obviously, you will need to bury your knowledge in such things as security, SQL virus and optimization, first of all, a MySQL procedure guru.

You are responsible for assistance, as a matter of fact, we will give you a variety of procedures that are saved from your specific addendum, and only then we will create more than necessary procedures. Zagalom I vicorist procedure; in my opinion, їх varto to be used in projects after їхної security, maintenance of the code and the overt design. Until then, don't forget that you have to work on MySQL procedures. Check for improvements that are worth the functionality and improvement. Please, do not waste your thoughts.

Last updated: 14.08.2017

Quite often, an operation with data represents a set of instructions, as it is necessary to follow the singing sequence. For example, when adding a purchase to a product, it is necessary to enter data to the tables. However, it is necessary to reconsider before cim, and what is a commodity that is bought in reality. Possibly, when you happen to convert the lowly dodatkovyh minds. That is actually the process of buying goods ohoplyuє kіlka dіy, yakі may vykonuvatsya at pevnіy poslіdovnostі. І in this way more optimally encapsulates all things in one object - procedure to be taken(stored procedure).

Tobto on the merits of the procedure, which are taken care of, represents a set of instructions, as they are consummated as a single whole. The procedures themselves, which are being saved, allow for simple complex operations and to blame them in a single object. Change the process of buying goods, vіdpovіdno enough to change the procedure code. This procedure also asks for a code.

Also, the procedures that are saved allow you to block access to data in tables and, by the same token, change the immovability of these or unrecognized, unimportant data.

Another important aspect is productivity. Save procedures sound better, lower sound SQL-instructions. All that the procedure code is compiled once during the first run, and then saved in a compiled form.

To create a procedure, the command CREATE PROCEDURE or CREATE PROC is stopped.

In this way, the procedure that is being saved has three key features: ease of code, security and productivity.

For example, let the data base have a table, so that you can collect data about the goods:

CREATE TABLE Products (Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL);

Let's create a procedure that is taken for examining data from the given tables:

USE productsdb; GO CREATE PROCEDURE ProductSummary AS SELECT ProductName AS Product, Manufacturer, Price FROM Products

However, the CREATE PROCEDURE command is responsible for calling the original package, but after the USE command, in order to install the streaming database, the GO command is called to designate a new package.

After the name of the procedure, you can use the AS keyword.

To add a procedure body to a script, the procedure code is often placed in the BEGIN...END block:

USE productsdb; GO CREATE PROCEDURE ProductSummary AS BEGIN SELECT ProductName AS Product, Manufacturer, Price FROM Products END;

After adding the procedures, we can work with database node in SQL Server Management Studio in sub node Programmability -> Stored Procedures:

We can also perform the procedure through the visual interface.

Vikonanny procedures

For the procedure to be saved, the command EXEC or EXECUTE is called:

EXEC Product Summary

VIDALENNYA PROCEDURES

To drop a procedure, the DROP PROCEDURE command is invoked:

DROP PROCEDURE ProductSummary

Saving SQL procedures - hacking a program module, which can be saved from looking at different objects. In other words, the object that has the SQL-instructions. Qi procedures that are saved can be checked in the client application programs enjoy good productivity. In addition, such objects are often called out from other scenarios, or to inspire some other kind of distribution.

Entry

It is very important for someone to know that they are similar to different procedures (depending on MS SQL). Maybe, you're right. They may have similar parameters, they can see similar values. Moreover, a number of vipadkiv stench stick. For example, stinks are associated with the DDL and DML databases, as well as with the core functions (codename - UDF).

Indeed, saving SQL procedures may have a wide range of benefits, as we see among such processes. Bezpeka, variability of programming, productivity - all add koristuvachiv, yak pratsyyut іz data bases, daedals more. The peak of popularity of procedures fell on 2005-2010, if the program was released under the name "Microsoft" under the name SQL Server Management Studio. With the help of data bases, it has become much simpler, more practical and more convenient. Rick y r_k is gaining popularity among programmers. Today, it is an absolutely sound program, like for koristuvachivs, like “connecting” with data bases, it has become like “Exel”.

When the call is made, the procedure will be processed by the server itself without zayvih protsesіv i vtruchannya koristuvach. If so, you can zdiyasnyuvati be-like a distant, vikonannya, change. For all that, you need a DDL-operator, which is able to work independently on the most complex tasks from object processing. Moreover, it seems to be very fast, and the server is actually not navantazhuetsya. Such speed and productivity allow you to quickly transfer large amounts of information from the source to the server and away.

For the implementation of this technology, robots with information are based on mov programming. Before them, you can see, for example, PL / SQL like Oracle, PSQL in InterBase and Firebird systems, as well as the classic “Microsoft” Transact-SQL. All stinks are recognized for creating and concluding procedures that are saved, that allow mastering algorithms in great databases. It is necessary and for the fact that you, who create such information, could protect all objects from unauthorized access of third-party systems, that, obviously, created, change or change the view of quiet or other data.

Productivity

Qi database objects can be programmed with different paths. Tse allows coristuvachas to choose the type of vicorous method, which will be the most suitable, which will save the strength of that hour. In addition, the procedure itself is being processed, which allows you to hide the great hourly exchange rates between the server and the server. Also, the module can be reprogrammed and changed at any time. Particularly, it is important to note the flexibility, for which the launch of the saved SQL procedure is expected: this process is more similar to others, similar to it, to make it efficient and universal.

Bezpeka

This type of processing of information is subject to similar processes that we guarantee promotion of security. Tse bezpechuetsya for rahunok that access іnshih koristuvachіv to procedures can be turned off more and more. Tse to allow the administrator to carry out operations with them independently, not being afraid for the transfer of information or unauthorized access to the data base.

Transfer of data

The connection between the SQL procedure, which is taken, and the client's addendum is based on different parameters and values, which are rotated. Let's not obov'yazkovo transfer data to the procedure, which is saved, the information is processed (mainly on demand) and processed for SQL. Since the procedure, which is being saved, has completed its work, we send the data packets back (ale, I know, for the bugs) to the addendum, so that by clicking on yoga, victorious methods, for the help of which you can find out how to save the SQL procedure, so and turn, for example:

Passing data for an additional parameter to the Output type;

Transfer of data for the assistance of the return operator;

The strength of the tribute for the assistance of the operator to the choice.

And now let's take a look, as if looking at the whole process in the middle.

1. Creating an EXEC Saved Procedure in SQL

You can create a procedure in MS SQL (Managment Studio). After the procedure is created, it will be redeemed from the programming of the data base, in which the procedure will be created by the operator. To save the SQL procedures, use an EXEC-process to avenge the name of the object itself.

When the procedure is combined, the name is declared first, after which one or more parameters assigned to you are carried out. Parameters can be non-binding. Since the parameter(s) for the body of the procedure will be written, it is necessary to carry out the necessary operations.

On the right, in that the body can be changed locally, roztashovani in nіy, and tsі change locally also according to the date before the procedures. In other words, they can only be seen in the middle of the body of a Microsoft SQL Server procedure. Saving procedures at times are vvazhayutsya local.

Thus, in order to create a procedure, we need the name of the procedure and at least one parameter in the body of the procedure. Catch the respect that the most important option for such a time is the creation of the procedure for the name of the scheme at the classifier.

The body of the procedure may be some kind of example, such as the creation of tables, the insertion of one or a row of rows in tables, the type and nature of the data base, and so on. The proteo body of the procedure is between the victories of the current operations in the new one. Acts of important delimitation have been built below:

The body is not guilty of doing any other procedure that is being saved;

The body is not guilty of creating a hell of a revelation about the object;

The body is not guilty of creating everyday triggers.

2. Installing a change in the body of the procedure

You can change them local to the body of the procedure, and the stinks will be changed only in the middle of the body of the procedure. Good practice is the creation of procedures that are changed on the cob of the body, which are saved. But it is also possible to install changes in any place in the body of a given object.

In other words, you can note that the number of changers is installed in one row, and the skin change parameter is water-reinforced. Also, please respect that you can change the prefix @. You can insert a change for the type of procedure, wherever you want. For example, changing @NAME1 can be voiced closer to the end of the procedure. In order to give the meaning of the voiceless change, a set of special data is collected. On the basis of the situation, if more than one change is voiced in one row, in such a situation there is less than one set of special data.

Quite often, quizzes ask: "How do you recognize a small value in one statement in a procedure title?" Well. Feeding the cicava, but it’s easier to make it richer, you don’t think so. Hint: for the help of such pairs, like "Select Var = value". You can beat the odds, spreading them out with a coma.

In the most manipulative butts, people show the creation of a simple procedure, what is taken care of, and vikonannya її. However, the procedure can accept parameters such as what the process is, what it calls, and what the value is close to (but don't set). As the stench escapes, then in the middle of the body, the processes begin. For example, how to create a procedure, how to accept the location of the region from the subscriber, what to call, and turn the data about those, how many authors are sent to the location of that region. The procedure will take into account the tables of authors of the data base, for example, Pubs, to the end of the list of authors. To take a look at the data base, for example, Google captures the SQL script from the side of SQL2005.

In the front case, the procedure takes two parameters, which will be called @State and @City in English. The type of data is consistent with the type assigned to the supplement. The title of the procedure may be internally changed by @TotalAuthors (of all authors), and it will be changed for the purpose of displaying their quantity. Dalі z'yavlyaєtsya razdіl vyboru zaputu, yakii all pіdrakhovuє. Nareshti, podrahovane znachennya vіdobrazhaєєtsya vіknі vyvodu for the assistance of the operator to a friend.

How do I save the procedure with SQL Viconati

There are two ways to follow the procedure. The first path is shown, passing the parameters, how the list is selected after the name of the procedure. It is permissible, we may have two meanings (like in the front butt). The values ​​are taken for additional changing parameters of the @State and @City procedures. Which method of transferring parameters has an important order. This method is called ordinal argument passing. In the other way, the parameters are unequivocally recognized, and in this way the order is not important. Another way to do this is to pass named arguments.

The procedure can be changed as standard. So it goes, like in the front butt, but only here the parameters are destroyed. So the @City parameter is taken first, and @State is taken from the promotion values. The standard parameter is visible and sounds okremo. Saving SQL procedures to pass just parameters. For whatever reason, use the parameter “UT” to override the value for the “CA” key. The other variant has more than one argument value for the @ City parameter, and the @ State parameter takes the default value "CA". Updates to the program will be glad, so that the changes for the abbreviations will be updated closer to the end of the list of parameters. In a different way, it is impossible to fail, and it is also your fault to practice with the transfer of naming arguments, which is more convenient and more foldable.

4. Save SQL Server Procedures: Ways to Rotate

There are three important ways to manage data at the foreclosure procedures that are saved. The stench is rehabilitated below:

Turning the value of the procedure that is taken;

Exclude the parameter of the procedures that are being saved;

Choose one of the procedures that are saved.

4.1 Turning the value of saving SQL procedures

In this method, the procedure gives the value of the local change and rotates it. The procedure can be turned around without delay. At the attacking butt, they created a procedure, as if turning over a large number of authors. If you compare this procedure with the previous ones, you can think that the meaning for the other is replaced by the return one.

Now let's wonder how to follow the procedure and enter the meaning, how to turn around. Vikonannya procedure vmagaє vstanovlennya zminnoї that friend, yak be carried out after the process. Note that you can substitute a Select operator, for example, Select @RetValue, as well as OutputValue.

4.2 Remove parameters of SQL procedures that are saved

The meaning of the vіdpovіd can be vikoristano for turning one zminnoy, which we and bachiled in the front butt. The Output parameter variable allows the procedure to change one or more of the values ​​for the side that is called. The output parameter is designated by the same key word “Output” for the hour of the procedure creation. If a job parameter is an output parameter, then the procedure object is responsible for setting its value. Saving SQL procedures, which can be used lower, are used in different cases with sub-bag information.

Our application will have two official names: @ TotalAuthors and @ TotalNoContract. The stench is indicated by the list of parameters. The number of changes is given in the middle of the body of the procedure. If we win the victorious parameters, caller may be more important, inserted in the middle of the body procedure.

In addition, in the previous scenario, two changes are made in order to increase the value, as if saving the MS SQL Server procedure in the output parameter. Then the procedure is beaten by the path of setting the normal value of the CA parameter. The next parameters are the same, and, later, the voiceless changes are transmitted in order. To give respect, that when passing through the change of days, the key word is also given here. After the procedure has been successful, the significance, as they turn for additional help of the last parameters, is shown for further reconciliation.

4.3 Choose one of the SQL procedures to save

This technique is used to rotate the set of values ​​in the data tables (RecordSet) to the full procedure that is taken. in tsyumu butt sql The procedure to be saved, with the @AuthID parameters, feeds the table "Authors" with a filter path, records are rotated after the additional @AuthId parameter. The Select statement overrides what can be turned to the calling procedure to select. When the procedure is over, the AuthId that is saved is passed back. Such a procedure here always turns only one record, otherwise it’s necessary. Ale, the procedure that is being taken is not allowed to turn over more than one record every day. It is often possible to use an example, in some cases, data rotations from the selected parameters, with the participation of the calculation of the changing ones, have a way of giving a large number of bag values.

At the end

The procedure that is being saved is to complete with a serious software module, which it turns or transmits, and also installs the necessary changes to the client addendum. Oskіlki procedure, which is saved, vikonuєtsya on the server itself, the exchange of data with the great obligations between the server and the client's addendum (for those deductible) can be deleted. This allows you to downgrade to the SQL server, which, obviously, goes into the hands of their rulers. One of the foreseeable is the T SQL procedures that are taken care of by those who are involved in the creation of significant databases. It is also great, to inspire a great number of nuances, which can be different when performing procedures that are saved, it is more necessary for those who plan to be engaged in programming, including professionally.

Microsoft SQL Server has to implement and automate its own algorithms ( rozrakhunkiv) you can beat the procedures that are saved, so today we’ll talk about those how stinks are created, changed and vanished.

A little bit of theory, so you figured out what are the procedures, what are saved and why do you need the stench in T-SQL.

Note! For programmers, I recommend the following basic materials on the topic of T-SQL:

• For more detailed information move T-SQL I also recommend reading the book - T-SQL Programmer's Way. Self Reader for Transact-SQL;
• Professional online courses with T-SQL

What are the procedures that are stored in T-SQL?

Saving procedures– all data base objects, which have an algorithm as a set of SQL instructions. In other words, we can say that the procedures that are saved are the programs in the middle of the data base. Saving procedures are saved to save on the server the re-wired code, for example, you wrote some kind of algorithm, the last one, or a rich SQL instruction, so that once you don’t win all the instructions that go into this algorithm, you can check out the procedures . In this case, if you create the SQL procedure, the server will compile the code, and then, when you run the SQL procedure on the skin, the server will not compile it again.

In order to run a procedure that is saved in SQL Server, it is necessary to write the EXECUTE command before the call, or you can write the EXEC command shorthand. I'll save the call procedure in the SELECT statement, for example, as I don't see the function, that's it. procedures are started okremo.

For procedures that are saved to view functions, it is also possible to override data modification operations like this: UNSERT, UPDATE, DELETE. Also, the procedures can be vikoristovuvati SQL statements Practically, for example, CREATE TABLE before the EXECUTE table is created, then. weekly of other procedures. There are a lot of types of instructions such as: creation or change of functions, appearance, triggers, creation of schemes and a few other similar instructions, for example, it is also not possible in the procedure, which is taken, to switch the context of the connection to the data base (USE).

The procedure is saved, you can change the input parameters and output parameters, you can rotate the tabular data, you can not rotate anything, just change the entries in the instructions.

Saving procedures are more difficult, stinks help us to automate or simplify a lot of operations, for example, you will need to constantly form different folds of analytical calls from victories zvedenih tables, then. PIVOT operator. To ask for the formation of a drink from the cym operator ( as you know, the PIVOT syntax is foldable), You can write a procedure to dynamically form the stars, for example, in the material “Dynamic PIVOT in T-SQL” of the views, the example of the implementation of this possibility is to see the procedures that are taken.

Work with procedures that are saved in Microsoft SQL Server

External data for applications

All applications below will be valid in Microsoft SQL Server 2016 Express. In order to demonstrate how to practice the procedures that are saved with real money, we need money, let's do it. For example, let's create a test table and add a few records to it, let's say that there will be a table to avenge the list of goods at their price.

Table Creation Statement CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Data Append Statement INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1 , "Misha", 100), (1, "Keyboard", 200), (2, "Phone", 400) GO --Pick to select SELECT * FROM TestTable

Danny, now let's move on to the establishment of savings procedures.

Created procedures to be taken in T-SQL - CREATE PROCEDURE statement

Saving procedures are created for additional instructions CREATE PROCEDURE After these instructions, you are responsible to write the name of your procedure, then, at the time of consumption, in the arms, set the input and output parameters. After you write the AS keyword, open the instruction block with the BEGIN keyword, close the block with the END word. Use the middle of the block You write the instructions to implement your algorithm, otherwise, it seems that you are programming in T-SQL.

For example, let's write a save procedure, like adding a new entry, tobto. new product to our test table. For which mi, three input parameters are significant: @CategoryId - іdentifіkator categorії product, @ProductName - product name and @Price - product price, given parameter be mi neobov'yazkovy, tobto. yoga can not be passed to the procedure ( for example, we do not know the price), for whom in yoga is assigned a value for locking. Qi parameters for the type of procedure, tobto. in the BEGIN ... END block you can twist, so it’s the same as the primary change ( yak you know, change is indicated by the @ sign). If you need to specify external parameters, then after naming the parameter, specify the keyword OUTPUT ( or shortened OUT).

At the BEGIN…END block, we will write an addendum data, and also in the completed SELECT addon procedure, the procedure, which zberіgaєtsya, turned us the tabular data about the goods in the designated category with the improvement of the new, well-added product. Also, in this procedure, I added the input parameter processing, and the most visible folded clearings on the cob and in the end of the text row with the method of excluding the situation, if the sprat of clearings were brought in.

Axis code tsієї procedures ( yogo I also commented).

Create procedure CREATE PROCEDURE TestProcedure (--Input parameters @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) AS BEGIN --Instructions for implementing your algorithm --Processing input parameters --Remove folded holes on the cob i at the end of the text row SET @ProductName = LTRIM(RTRIM(@ProductName)); --Append new record INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Pass data SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO

Running a procedure that is saved in T-SQL - the EXECUTE command

Run this procedure, as I have already assigned, you can use the additional command EXECUTE or EXEC. The input parameters are passed to the procedures by a simple remapping and specifying the appropriate value after the name of the procedure ( for output parameters it is also necessary to specify the OUTPUT command). However, the name of the parameters can be omitted, but in the other case it is necessary to add the sequence of the indication of the value, that is. enter the values ​​in the order in which the input parameters are specified ( cost and output parameters).

Parameters, which may have meaning for promotion, may or may not be specified, as the so-called neobov'yazykovі parameters.

The axis of a few different, albeit equivalent, ways to launch procedures, which are saved, is the core of our test procedure.

1. Calling a procedure without entering a price EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Test product 1"-2. We call the procedure from the assigned price EXEC TestProcedure @CategoryId = 1, @ProductName = "Test item 2", @Price = 300-3. Calling a procedure without specifying a parameter name in EXEC TestProcedure 1, "Test Item 3", 400

Changing the procedure that is taken in T-SQL - ALTER PROCEDURE statement

You can make changes to the algorithm of the robotic procedure for additional instructions ALTER PROCEDURE. In other words, to change the already existing procedure, it is enough for you to write ALTER PROCEDURE instead of CREATE PROCEDURE, and change everything else as needed.

Let's say we need to make changes to our test procedure, let's say the @Price parameter, then. the price, for which we will take into account the meaning of the abbreviation, and also it will be clear that we have a need in the collection of the resulting set of data, for which we will simply take the SELECT statement from the procedure that is taken.

Change procedure ALTER PROCEDURE TestProcedure (--Input parameters @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) AS BEGIN --Instructions on how to implement your algorithm --Processing input parameters --Remove lateral gaps on the cob and in the end text string SET @ProductName = LTRIM(RTRIM(@ProductName)); --Add new record INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO

Viewing procedures that are taken in T-SQL - the DROP PROCEDURE statement

If necessary, you can see the procedure that you need to save, don’t hesitate to ask for additional instructions DROP PROCEDURE.

For example, let's apparently create a test procedure for us.

DROP PROCEDURE TestProcedure

When you see the savings of the procedures, remember about those that the procedure will be enforced by other procedures or SQL instructions, if you see the stench will be completed with a pardon, shards of the procedure, for the sake of the stink, there is no more.

I’ve got everything, I’m sure, the material is good for you and brown, for now!

In order to program the extended procedures that are saved, Microsoft provides an ODS (Open Data Service) API for a set of macros and functions that can be used to encourage server add-ons to allow extending the functionality of MS SQL Server 2000.

Extended procedures that are saved - these primary functions are written in C / C ++ due to the ODS API and WIN32 API bugs, designed in the form of a dynamic link library (dll) and click, as I already said, expand the functionality of the SQL server. The ODS API gives the retailer a rich set of functions to allow transferring data to the client, retrieving any existing data sources (data source) as a great record set. Also, an extended stored procedure can rotate values ​​by passing in the OUTPUT parameter.

How to practice the expanded procedures that are saved.

• When the client program calls for an extended procedure to be saved, the request is transferred in TDS format via the Net-Libraries and Open Data Service library to the MS SQL SERVER core.
• SQL Sever to know the dll library is associated with the expanded procedure saving procedure and seizing it in its context, as it was not seized there earlier, and calling the extended saving procedure, implemented as a function in the middle of the dll.
• The procedure for saving, typing on the server and transferring the result set to the client addendum, vicorist service, which is hoped for by the ODS API, has been expanded.

Features of extended procedures that are taken care of.

• Extended procedures that are saved - these functions are conserved in the address space of MS SQL Server and in the context of security oblіkovogo record under which the MS SQL Server service is running;
• In addition, as a dll library with extended procedures that are saved, the bula is zavantazhenny in memory, it is left there until the silence, until the SQL Server is no longer sounding, or until the administrator does not vivantage the primus, vikoristovuyuchi command:
  DBCC DLL_name (FREE).
• The procedure that is saved is expanded, it is launched on the screening just like the usual procedure that is saved:
  EXECUTE xp_extendedProcName @param1, @param2 OUTPUT
  @param1 input parameter
  @param2 input/output parameter

Creation of expanded procedures that are saved.

Expanded the procedure for saving this function to the next prototype:

SRVRETCODE xp_extendedProcName(SRVPROC * pSrvProc);

Parameter pSrvProc indicator for the SRVPROC structure, as a description (handle) of a skin-specific client connection. The fields of this structure are undocumented and contain information, such as the ODS library for managing communication and data between the server addendum (Open Data Services server application) and the client. For whatever your taste, you don't need to go to the same structure and you can't modify it any more. This parameter needs to be specified for an hour, whether it be a function of the ODS API, so far away, I don’t know about this description.
The variation of the xp_ prefix is ​​not required, however, if you want to start an expanded procedure, it is possible to save it yourself, so that you add the name to the original saved procedure, the names of those, as you know, it is accepted to start the sp_z prefix.
Also, keep in mind that the names of the extended procedures that are saved are case sensitive. Do not forget about it, if you expand the procedure that is saved, otherwise you will replace the scoring result, You will remove the reminder about the pardon.
If you need to write the code for the initialization/deinitialization of the dll, tag for it standard function DllMain(). If you don't have these needs and don't want to write DLLMain(), then the compiler will choose its own version of the DLLMain() function, so don't do anything, just turn TRUE. All the functions that are called from the dll (to extend the saving procedures) are due to being voiced as exported. If you are writing in MS Visual C++, use the directive __declspec(dllexport). If the compiler does not honor this directive, describe the exported function in the EXPORTS section of the DEF file.
So, for the creation of the project, we need the following files:

• Srv.h header file, write descriptions of functions and macros of ODS API;
• Opends60.lib file to import the Opends60.dll library, which implements all the services expected by the ODS API.

Declspec(dllexport) ULONG __GetXpVersion()
{
return ODS_VERSION;
}

If MS SQL Server captures a DLL with an extended stored procedure, it will call for this function to retrieve information about the version of the library.

To write your first extended stored procedure, you need to install on your computer:

MS SQL Server 2000, be it an edition (I may have a Personal Edition). In the process of installing the code, wrap the source sample option
- MS Visual C++ (I used version 7.0), but I know for sure version 6.0

Installing SQL Server -a is required to test and tweak your DLL. Possibility and improvement as far as possible, but I didn’t shy in any way, and I put everything on my own local disk. Microsoft Visual C++ 7.0 Interprise Edition includes the Extended Stored Procedure DLL Wizard. In principle, nothing needs to be broken by nature, but only generate a template for an extended procedure that is saved. If you like fakhіvtsі, you can win yoga. Well, I have the will to work everything with my hands, and I can’t see that swing.

Now to help:
- Run Visual C++ and create a new project - Win32 Dynamic Link Library.
- Include project header file - #include ;
- Go to the menu Tools => Options and add the path to the search for include and library files. If you did not change anything during the installation of MS SQL Server, then set:

C:Program FilesMicrosoft SQL Server80ToolsDevToolsInclude for header files;
- C:Program FilesMicrosoft SQL Server80ToolsDevToolsLib for library files.
- Specify the name of the opends60.lib library file in the linker options.

If the preparatory stage is over, you can write your first extended stored procedure.

Problem setting.

The first step is to proceed to programming, it is necessary to clearly state what to start, what may be the final result, and in what way to reach it. Otzhe, the axis of technical tasks for us:

Extend the procedure for MS SQL Server 2000 complete list correcting registrations at the domain, and turning it over to the client as a standard set of records (record set). As the first input parameter, the function takes the name of the server to sweep the database directory (Active Directory), that is, the name of the domain controller. If this parameter is set to NULL, it is necessary to pass a list of local groups to the client. Another parameter will be checked by the extended stored procedure to return the value of the result of a successful/unsuccessful operation (OUTPUT parameter). If the procedure is expanded, which is saved, it is successful, then it is necessary to transfer the number of records turned into the client record set, so that in the process of the work it was not possible to take the necessary information, the value of the other parameter must be set to -1, as a sign of failure.

A mental prototype of an expanded procedure for avoiding attacks:

xp_GetUserList(@NameServer varchar, @CountRec int OUTPUT);

#include
#include
#define XP_NOERROR 0
#define XP_ERROR -1


__declspec(dllexport) SERVRETCODE xp_GetGroupList(SRVPROC* pSrvProc)
{

//Checking the number of passed parameters

//Checking the type of passed parameters

//Check, which is parameter 2 OUTPUT parameter

//Check if parameter 2 may have enough time to save the value

//Remove input parameters

//Remove the list of correspondents

// Send data to the client as a standard set of records (record set)

//Setting the value of the OUTPUT parameter

return(XP_NOERROR);
}

Robot with input parameters

I don’t want to increase your respect in third-party speeches, but I want to inspire yoga on robots with the procedure transferred to the extended procedure, which is selected by parameters. For this reason, our technical task is easy to understand and it is possible to expand only that part, as it works with the input parameters. Ale, back to back, not a lot of theory

First day, yaku maє vykonati our exteneded stored procedure - take the parameters, as they were transferred during vyklik. Dorimuyuchis induced by the algorithm, we need to follow these steps:

Determine the number of transferred parameters;
- change that the passed parameters may be the correct type of data;
- Reconsider that the OUTPUT parameter may have enough value, to save it to a new value, it is rotated by our extended stored procedure.
- otrimati transferred parameters;
- Set the value of the output parameter as the result of a successful/unsuccessful completion of the extended stored procedure.

Now we can clearly see the leather item:

Deciding the number of transfers to the expanded procedure, which is saved, parameters

To remove the number of transferred parameters, it is necessary to select the function:

int srv_rpcparams(SRV_PROC * srvproc);

Depending on the data type and the value of the passed parameters

To retrieve information about the type and the number of passed parameters, Microsoft recommends using the srv_paramifo function. This universal function replaces the wikis srv_paramtype, srv_paramlen, srv_parammaxlen, as they are considered obsolete. Axis її prototype:

int srv_paraminfo(
SRV_PROC * srvproc,
intn,
BYTE*pbType,
ULONG* pcbMaxLen,
ULONG*pcbActualLen,
BYTE*pbData,
bool*pfNull);

Recheck, which is another OUTPUT parameter.

The srv_paramstatus() function is assigned to the status of the passed parameter:

int srv_paramstatus(
SRV_PROC * srvproc,
intn
);

n is the number of the parameter passed to the extended saving procedure for weekday. I guess: the parameters are always numbered from 1.
To rotate the value of srv_paramstatus, set the zero bit. Even if the parameter is set to 1, the parameter is passed as the OUTPUT parameter, and if it is set to 0, the most significant parameter, we pass by value. Likewise, the extended stored procedure of the boolean without parameters, the turn function is -1.

Set the value of the output parameter.

An output parameter passed to the extended saver can be passed a value using the srv_paramsetoutput function. This new function replaces the shortcut of the srv_paramset function, as it is now respected by the old one, because does not support new types of data introduced into the ODS API and data of zero value.

int srv_paramsetoutput(
SRV_PROC *srvproc,
intn,
BYTE *pbData,
ULONG cbLen,
BOOL fNull
);

#include

#define XP_NOERROR 0
#define XP_ERROR 1

#define MAX_SERVER_ERROR 20000
#define XP_HELLO_ERROR MAX_SERVER_ERROR+1

void printError (SRV_PROC*, CHAR*);

#ifdef __cplusplus
extern "C" (
#endif

SRVRETCODE __declspec(dllexport) xp_helloworld(SRV_PROC* pSrvProc);

#ifdef __cplusplus
}
#endif

SRVRETCODE xp_helloworld(SRV_PROC* pSrvProc)
{
char szText = "Hello World!";
BYTE bType;
ULONG cbMaxLen;
ULONG cbActualLen;
BOOL fNull;

/* Determining the number of transfers to the extended savings
parameter procedure */
if (srv_rpcparams(pSrvProc) != 1)
{
printError(pSrvProc, "Incorrect number of parameters!");
return(XP_ERROR);
}

/* Retrieve information about the type of data and the length of the transferred parameters */
if (srv_paraminfo(pSrvProc, 1, &bType, &cbMaxLen,
&cbActualLen, NULL, &fNull) == FAIL)
{
printError(pSrvProc,
"Do not try to take information about the input parameters...");
return(XP_ERROR);
}

/* Verify that the number of passes is the OUTPUT parameter */
if ((srv_paramstatus(pSrvProc, 1) & SRV_PARAMRETURN) == FAIL)
{
printError(pSrvProc,
"Transmission parameter is not OUTPUT parameter!");
return(XP_ERROR);
}

/* Check the data type of the passed parameter */
if (bType != SRVBIGVARCHAR && bType != SRVBIGCHAR)
{
printError(pSrvProc, "Incorrect type of parameter passed!");
return(XP_ERROR);
}

/* We change that the transfer parameter can be enough to save the row that is being rotated */
if (cbMaxLen< strlen(szText))
{
printError(pSrvProc,
"Insufficient time parameter passed to save row n, what to turn!");
return(XP_ERROR);
}

/* Set the value of the OUTPUT parameter */
if (FAIL == srv_paramsetoutput(pSrvProc, 1, (BYTE*)szText, 13, FALSE))
{
printError(pSrvProc,
"Can't set value of OUTPUT parameter...");
return(XP_ERROR);
}

srv_senddone(pSrvProc, (SRV_DONE_COUNT | SRV_DONE_MORE), 0, 1);
return(XP_NOERROR);
}

void printError (SRV_PROC *pSrvProc, CHAR* szErrorMsg)
{
srv_sendmsg(pSrvProc, SRV_MSG_ERROR, XP_HELLO_ERROR, SRV_INFO, 1,
NULL, 0, 0, szErrorMsg,SRV_NULLTERM);

Srv_senddone(pSrvProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
}

The srv_sendmsg and srv_senddone functions have been left out of sight. The srv_sendmsg function is vindicated to help the client. Axis її prototype:

int srv_sendmsg(
SRV_PROC * srvproc,
intmsgtype,
dbint msgnum,
DBTINYINT class,
DBTINYINT state,
dbchar*rpcname,
int rpcnamelen,
DBUSMALLINT linenum,
dbchar*message,
int msglen
);

The process of work has expanded the procedure that is saved, it is the responsibility of regularly informing the client's addendum of their status, tobto. nadsilati podomlennya about vikonanі diї. For which i the srv_senddone function is recognized:

int srv_senddone(
SRV_PROC * srvproc,
DBUSMALLINT status,
DBUSMALLINT info,
DBINT count
);

status status ensign. The value of this parameter can be set for additional logical AND and OR operators to combine pointing constants in the table:
Status flag Description
SRV_DONE_FINAL Continuous collection of results is residual;
SRV_DONE_MORE Accurate collection of results is not a residual trace to score on a black portion of data;
SRV_DONE_COUNT The count parameter must have a valid value
SRV_DONE_ERROR Vikoristovuetsya for a message about pardons that negain complete.
into reservations, you must set 0.
count Number of result sets of data that are asked to the client. As a matter of fact, status is set to SRV_DONE_COUNT, then count is responsible for removing the correct number of sets that the client asks for in the records.
Meanings that turn:
- at times success SUCCEED
- In case of failure FAIL.

Installing extension procedures that are saved on MS SQL Server 2000

1.Copy the dll library with the extended save procedure to the binn directory on the machine where MS SQL Server is installed. I have less trouble: C:Program FilesMicrosoft SQL ServerMSSQLBinn;
2.Register the extended procedure that is saved to the server by writing the following script:

US Master
EXECUTE SP_ADDEXTENDEDPROC xp_helloworld, xp_helloworld.dll

Test the xp_helloworld robot by running the following script:

DECLARE @Param varchar(33)
EXECUTE xp_helloworld @Param OUTPUT
SELECT @Param AS OUTPUT_Param

Visnovok

On this first part of my article is completed. Now I am convinced, you are ready to get involved with our technical tasks at 100%. At the next article, you recognize:
- Tipi data, designated in the ODS API;
- special benefits of expanding savings procedures;
- how to form the recordset and transfer it to your client's addendum;
- Frequently reviewed Active Directory Network Manegment API functions required for managing the list of domain names;
- We create a ready-made project (we implement our technical project)
I'm going down - to the swedish zustrich!

PS: application files for statistics for studio 7.0