MySQL 5 has many new features, one of the most significant of which is the creation of stored procedures. In this tutorial, I'll talk about what they are and how they can make your life easier.

Introduction

A stored procedure is a way to encapsulate repetitive actions. Stored procedures can declare variables, manipulate data flow, and use other programming techniques.

The reason for their creation is clear and is confirmed by frequent use. On the other hand, if you talk to those who work with them irregularly, the opinions will be divided into two completely opposite flanks. Don't forget this.

Behind

• Sharing logic with other applications. Stored procedures encapsulate functionality; this provides connectivity for data access and management 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 SQL command DELETE.
• 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.

The tool I work with is called MySQL Query Browser, which is pretty standard for interacting with databases. The MySQL command line tool is another excellent choice. The reason I'm telling you this is because everyone's favorite phpMyAdmin doesn't support running stored procedures.

By the way, I'm using a basic table structure to make it easier for you to understand this topic. I’m talking about stored procedures, and they are complex enough to require delving into the cumbersome table structure.

Step 1: Place a limiter

A delimiter is a character or string of characters that is used to indicate to the MySQL client that you have finished writing the SQL expression. For ages, the semicolon has been the delimiter. However, problems may arise because there may be multiple expressions in a stored procedure, each of which must end with a semicolon. In this tutorial I use the string “//” as a delimiter.

Step 2: How to work with stored procedures

Creating a Stored Procedure

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 stored procedure. The next one contains optional parameters. Then comes the name and, finally, the body of the procedure itself.

Stored procedure names are case sensitive. You also cannot create multiple procedures with the same name. There cannot be expressions inside a stored procedure that modify the database 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

To call a stored procedure, you must type keyword CALL, and then the name of the procedure, and in parentheses indicate the parameters (variables or values). Parentheses are required.

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.

Removing a Stored Procedure

DROP PROCEDURE IF EXISTS p2;

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

Step 3: Options

Let's see how we can pass parameters to a stored procedure.

• 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.

Naturally, you can specify several parameters of different types.

IN parameter example

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

Example OUT parameter

DELIMITER // CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "This is a test"; END //

INOUT parameter example

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

Step 4: Variables

Now I will teach you how to create variables and store them inside procedures. You must declare them explicitly at the beginning of the BEGIN/END block, along with their data types. Once you have declared a variable, you can use it in the same way as session variables, literals, or column names.

The variable declaration syntax looks like this:

DECLARE varname DATA-TYPE DEFAULT defaultvalue;

Let's declare some variables:

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

Working with Variables

Once you have declared a variable, you can set its value using 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 //

Step 5: Thread Control Structures

MySQL supports IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs to control threads within a stored procedure. We'll look at how to use IF, CASE, and WHILE since they are the most commonly used.

IF design

Using the IF construct, we can perform tasks containing conditions:

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

CASE design

CASE is another method of testing conditions and selecting a suitable solution. This great way replacing multiple IF constructs. The construct can be described in two ways, providing flexibility in managing multiple conditional expressions.

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 design

Technically, there are three types of loops: the WHILE loop, the LOOP loop, and the REPEAT loop. You can also loop using the Darth Vader programming technique: GOTO statements. Here's an example 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 //

Step 6: Cursors

Cursors are used to traverse the set of rows returned by a query and process each row.

MySQL supports cursors in stored procedures. Here is a short syntax for creating and using a cursor.

DECLARE cursor-name CURSOR FOR SELECT ...; /*Declaring a cursor and filling it */ DECLARE CONTINUE HANDLER FOR NOT FOUND /*What to do when there are no more records*/ OPEN cursor-name; /*Open cursor*/ FETCH cursor-name INTO variable [, variable]; /*Assign a value to a variable equal to the current value of the column*/ CLOSE cursor-name; /*Close cursor*/

In this example we will perform some simple operations using a 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; ENDIF; END WHILE; CLOSE cur1; SET param1 = c; END //

Cursors have three properties that you need to understand to avoid getting unexpected results:

• Not sensitive: a cursor that opens once will not reflect changes in the table that occur later. In reality, MySQL does not guarantee that the cursor will be updated, so don't rely on it.
• Read-only: Cursors cannot be modified.
• No rewind: the cursor can only move in one direction - forward, you will not be able to skip lines without selecting them.

Conclusion

In this tutorial, I introduced you to the basics of working with stored procedures and some of the specific properties associated with it. Of course, you will need to deepen your knowledge in areas such as security, SQL expressions and optimization before becoming a true MySQL procedure guru.

You should calculate the benefits of using stored procedures in your specific application, and only then create only necessary procedures. In general, I use procedures; In my opinion, they are worth implementing into projects due to their security, code maintenance and overall design. In addition, do not forget that over MySQL procedures still a work in progress. Expect improvements regarding functionality and improvements. Please feel free to share your opinions.

In Microsoft SQL Server to implement and automate your own algorithms ( calculations) you can use stored procedures, so today we will talk about how they are created, modified and deleted.

But first, a little theory so that you understand what stored procedures are and why they are needed in T-SQL.

Note! I recommend the following to beginner programmers: useful materials on the topic of T-SQL:

• For a more detailed study of the T-SQL language, I also recommend reading the book - The T-SQL Programmer's Path. Tutorial on the Transact-SQL language;
• Professional online courses on T-SQL

What are stored procedures in T-SQL?

Stored procedures– these are database objects that contain an algorithm in the form SQL set instructions. In other words, we can say that stored procedures are programs inside a database. Stored procedures are used to store reusable code on the server, for example, you wrote some algorithm, sequential calculation or multi-step SQL statement, and so as not to execute all the instructions included in it each time this algorithm You can format it as a stored procedure. At the same time, when you create a SQL procedure, the server compiles the code, and then, every time you run this SQL procedures the server will no longer compile it again.

In order to run a stored procedure in SQL Server, you must write the EXECUTE command before its name; it is also possible to abbreviate this command as EXEC. Calling a stored procedure in a SELECT statement, for example, as a function will no longer work, i.e. procedures are launched separately.

In stored procedures, unlike functions, it is already possible to perform data modification operations such as: UNSERT, UPDATE, DELETE. You can also use SQL statements of almost any type in procedures, for example, CREATE TABLE to create tables or EXECUTE, i.e. calling other procedures. The exception is several types of instructions, such as: creating or changing functions, views, triggers, creating schemas and several other similar instructions, for example, you also cannot switch the database connection context (USE) in a stored procedure.

A stored procedure can have input parameters and output parameters, it can return tabular data, or it can return nothing, only execute the instructions contained in it.

Stored procedures are very useful, they help us automate or simplify many operations, for example, you constantly need to generate various complex analytical reports using pivot tables, i.e. PIVOT operator. To make it easier to formulate queries with this operator ( as you know, PIVOT's syntax is quite complex), You can write a procedure that will dynamically generate summary reports for you, for example, the material “Dynamic PIVOT in T-SQL” provides an example of implementing this feature in the form of a stored procedure.

Examples of working with stored procedures in Microsoft SQL Server

Source data for examples

All examples below will be run in Microsoft SQL Server 2016 Express. In order to demonstrate how stored procedures work with real data, we need this data, let's create it. For example, let's create a test table and add some records to it, let's say that it will be a table containing a list of products with their prices.

Instruction for creating a table CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Instruction for adding data INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1 , "Mouse", 100), (1, "Keyboard", 200), (2, "Phone", 400) GO --Select query SELECT * FROM TestTable

We have the data, now let's move on to creating stored procedures.

Creating a stored procedure in T-SQL - the CREATE PROCEDURE statement

Stored procedures are created using a statement CREATE PROCEDURE, after this instruction you must write the name of your procedure, then, if necessary, define the input and output parameters in parentheses. After that, you write the AS keyword and open the block of instructions with the BEGIN keyword, close this block with the word END. Inside this block, you write all the instructions that implement your algorithm or some kind of sequential calculation, in other words, you program in T-SQL.

For example, let's write a stored procedure that will add new entry, i.e. new product to our test table. To do this, we will define three input parameters: @CategoryId – product category identifier, @ProductName – product name and @Price – product price; this parameter will be optional, i.e. it will not be necessary to pass it to the procedure ( for example, we don’t know the price yet), for this purpose we will set a default value in its definition. These parameters are in the body of the procedure, i.e. in the BEGIN...END block can be used, just like regular variables ( As you know, variables are denoted by the @ sign). If you need to specify output parameters, then after the parameter name indicate the keyword OUTPUT ( or OUT for short).

In the BEGIN...END block we will write an instruction for adding data, as well as a SELECT instruction at the end of the procedure, so that the stored procedure will return us tabular data about the products in the specified category, taking into account the new, just added product. Also in this stored procedure I added processing of the incoming parameter, namely removing extra spaces at the beginning and at the end text string in order to exclude situations where several spaces were accidentally entered.

Here is the code for this procedure ( I also commented on it).

Create a procedure CREATE PROCEDURE TestProcedure (--Input parameters @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) AS BEGIN --Instructions that implement your algorithm --Processing incoming parameters --Removing extra spaces at the beginning and in end of the text line SET @ProductName = LTRIM(RTRIM(@ProductName)); --Add a new record INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Return the data SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO

Running a stored procedure in T-SQL - EXECUTE command

You can run a stored procedure, as I already noted, using the EXECUTE or EXEC command. Incoming parameters are passed to procedures by simply listing them and specifying the appropriate values ​​after the procedure name ( for output parameters you also need to specify the OUTPUT command). However, the names of the parameters may not be specified, but in this case it is necessary to follow the sequence of specifying the values, i.e. specify values ​​in the order in which the input parameters are defined ( this also applies to output parameters).

Parameters that have default values ​​do not need to be specified; these are the so-called optional parameters.

Here are a few different but equivalent ways to run stored procedures, specifically our test procedure.

1. Call the procedure without specifying the price EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Test product 1" --2. Call the procedure indicating the price EXEC TestProcedure @CategoryId = 1, @ProductName = "Test product 2", @Price = 300 --3. Call the procedure without specifying the name of the parameters EXEC TestProcedure 1, "Test product 3", 400

Changing a stored procedure to T-SQL - ALTER PROCEDURE statement

You can make changes to the algorithm of the procedure using the instructions ALTER PROCEDURE. In other words, in order to change an already existing procedure, you just need to write ALTER PROCEDURE instead of CREATE PROCEDURE, and change everything else as necessary.

Let's say we need to make changes to our test procedure, say the @Price parameter, i.e. price, we will make it mandatory, for this we will remove the default value, and also imagine that we no longer need to obtain the resulting data set, for this we will simply remove the SELECT statement from the stored procedure.

We change the procedure ALTER PROCEDURE TestProcedure (--Incoming parameters @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) AS BEGIN --Instructions that implement your algorithm --Processing incoming parameters --Removing extra spaces at the beginning and end of the text lines SET @ProductName = LTRIM(RTRIM(@ProductName)); --Add a new record INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO

Deleting a stored procedure in T-SQL - DROP PROCEDURE statement

If necessary, you can delete the stored procedure; this is done using the instructions DROP PROCEDURE.

For example, let's delete the test procedure we created.

DROP PROCEDURE TestProcedure

When deleting stored procedures, it is worth remembering that if the procedure is referenced by other procedures or SQL statements, after it is deleted, they will fail with an error, since the procedure they reference no longer exists.

That’s all I have, I hope the material was interesting and useful to you, bye!

stored procedure is possible only if it is carried out in the context of the database where the procedure is located.

Types of Stored Procedures

SQL Server has several types stored procedures.

• System stored procedures designed to perform various administrative actions. Almost all server administration activities are performed with their help. We can say that systemic stored procedures are an interface that provides work with system tables, which ultimately comes down to changing, adding, deleting and retrieving data from system tables of both user and system databases. System stored procedures have the sp_ prefix, are stored in the system database and can be called in the context of any other database.
• Custom stored procedures implement certain actions. Stored procedures– a full-fledged database object. As a result, each stored procedure is located in a specific database where it is executed.
• Temporary stored procedures exist only for a while, after which they are automatically destroyed by the server. They are divided into local and global. Local temporary stored procedures can only be called from the connection in which they were created. When creating such a procedure, you must give it a name that begins with a single # character. Like all temporary objects, stored procedures of this type are automatically deleted when the user disconnects or the server is restarted or stopped. Global temporary stored procedures are available for any connections from a server that has the same procedure. To define it, just give it a name starting with the characters ## . These procedures are deleted when the server is restarted or stopped, or when the connection in the context in which they were created is closed.

Create, modify, and delete stored procedures

Creation stored procedure involves solving the following problems:

• determining the type of created stored procedure: temporary or custom. In addition, you can create your own system stored procedure, giving it a name prefixed with sp_ and placing it in system base data. This procedure will be available in the context of any local server database;
• planning access rights. While creating stored procedure it should be taken into account that it will have the same access rights to database objects as the user who created it;
• definition stored procedure parameters. Similar to the procedures included in most programming languages, stored procedures may have input and output parameters;
• code development stored procedure. The procedure code can contain a sequence of any SQL commands, including calls to other stored procedures.

Creating a new one and changing an existing one stored procedure done using the following command:

<определение_процедуры>::= (CREATE | ALTER ) procedure_name [;number] [(@parameter_name data_type ) [=default] ][,...n] AS sql_operator [...n]

Let's look at the parameters of this command.

Using the prefixes sp_ ​​, # , ## , the created procedure can be defined as a system or temporary one. As you can see from the command syntax, it is not allowed to specify the name of the owner who will own the created procedure, as well as the name of the database where it should be located. Thus, in order to place the created stored procedure in a specific database, you must issue the CREATE PROCEDURE command in the context of that database. When turning from the body stored procedure shortened names can be used for objects of the same database, i.e. without specifying the database name. When you need to access objects located in other databases, specifying the database name is mandatory.

The number in the name is an identification number stored procedure, which uniquely identifies it in a group of procedures. For ease of management, procedures are logically of the same type stored procedures can be grouped by giving them the same name but different identification numbers.

To transfer input and output data in the created stored procedure parameters can be used, the names of which, like the names of local variables, must begin with the @ symbol. One stored procedure You can specify multiple parameters separated by commas. The body of a procedure should not use local variables whose names coincide with the names of the parameters of this procedure.

To determine the data type that the corresponding stored procedure parameter, any type is suitable SQL data, including user-defined ones. However, the CURSOR data type can only be used as output parameter stored procedure, i.e. specifying the OUTPUT keyword.

The presence of the OUTPUT keyword means that the corresponding parameter is intended to return data from stored procedure. However, this does not mean that the parameter is not suitable for passing values ​​to stored procedure. Specifying the OUTPUT keyword instructs the server to exit stored procedure assign the current value of the parameter to the local variable that was specified when calling the procedure as the value of the parameter. Note that when specifying the OUTPUT keyword, the value of the corresponding parameter when calling the procedure can only be set using a local variable. Any expressions or constants that are allowed for regular parameters are not permitted.

The VARYING keyword is used in conjunction with

Stored procedure is a special type of Transact-SQL statement package created using the SQL language and procedural extensions. The main difference between a package and a stored procedure is that the latter is stored as a database object. In other words, stored procedures are stored on the server side to improve performance and consistency of repeatable tasks.

The Database Engine supports stored procedures and system procedures. Stored procedures are created in the same way as all other database objects, i.e. using DDL language. System procedures are provided by the Database Engine and can be used to access and modify information in the system catalog.

When you create a stored procedure, you can define an optional list of parameters. This way, the procedure will accept the appropriate arguments each time it is called. Stored procedures can return a value containing user-defined information or, in the event of an error, an appropriate error message.

The stored procedure is precompiled before it is stored as an object in the database. The precompiled form of the procedure is stored in the database and used each time it is called. This property of stored procedures provides the important benefit of eliminating (in almost all cases) repeated procedure compilations and achieving corresponding performance improvements. This property of stored procedures also has a positive effect on the amount of data exchanged between the database system and applications. In particular, calling a stored procedure that is several thousand bytes in size may require less than 50 bytes. When multiple users perform repetitive tasks using stored procedures, the cumulative effect of these savings can be quite significant.

Stored procedures can also be used for the following purposes:

to create a log of actions with database tables.

Using stored procedures provides a level of security control that goes well beyond the security provided by using GRANT and REVOKE statements, which grant different access privileges to users. This is possible because the authorization to execute a stored procedure is independent of the authorization to modify the objects contained in the stored procedure, as described in the next section.

Stored procedures that create logs of table write and/or read operations provide additional opportunity ensuring database security. Using such procedures, the database administrator can monitor modifications made to the database by users or application programs.

Creating and Executing Stored Procedures

Stored procedures are created using a statement CREATE PROCEDURE, which has the following syntax:

CREATE PROC proc_name [((@param1) type1 [ VARYING] [= default1] )] (, ...) AS batch | EXTERNAL NAME method_name Syntax conventions

The schema_name parameter specifies the name of the schema that is assigned by the owner of the created stored procedure. The proc_name parameter specifies the name of the stored procedure. The @param1 parameter is a procedure parameter (formal argument) whose data type is determined by the type1 parameter. Procedure parameters are local within the procedure, just as local variables are local within the package. Procedure parameters are values ​​that are passed by the caller to the procedure for use in it. The default1 parameter specifies the default value for the corresponding procedure parameter. (The default value can also be NULL.)

OUTPUT option indicates that a procedure parameter is a return parameter and can be used to return a value from a stored procedure to the calling procedure or system.

As mentioned earlier, the precompiled form of a procedure is stored in the database and used every time it is called. If for some reason the stored procedure needs to be compiled each time it is called, when declaring the procedure, use WITH RECOMPILE option. Using the WITH RECOMPILE option negates one of the most important benefits of stored procedures: the performance improvement due to a single compilation. Therefore, the WITH RECOMPILE option should only be used when the database objects used by the stored procedure are frequently modified.

EXECUTE AS clause defines the security context in which the stored procedure should execute after it is called. By setting this context, the Database Engine can control the selection of user accounts to verify access permissions to the objects referenced by the stored procedure.

By default, only members of the sysadmin fixed server role and the db_owner or db_ddladmin fixed database roles can use the CREATE PROCEDURE statement. But members of these roles can assign this right to other users using the statement GRANT CREATE PROCEDURE.

The example below shows how to create a simple stored procedure to work with the Project table:

USE SampleDb; GO CREATE PROCEDURE IncreaseBudget (@percent INT=5) AS UPDATE Project SET Budget = Budget + Budget * @percent/100;

As stated earlier, to separate two packets, use GO instructions. The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in the same batch. The IncreaseBudget stored procedure increases budgets for all projects by a certain percentage, determined by the @percent parameter. The procedure also defines a default percentage value (5) that is used if this argument is not present when the procedure runs.

Stored procedures can access tables that do not exist. This property allows you to debug procedure code without first creating the appropriate tables or even connecting to the destination server.

Unlike primary stored procedures, which are always stored in the current database, it is possible to create temporary stored procedures that are always stored in the temporary system database tempdb. One reason to create temporary stored procedures may be to avoid repetitive execution. certain group instructions when connecting to the database. You can create local or global temporary procedures. To do this, the name of the local procedure is specified with a single # character (#proc_name), and the name of the global procedure is specified with a double character (##proc_name).

A local temporary stored procedure can only be executed by the user who created it, and only while connected to the database in which it was created. A global temporary procedure can be executed by all users, but only until the last connection on which it is executed (usually the connection of the procedure's creator) terminates.

The life cycle of a stored procedure consists of two stages: its creation and its execution. Each procedure is created once and executed many times. The stored procedure is executed using EXECUTE instructions a user who is the owner of a procedure or has EXECUTE privilege to access that procedure. The EXECUTE statement has the following syntax:

[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] value | [@parameter1=] @variable ] | DEFAULT).. Syntax conventions

With the exception of the return_status parameter, all parameters of the EXECUTE statement have the same logical meaning as the same parameters of the CREATE PROCEDURE statement. The return_status parameter specifies an integer variable that stores the return status of the procedure. A value can be assigned to a parameter using either a constant (value) or a local variable (@variable). The order of the values ​​of named parameters is not important, but the values ​​of unnamed parameters must be provided in the order in which they are defined in the CREATE PROCEDURE statement.

DEFAULT clause provides the default value for a procedure parameter that was specified in the procedure definition. When a procedure expects a value for a parameter for which no default value has been defined and the parameter is missing or the DEFAULT keyword is specified, an error occurs.

When the EXECUTE statement is the first statement of a batch, the EXECUTE keyword can be omitted. However, it is safer to include this word in every packet. The use of the EXECUTE statement is shown in the example below:

USE SampleDb; EXECUTE IncreaseBudget 10;

The EXECUTE statement in this example executes the IncreaseBudget stored procedure, which increases the budget of all projects by 10%.

The example below shows how to create a stored procedure to process data in the Employee and Works_on tables:

The ModifyEmpId example procedure illustrates the use of stored procedures as part of the process of maintaining referential integrity (in this case between the Employee and Works_on tables). A similar stored procedure can be used inside a trigger definition, which actually provides referential integrity.

The following example shows the use of an OUTPUT clause in a stored procedure:

This stored procedure can be executed using the following instructions:

DECLARE @quantityDeleteEmployee INT; EXECUTE DeleteEmployee @empId=18316, @OUTPUT; PRINT N"Deleted employees: " + convert(nvarchar(30), @quantityDeleteEmployee);

This procedure counts the number of projects that the employee with personnel number @empId is working on and assigns the resulting value to the ©counter parameter. After all rows for a given personnel number are deleted from the Employee and Works_on tables, the calculated value is assigned to the @quantityDeleteEmployee variable.

The parameter value is returned to the calling procedure only if the OUTPUT option is specified. In the example above, the DeleteEmployee procedure passes the @counter parameter to the calling procedure, hence the stored procedure returns a value to the system. Therefore, the @counter parameter must be specified both in the OUTPUT option when declaring a procedure and in the EXECUTE statement when calling it.

WITH RESULTS SETS clause of EXECUTE statement

In SQL Server 2012, for the EXECUTE statement, you enter WITH RESULTS SETS clause, through which, when certain conditions are met, you can change the form of the result set of a stored procedure.

The following two examples will help explain this sentence. The first example is an introductory example that shows what the result might look like when the WITH RESULTS SETS clause is omitted:

The EmployeesInDept procedure is a simple procedure that displays the personnel numbers and last names of all employees working in a specific department. The department number is a procedure parameter and must be specified when calling it. Executing this procedure produces a table with two columns whose headings match the names of the corresponding columns in the database table, i.e. Id and LastName. To change the headers of result columns (as well as their data type), SQL Server 2012 uses the new WITH RESULTS SETS clause. The application of this sentence is shown in the example below:

USE SampleDb; EXEC EmployeesInDept "d1" WITH RESULT SETS (( INT NOT NULL, [LastName] CHAR(20) NOT NULL));

The result of executing a stored procedure called in this way will be as follows:

As you can see, running a stored procedure using the WITH RESULT SETS clause in the EXECUTE statement allows you to change the names and data types of the columns in the result set produced by the procedure. Thus, this new functionality provides greater flexibility in executing stored procedures and placing their results in a new table.

Changing the Structure of Stored Procedures

The Database Engine also supports the instruction ALTER PROCEDURE to modify the structure of stored procedures. The ALTER PROCEDURE statement is typically used to change Transact-SQL statements within a procedure. All parameters of the ALTER PROCEDURE statement have the same meaning as the same parameters of the CREATE PROCEDURE statement. The main purpose of using this statement is to avoid overriding existing stored procedure rights.

The Database Engine supports CURSOR data type. This data type is used to declare cursors in stored procedures. Cursor is a programming construct used to store the results of a query (usually a set of rows) and allow users to display that result row by row.

To delete one or a group of stored procedures, use DROP PROCEDURE instruction. Only the owner or members of the db_owner and sysadmin fixed roles can delete a stored procedure.

Stored procedures and the common language runtime

SQL Server supports the Common Language Runtime (CLR), which allows you to develop various database objects (stored procedures, user-defined functions, triggers, user-defined aggregations, and custom data types) using C# and Visual Basic. The CLR also allows you to execute these objects using the common runtime system.

The common language runtime is enabled and disabled using the option clr_enabled system procedure sp_configure, which is launched for execution by instruction RECONFIGURE. The following example shows how you can use the sp_configure system procedure to enable the CLR:

USE SampleDb; EXEC sp_configure "clr_enabled",1 RECONFIGURE

To create, compile, and save a procedure using the CLR, you must complete the following sequence of steps in the order shown:

Create a stored procedure in C# or Visual Basic, and then compile it using the appropriate compiler.

Using instructions CREATE ASSEMBLY, create the corresponding executable file.

Execute the procedure using the EXECUTE statement.

The figure below shows a graphical diagram of the previously outlined steps. Below is more detailed description this process.

First create the required program in some development environment like Visual Studio. Compile ready-made program into object code using the C# or Visual Basic compiler. This code is stored in a dynamic-link library (.dll) file, which serves as the source for the CREATE ASSEMBLY statement, which creates the intermediate executable code. Next, issue a CREATE PROCEDURE statement to save the executing code as a database object. Finally, run the procedure using the familiar EXECUTE statement.

The example below shows the source code for a stored procedure in C#:

Using System.Data.SqlClient; using Microsoft.SqlServer.Server; public partial class StoredProcedures ( public static int CountEmployees() ( int rows; SqlConnection connection = new SqlConnection("Context Connection=true"); connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = "select count(*) as "Number of employees" " + "from Employee"; rows = (int)cmd.ExecuteScalar(); connection.Close(); return rows; ) )

This procedure implements a query to count the number of rows in the Employee table. Using directives at the beginning of a program specify the namespaces required to execute the program. The use of these directives allows you to specify source code class names without explicitly specifying the corresponding namespaces. Next, the StoredProcedures class is defined, for which SqlProcedure attribute, which informs the compiler that this class is a stored procedure. The CountEmployees() method is defined inside the class code. A connection to the database system is established through an instance of the class SqlConnection. To open a connection, the Open() method of this instance is used. A CreateCommand() method allows you to access an instance of a class SqlCommnd, to which the required SQL command is passed.

In the following code snippet:

Cmd.CommandText = "select count(*) as "Number of employees" " + "from Employee";

uses a SELECT statement to count the number of rows in the Employee table and display the result. The command text is specified by setting the CommandText property of the cmd variable to the instance returned by the CreateCommand() method. Next it is called ExecuteScalar() method SqlCommand instance. This method returns a scalar value which is converted to integer type int data and assigned to the rows variable.

You can now compile this code using Visual Studio. I added this class to a project called CLRStoredProcedures, so Visual Studio will compile an assembly of the same name with a *.dll extension. The example below shows the next step in creating a stored procedure: creating the executable code. Before you run the code in this example, you need to know the location of the compiled dll file (usually located in the Debug folder of the project).

USE SampleDb; GO CREATE ASSEMBLY CLRStoredProcedures FROM "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" WITH PERMISSION_SET = SAFE

The CREATE ASSEMBLY statement takes managed code as input and creates a corresponding object on which you can create CLR stored procedures, user-defined functions, and triggers. This instruction has the following syntax:

CREATE ASSEMBLY assembly_name [ AUTHORIZATION owner_name ] FROM (dll_file) Syntax conventions

The assembly_name parameter specifies the name of the assembly. The optional AUTHORIZATION clause specifies the role name as the owner of this assembly. The FROM clause specifies the path where the assembly to load is located.

WITH PERMISSION_SET clause is a very important clause of the CREATE ASSEMBLY statement and must always be specified. It defines the set of permissions granted to the assembly code. The SAFE permission set is the most restrictive. Assembly code that has these rights cannot access external system resources such as files. The EXTERNAL_ACCESS rights set allows assembly code to access certain external system resources, while the UNSAFE rights set allows unrestricted access to resources both inside and outside the database system.

To save assembly code information, the user must be able to issue a CREATE ASSEMBLY statement. The owner of the assembly is the user (or role) executing the instruction. You can make another user the owner of the assembly by using the AUTHORIZATION clause of the CREATE SCHEMA statement.

The Database Engine also supports ALTER ASSEMBLY and DROP ASSEMBLY statements. ALTER ASSEMBLY statement used to update the assembly to latest version. This instruction also adds or removes files associated with the corresponding assembly. DROP ASSEMBLY instruction Removes the specified assembly and all its associated files from the current database.

The example below shows how to create a stored procedure based on the managed code you implemented earlier:

USE SampleDb; GO CREATE PROCEDURE CountEmployees AS EXTERNAL NAME CLRStoredProcedures.StoredProcedures.CountEmployees

The CREATE PROCEDURE instruction in the example differs from the same instruction in the previous examples in that it contains EXTERNAL NAME parameter. This option specifies that the code is generated by the common language runtime. The name in this sentence consists of three parts:

assembly_name.class_name.method_name

assembly_name - indicates the name of the assembly;

class_name - indicates the name of the general class;

method_name - optional part, specifies the name of the method that is defined inside the class.

The execution of the CountEmployees procedure is shown in the example below:

USE SampleDb; DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count -- Return 7

The PRINT statement returns the current number of rows in the Employee table.

1. Study operators describing stored procedures and the principles of passing their input and output parameters.
2. Learn how to create and debug stored procedures on MS SQL Server 2000.
3. Develop five basic stored procedures for the Library training database.
4. Prepare a report on the work done in electronic form.

1. Understanding Stored Procedures

Stored Procedure this is a set of commands stored on the server and executed as a single unit. Stored procedures are a mechanism by which you can create routines that run on the server and are controlled by its processes. Such routines can be invoked by the application that calls them. They can also be caused by data integrity rules or triggers.

Stored procedures can return values. The procedure can compare user-entered values ​​with information preset in the system. Stored procedures take advantage of powerful SQL Server hardware solutions. They are database-centric and work closely with the SQL Server optimizer. This allows you to get high performance when processing data.

You can pass values ​​to stored procedures and receive results from them, not necessarily related to the worksheet. A stored procedure can compute results as it runs.

There are two types of stored procedures: ordinary And extended. Regular stored procedures are a set of Transact-SQL commands, while extended stored procedures are represented as dynamic-link libraries (DLLs). Such procedures, unlike ordinary ones, have the prefix xp_. The server has a standard set of extended procedures, but users can write their own procedures in any programming language. The main thing is to use the programming interface SQL Server Open Data Services API. Extended stored procedures can only reside in the Master database.

Regular stored procedures can also be divided into two types: systemic And custom. System procedures these are standard procedures used to operate the server; custom any procedures created by the user.

1.1. Benefits of Stored Procedures

In the most general case, stored procedures have the following advantages:

• High performance. Is the result of the location of stored procedures on the server. The server, as a rule, is a more powerful machine, so the execution time of the procedure on the server is significantly less than on the workstation. Additionally, the database information and the stored procedure reside on the same system, so there is little time spent transferring records over the network. Stored procedures have direct access to databases, which makes working with information very fast.
• The advantage of developing a system in a client-server architecture. It consists in the possibility of separately creating client and server software. This advantage is key in development and can significantly reduce the time required to complete a project. The code running on the server can be developed separately from the client-side code. In this case, server-side components can be shared with client-side components.
• Security level. Stored procedures can act as a security enhancement tool. You can create stored procedures that perform add, edit, delete, and list display operations, giving you control over every aspect of information access.
• Strengthening server rules that work with data. This is one of the most important reasons application of an intelligent database engine. Stored procedures allow you to apply rules and other logic that help control information entered into the system.

Although SQL is defined as a non-procedural language, SQL Server uses keywords related to managing the flow of procedures. Such keywords are used to create procedures that can be saved for later execution. Stored procedures can be used in place of programs written in standard programming languages ​​(such as C or Visual Basic) that perform operations on a SQL Server database.

Stored procedures are compiled the first time they are executed and stored in a system table in the current database. They are optimized when compiled. In this case, the most The best way access to table information. This optimization takes into account the actual position of the data in the table, available indexes, table load, etc.

Compiled stored procedures can significantly improve system performance. It is worth noting, however, that data statistics from the time a procedure is created to the time it is executed may become out of date, and indexes may become ineffective. Although you can update the statistics and add new, more efficient indexes, the execution plan for the procedure has already been written, that is, the procedure has been compiled, and as a result the way you access the data may no longer be efficient. Therefore, it is possible to recompile procedures each time they are called.

On the other hand, recompilation will take time each time. Therefore, the question of the effectiveness of recompiling a procedure or drawing up a plan for its execution at once is quite delicate and should be considered for each specific case separately.

Stored procedures can be executed either on the local machine or on a remote SQL Server system. This makes it possible to activate processes on other machines and work not only with local databases data, but also with information on several servers.

Application programs written in one of the languages high level, such as C or Visual Basic .NET, can also call stored procedures, which provides an optimal solution for balancing the load between software client part and SQL server.

1.2. Creating Stored Procedures

To create a stored procedure, use the Create Procedure statement. The stored procedure name can be up to 128 characters long, including the # and ## characters. Procedure definition syntax:

CREATE PROC procedure_name [; number]
[(@data_type parameter) [= default_value] ] [,...n]

AS
<Инструкции_SQL>

Let's look at the parameters of this command:

• Procedure_name procedure name; must satisfy the rules for identifiers: its length cannot exceed 128 characters; for local temporary procedures, the name is preceded by the # sign, and for global temporary procedures, the ## signs are used;
• Number An optional integer used to group multiple procedures under one name;
• @parameter data_type a list of procedure parameter names indicating the corresponding data type for each; There can be up to 2100 such parameters. NULL can be passed as a parameter value. All data types can be used except the text, ntext and image types. You can use the Cursor data type as an output parameter (the OUTPUT or VARYING keyword). Parameters with the Cursor data type can only be output parameters;
• VARYING keyword that specifies that the result set is used as the output parameter (used only for the Cursor type);
• OUTPUT indicates that the specified parameter can be used as an output;
• default_value used when a parameter is omitted when calling a procedure; must be a constant and can include wildcard characters (%, _, [, ], ^) and a NULL value;
• WITH RECOMPILE keywords indicating that SQL Server will not write the procedure plan to the cache, but will create it each time it is executed;
• WITH ENCRYPTION keywords indicating that SQL Server will encrypt the procedure before writing it to the Syscomments system table. To make the text of encrypted procedures impossible to recover, it is necessary to remove the corresponding tuples from the syscomments table after encryption;
• FOR REPLICATION keywords indicating that this procedure is created only for replication. This option is not compatible with the WITH RECOMPILE keywords;
• AS start of definition of procedure text;
• <Инструкции_SQL>set of valid SQL statements, limited only by the maximum size of the stored procedure 128 KB. The following statements are invalid: ALTER DATABASE, ALTER PROCEDURE, ALTER TABLE, CREATE DEFAULT, CREATE PROCEDURE, ALTER TRIGGER, ALTER VIEW, CREATE DATABASE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, CREATE VIEW, DISK INIT, DISK RESIZE, DROP D ATABASE DROP DEFAULT, DROP PROCEDURE, DROP RULE, DROP TRIGGER, DROP VIEW, RESOTRE DATABASE, RESTORE LOG, RECONFIGURE, UPDATE STATISTICS.

Let's look at an example of a stored procedure. Let's develop a stored procedure that counts and displays the number of copies of books that are currently in the library:

CREATE Procedure Count_Ex1
- procedure for counting the number of copies of books,
- currently in the library,
- and not in the hands of readers
As
-- set a temporary local variable
Declare @N int
Select @N = count(*) from Exemplar Where Yes_No = "1"
Select @N
GO

Since a stored procedure is a full-fledged database component, then, as you already understood, create new procedure only possible for the current database. When working in SQL Server Query Analyzer, setting the current database is done with a Use statement followed by the name of the database where the stored procedure is to be created. You can also select the current database using the drop-down list.

After a stored procedure is created on the system, SQL Server compiles it and checks the routines that are executed. If any problems arise, the procedure is rejected. Errors must be corrected before retransmission.

SQL Server 2000 uses delayed name resolution, so if a stored procedure contains a call to another procedure that has not yet been implemented, a warning is printed, but the call to the non-existent procedure is preserved.

If you leave a call to an uninstalled stored procedure on the system, the user will receive an error message when they try to execute it.

You can also create a stored procedure with using SQL Server Enterprise Manager:

In order to check the functionality of the created stored procedure, you need to go to Query Analyzer and run the procedure for execution by the operator EXEC<имя процедуры> . The results of running the procedure we created are shown in Fig. 4.

Rice. 4. Running a stored procedure in Query Analyzer

Rice. 5. The result of executing the procedure without the display operator

1.3. Stored Procedure Parameters

Stored procedures are a very powerful tool, but maximum efficiency can only be achieved by making them dynamic. The developer must be able to pass the stored procedure values ​​with which it will work, that is, parameters. Below are the basic principles for using parameters in stored procedures.

• You can define one or more parameters for a procedure.
• Parameters are used as named places to store data, just like variables in programming languages ​​such as C, Visual Basic .NET.
• The parameter name must be preceded by the @ symbol.
• Parameter names are local to the procedure where they are defined.
• Parameters are used to pass information to a procedure when it is executed. They'll go crazy command line after the procedure name.
• If a procedure has several parameters, they are separated by commas.
• To determine the type of information passed as a parameter, system or user data types are used.

Below is the definition of a procedure that has one input parameter. Let's change the previous task and count not all copies of books, but only copies of a specific book. Our books are uniquely identified by a unique ISBN, so we will pass this parameter to the procedure. In this case, the text of the stored procedure will change and will look like this:

Create Procedure Count_Ex(@ISBN varchar(14))
As
Declare @N int
Select @N
GO

When we launch this procedure for execution, we must pass it the value of the input parameter (Fig. 6).

Rice. 6. Starting a procedure with passing a parameter

To create multiple versions of the same procedure that have the same name, follow the base name with a semicolon and an integer. How to do this is shown in following example, which describes creating two procedures with the same name, but with different numbers versions (1 and 2). The number is used to control which version of this procedure is being executed. If no version number is specified, the first version of the procedure is executed. This option is not shown in the previous example, but is still available for your application.

Both procedures use a print statement to print a message identifying the version. The first version counts the number of free copies, and the second the number of copies on hand for a given book.

The text of both versions of the procedures is given below:

CREATE Procedure Count_Ex_all; 1
(@ISBN varchar(14))
-- procedure for counting free copies of a given book
As
Declare @N int
Select @N = count(*) from Exemplar Where ISBN = @ISBN and Yes_No = "1"
Select @N
--
GO
--
CREATE Procedure Count_Ex_all; 2
(@ISBN varchar(14))
-- procedure for counting free copies of a given book
As
Declare @N1 int
Select @N1 = count(*) from Exemplar Where ISBN = @ISBN and Yes_No = "0"
Select @N1
GO

The results of performing the procedure with different versions are shown in Fig. 7.

Rice. 7. Launch results different versions the same stored procedure

When writing multiple versions, keep the following restrictions in mind: Since all versions of a procedure are compiled together, all local variables are considered shared. Therefore, if this is required by the processing algorithm, it is necessary to use different names of internal variables, which is what we did by calling the variable @N in the second procedure with the name @N1.

The procedures we wrote do not return a single parameter, they simply display the resulting number on the screen. However, most often we need to get a parameter for further processing. There are several ways to return parameters from a stored procedure. The simplest one is to use the RETURN operator. This operator will return a single numeric value. But we must specify the variable name or expression that is assigned to the return parameter. The following are the values ​​returned by the RETURN statement and are reserved by the system:

Code	Meaning
0	Everything is fine
1	Object not found
2	Data type error
3	The process fell victim to a deadlock
4	access error
5	Syntax error
6	Some error
7	Error with resources (no space)
8	A recoverable internal error has occurred
9	System limit reached
10	Incorrigible violation of internal integrity
11	The same
12	Table or index destruction
13	Database destruction
14	Hardware error

Thus, in order not to contradict the system, we can only return positive integers through this parameter.

For example, we can change the text of the previously written stored procedure Count_ex as follows:

Create Procedure Count_Ex2(@ISBN varchar(14))
As
Declare @N int
Select @N = count(*) from Exemplar
Where ISBN = @ISBN and YES_NO = "1"
-- return the value of the @N variable,
-- if the value of the variable is not defined, return 0
Return Coalesce(@N, 0)
GO

Now we can get the value of the @N variable and use it for further processing. In this case, the return value is assigned to the stored procedure itself, and in order to parse it, you can use the following stored procedure call statement format:

Exec<переменная> = <имя_процедуры> <значение_входных_параметров>

An example of calling our procedure is shown in Fig. 8.

Rice. 8. Passing the return value of a stored procedure to a local variable

Stored procedure input parameters can use a default value. This value will be used if the parameter value was not specified when calling the procedure.

The default value is specified using an equal sign after the description of the input parameter and its type. Consider a stored procedure that counts the number of copies of books of a given year of publication. The default release year is 2006.

CREATE PROCEDURE ex_books_now(@year int = 2006)
-- counting the number of copies of books of a given year of publication
AS
Declare @N_books int
select @N_books = count(*) from books, exemplar
where Books.ISBN = exemplar.ISBN and YEARIZD = @year
return coalesce(@N_books, 0)
GO

In Fig. Figure 9 shows an example of calling this procedure with and without specifying an input parameter.

Rice. 9. Calling a stored procedure with and without a parameter

All of the above examples of using parameters in stored procedures provided only input parameters. However, the parameters may also be output. This means that the value of the parameter after the procedure completes will be passed to the person who called this procedure (another procedure, trigger, command package, etc.). Naturally, in order to receive an output parameter, when calling, you should specify not a constant, but a variable as the actual parameter.

Note that defining a parameter as an output parameter in a procedure does not obligate you to use it as such. That is, if you specify a constant as the actual parameter, then no error will occur and it will be used as a normal input parameter.

To indicate that a parameter is an output, the OUTPUT statement is used. This keyword is written after the parameter description. When describing the parameters of stored procedures, it is advisable to specify the values ​​of the output parameters after the input ones.

Let's look at an example of using output parameters. Let's write a stored procedure that, for a given book, counts the total number of its copies in the library and the number of free copies. We won't be able to use the RETURN statement here because it only returns one value, so we need to define the output parameters here. The stored procedure text might look like this:

CREATE Procedure Count_books_all
(@ISBN varchar(14), @all int output, @free int output)
-- procedure for counting the total number of copies of a given book
-- and the number of free copies
As
-- counting the total number of copies
Select @all = count(*) from Exemplar Where ISBN = @ISBN
Select @free = count(*) from Exemplar Where ISBN = @ISBN and Yes_No = "1"
GO

An example of this procedure is shown in Fig. 10.

Rice. 10. Testing a stored procedure with output parameters

As mentioned earlier, in order to obtain the values ​​of the output parameters for analysis, we must set them to variables, and these variables must be described by the Declare operator. The last output statement allowed us to simply print the resulting values ​​to the screen.

Procedure parameters can even be variables of type Cursor . To do this, the variable must be described as a special data type VARYING, without binding to the standard ones system types data. In addition, it must be indicated that this is a variable of type Cursor .

Let's write a simple procedure that displays a list of books in our library. Moreover, if there are no more than three books, then we display their names within the procedure itself, and if the list of books exceeds the specified number, then we pass them as a cursor to the calling program or module.

The text of the procedure looks like this:

CREATE PROCEDURE GET3TITLES
(@MYCURSOR CURSOR VARYING OUTPUT)
-- procedure for printing book titles with a cursor
AS
-- define a local variable of type Cursor in the procedure
SET @MYCURSOR = CURSOR
FOR SELECT DISTINCT TITLE
FROM BOOKS
-- open the cursor
OPEN @MYCURSOR
-- describe internal local variables
DECLARE @TITLE VARCHAR(80), @CNT INT
--- set the initial state of the book counter
SET @CNT = 0
-- go to the first line of the cursor
-- while there are cursor lines,
-- that is, as long as the transition to a new line is correct
WHILE (@@FETCH_STATUS = 0) AND (@CNT<= 2) BEGIN
PRINT @TITLE
FETCH NEXT FROM @MYCURSOR INTO @TITLE
-- change the state of the book counter
SET @CNT = @CNT + 1
END
IF @CNT = 0 PRINT "NO MATCHING BOOKS"
GO

An example of calling this stored procedure is shown in Fig. eleven.

In the calling procedure, the cursor must be declared as a local variable. Then we called our procedure and passed it the name of a local variable of type Cursor. The procedure started working and displayed the first three names on the screen, and then transferred control to the calling procedure, and it continued processing the cursor. To do this, she organized a While loop using the global variable @@FETCH_STATUS, which monitors the state of the cursor, and then in the loop displayed all other lines of the cursor.

In the output window we see increased spacing between the first three lines and subsequent titles. This interval just shows that control has been transferred to an external program.

Note that the @TITLE variable, being local to the procedure, will be destroyed when it terminates, so it is declared again in the block that calls the procedure. The creation and opening of the cursor in this example occurs in a procedure, and the closing, destruction, and additional processing are performed in the command block in which the procedure is called.

The easiest way to view the text of a procedure, change or delete it is using the Enterprise Manager graphical interface. But you can also do this using special Transact-SQL system stored procedures. In Transact-SQL, you can view a procedure definition using the sp_helptext system procedure, and you can use the sp_help system procedure to display control information about the procedure. The sp_helptext and sp_help system procedures are also used to view database objects such as tables, rules, and default settings.

Information about all versions of one procedure, regardless of the number, is displayed immediately. Deleting different versions of the same stored procedure also occurs simultaneously. The following example shows how version 1 and version 2 definitions of Count_Ex_all are printed when its name is specified as a parameter to the sp_helptext system procedure (Figure 12).

Rice. 12. Viewing stored procedure text using a system stored procedure

The system procedure SP_HELP displays the characteristics and parameters of the created procedure in the following form:

Name Owner Type	Created_datetime
Count_books_all dbo stored procedure	2006-12-06 23:15:01.217
Parameter_name Type Length Prec.	Scale	Param_order Collation
@ISBN varchar 14 14	NULL	1	Cyrillic_General_CI_AS
@all int 4 10	0	2	NULL
@free int 4 10	0	3	NULL

Try to decipher these parameters yourself. What are they talking about?

1.4. Compiling a Stored Procedure

The advantage of using stored procedures to execute a set of Transact-SQL statements is that they are compiled the first time they are executed. During the compilation process, Transact-SQL statements are converted from their original symbolic representation into executable form. Any objects accessed in the procedure are also converted to an alternate representation. For example, table names are converted to object identifiers, and column names are converted to column identifiers.

The execution plan is created in the same way as for executing a single Transact-SQL statement. This plan contains, for example, indexes used to read rows from the tables accessed by the procedure. The procedure's execution plan is stored in the cache and used each time it is called.

Note: The procedure cache can be sized to contain most or all of the procedures available for execution. This will save the time required to regenerate the procedure plan.

1.5. Automatic recompilation

Typically the execution plan is located in the procedure cache. This allows you to increase the performance of its execution. However, under some circumstances the procedure is automatically recompiled.

• The procedure is always recompiled when SQL Server starts. This usually occurs after the operating system is restarted and the first time the procedure is executed after creation.
• A procedure's execution plan is always automatically recompiled if the index on the table accessed by the procedure is dropped. Because the current plan accesses an index that no longer exists to read table rows, a new execution plan must be created. Procedure queries will only be executed if it is updated.
• Compilation of the execution plan also occurs if another user is currently working with this plan located in the cache. An individual copy of the execution plan is created for the second user. If the first copy of the plan were not busy, there would be no need to create a second copy. When a user completes a procedure, the execution plan is available in the cache to another user who has the appropriate access permission.
• A procedure is automatically recompiled if it is deleted and recreated. Because the new procedure may differ from the old version, any copies of the execution plan in the cache are removed and the plan is recompiled.

SQL Server strives to optimize stored procedures by caching the most heavily used procedures. Therefore, the old execution plan loaded into the cache can be used instead of the new plan. To prevent this problem, you should delete and recreate the stored procedure, or stop and restart SQL Server. This will clear the procedure cache and eliminate the possibility of working with an old execution plan.

The procedure can also be created with the WITH RECOMPILE option. In this case, it will be automatically recompiled every time it is executed. The WITH RECOMPILE option should be used in cases where the procedure accesses very dynamic tables whose rows are frequently added, deleted, or updated, since this causes significant changes to the indexes defined on the tables.

If procedures are not automatically recompiled, you can force them to do so. For example, if the statistics used to determine whether an index can be used in a given query are updated, or if a new index is created, a forced recompilation must be performed. To force a recompilation, use the WITH RECOMPILE clause in the EXECUTE statement:

EXECUTE procedure_name;
AS
<инструкции Transact-SQL>
WITH RECOMPILE

If the procedure operates with parameters that control the order in which it is executed, you should use the WITH RECOMPILE option. If the parameters of a stored procedure can determine the best path for executing it, it is recommended that you create an execution plan as you run it, rather than creating one the first time you call the procedure for use in all subsequent calls.

Note: Sometimes it can be difficult to determine whether to use the WITH RECOMPILE option when creating a procedure or not. If in doubt, it is best not to use this option, since recompiling the procedure each time it is executed will waste very valuable CPU time. If you need to recompile a stored procedure in the future, you can do so by adding a WITH RECOMPILE clause to the EXECUTE statement.

You cannot use the WITH RECOMPILE option in a CREATE PROCEDURE statement that contains a FOR REPLICATION option. Use this option to create a procedure that runs during the replication process.

1.6. Nesting of stored procedures

Stored procedures can call other stored procedures, but there is a limit on the level of nesting. The maximum nesting level is 32. The current nesting level can be determined using the @@NESTLEVEL global variable.

2. User Defined Functions (UDF)

MS SQL SERVER 2000 has many predefined functions that allow you to perform various actions. However, there may always be a need to use some specific functions. To do this, starting with version 8.0 (2000), it became possible to describe user defined functions (UDF) and store them as a full-fledged database object, along with stored procedures, views, etc.

The convenience of using user-defined functions is obvious. Unlike stored procedures, functions can be embedded directly in a SELECT statement, and can be used both to retrieve specific values ​​(in the SELECT clause) and as a data source (in the FROM clause).

When using UDFs as data sources, their advantage over views is that UDFs, unlike views, can have input parameters that can be used to influence the outcome of the function.

User-defined functions can be of three types: scalar functions, inline functions And multi-statement functions that return a table result. Let's take a closer look at all these types of functions.

2.1. Scalar functions

Scalar functions return a single scalar result. This result can be any of the types described above, except for the text, ntext, image, and timestamp types. This is the simplest type of function. Its syntax is as follows:

RETURNS scalar_data_type

BEGIN
body_function
RETURN scalar_expression
END

• The ENCRYPTION parameter has already been described in the section on stored procedures;
• SCHEMABINDING binds a function to a schema. This means that you cannot delete the tables or views that the function is based on without deleting or modifying the function itself. You also cannot change the structure of these tables if the part being changed is used by a function. Thus, this option allows you to eliminate situations where the function uses some tables or views, and someone, without knowing it, deleted or changed them;
• RETURNS scalar_data_type describes the data type that the function returns;
• scalar_expression an expression that directly returns the result of a function. It must be of the same type as the one described after RETURNS;
• function_body set of Transact-SQL instructions.

Let's look at examples of using scalar functions.

Create a function that will select the smallest of two integers supplied as input as parameters.

Let the function look like this:

CREATE FUNCTION min_num(@a INT, @b INT)
RETURNS INT
BEGIN
DECLARE @c INT
IF @a< @b SET @c = @a
ELSE SET @c = @b
RETURN @c
END

Let's now execute this function:

SELECT dbo.min_num(4, 7)

As a result, we get the value 4.

You can use this function to find the smallest of the table column values:

SELECT min_lvl, max_lvl, min_num(min_lvl, max_lvl)
FROM Jobs

Let's create a function that will receive a datetime type parameter as input and return the date and time corresponding to the beginning of the specified day. For example, if the input parameter is 09.20.03 13:31, then the result will be 09.20.03 00:00.

CREATE FUNCTION dbo.daybegin(@dat DATETIME)
RETURNS smalldatetime AS
BEGIN
RETURN CONVERT(datetime, FLOOR(convert(FLOAT, @dat)))
END

Here the CONVERT function performs type conversion. First, the datetime type is cast to FLOAT. With this reduction, the integer part is the number of days counting from January 1, 1900, and the fractional part is the time. Next, it is rounded to a smaller integer using the FLOOR function and converted to a date-time type.

Let's check the function:

SELECT dbo.daybegin(GETDATE())

Here GETDATE() is a function that returns the current date and time.

Previous functions used only input parameters in their calculations. However, you can also use data stored in a database.

Let's create a function that will take two dates as parameters: the beginning and end of a time interval and calculate the total sales revenue for this interval. The sale date and quantity will be taken from the Sales table, and the prices for the titles being sold will be taken from the Titles table.

CREATE FUNCTION dbo.SumSales(@datebegin DATETIME, @dateend DATETIME)
RETURNS Money
AS
BEGIN
DECLARE @Sum Money
SELECT @Sum = sum(t.price * s.qty)

RETURN @Sum
END

2.2. Inline functions

This type of function returns as a result not a scalar value, but a table, or rather a data set. This can be very convenient in cases where the same type of subquery is often executed in different procedures, triggers, etc. Then, instead of writing this query everywhere, you can create a function and use it in the future.

Functions of this type are even more useful in cases where you want the returned table to depend on input parameters. As you know, views cannot have parameters, so only inline functions can solve this kind of problem.

The peculiarity of inline functions is that they can contain only one request in their body. Thus, functions of this type are very similar to views, but can additionally have input parameters. Inline function syntax:

CREATE FUNCTION [owner.]function_name
([(@parameter_name scalar_data_type [= default_value]) [, n]])
RETURNS TABLE

RETURN [(<запрос>)]

The function definition states that it will return a table;<запрос>this is the request, the result of which will be the result of the function.

Let's write a function similar to the scalar function from the last example, but returning not only the summing result, but also sales rows, including the date of sale, book title, price, number of pieces and sale amount. Only those sales that fall within a given time period should be selected. Let's encrypt the text of the function so that other users can use it, but cannot read and correct it:

CREATE FUNCTION Sales_Period (@datebegin DATETIME, @dateend DATETIME)
RETURNS TABLE
WITH ENCRYPTION
AS
RETURN (
SELECT t.title, t.price, s.qty, ord_date, t.price * s.qty as stoim
FROM Titles t JOIN Sales s ON t.title_Id = s.Title_ID
WHERE ord_date BETWEEN @datebegin and @dateend
)

Now let's call this function. As already mentioned, it can only be called in the FROM clause of the SELECT statement:

SELECT * FROM Sales_Period("09/01/94", "09/13/94")

2.3. Multistatement functions that return a table result

The first type of functions considered allowed the use of as many Transact-SQL statements as desired, but returned only a scalar result. The second type of function could return tables, but its body represents only one query. Multi-statement functions that return a table result allow you to combine the properties of the first two functions, that is, they can contain many Transact-SQL statements in the body and return a table as a result. Multistatement function syntax:

CREATE FUNCTION [owner.]function_name
([(@parameter_name scalar_data_type [= default_value]) [,... n]])
RETURNS @result variable_name TABLE
<описание_таблицы>

BEGIN
<тело_функции>
RETURN
END

• TABLE<описание_таблицы> describes the structure of the returned table;
• <описание_таблицы> contains a list of columns and constraints.

Now let's look at an example that can only be done using functions of this type.

Let there be a tree of directories and files contained in them. Let this entire structure be described in the database in the form of tables (Fig. 13). Essentially, here we have a hierarchical structure for directories, so the diagram shows the relationship of the Folders table to itself.

Rice. 13. Database structure to describe the hierarchy of files and directories

Now let's write a function that will take a directory identifier as input and output all the files that are stored in it and in all directories down the hierarchy. For example, if the directories Faculty1, Faculty2, etc. are created in the Institute directory, they contain department directories, and each directory contains files, then when we specify the Institute directory identifier as a parameter to our function, a list of all files for all these directories. For each file, the name, size and creation date should be displayed.

The problem cannot be solved using an inline function, since SQL is not designed to perform hierarchical queries, so one SQL query is not enough. A scalar function cannot be used either, since the result must be a table. This is where a multi-statement function that returns a table will come to our aid:

CREATE FUNCTION dbo.GetFiles(@Folder_ID int)
RETURNS @files TABLE(Name VARCHAR(100), Date_Create DATETIME, FileSize INT) AS
BEGIN
DECLARE @tmp TABLE(Folder_Id int)
DECLARE @Cnt INT
INSERT INTO @tmp values(@Folder_ID)
SET @Cnt = 1
WHILE @Cnt<>0 BEGIN
INSERT INTO @tmp SELECT Folder_Id
FROM Folders f JOIN @tmp t ON f.parent=t.Folder_ID
WHERE F.id NOT IN(SELECT Folder_ID FROM @tmp)
SET @Cnt = @@ROWCOUNT
END
INSERT INTO @Files(Name, Date_Create, FileSize)
SELECT F.Name, F.Date_Create, F.FileSize
FROM Files f JOIN Folders Fl on f.Folder_id = Fl.id
JOIN @tmp t on Fl.id = t.Folder_Id
RETURN
END

Here, in a loop, all subdirectories at all nesting levels are added to the @tmp variable until there are no more subdirectories left. The @Files result variable then records all the necessary attributes of the files located in the directories listed in the @tmp variable.

Tasks for independent work

You must create and debug five stored procedures from the following required list:

Procedure 1. Increasing the deadline for submitting copies of the book by a week if the current due date lies within the range from three days before the current date to three days after the current date.

Procedure 2. Counting the number of free copies of a given book.

Procedure 3. Checking the existence of a reader with a given last name and date of birth.

Procedure 4. Entering a new reader, checking his existence in the database and determining his new library card number.

Procedure 5. Calculation of fines in monetary terms for debtor readers.

Brief description of procedures

Procedure 1. Increasing the deadline for delivery of books

For each record in the Exemplar table, it is checked whether the book's due date falls within the specified time interval. If it does, the return date for the book is extended by a week. When performing the procedure, you must use the function for working with dates:

DateAdd(day,<число добавляемых дней>, <начальная дата>)

Procedure 2. Counting the number of free copies of a given book

The input parameter of the procedure is ISBN the unique cipher of the book. The procedure returns 0 (zero) if all copies of this book are in the hands of readers. The procedure returns the value N, equal to the number of copies of the book that are currently in the hands of readers.

If a book with the given ISBN is not in the library, then the procedure returns 100 (minus one hundred).

Procedure 3. Checking the existence of a reader with a given last name and date of birth

The procedure returns the library card number if a reader with such data exists, and 0 (zero) otherwise.

When comparing date of birth, you must use the Convert() conversion function to convert the date of birth, a Varchar(8) character variable used as an input parameter to the procedure, into datatime, which is used in the Readers table. Otherwise, the comparison operation when searching for a given reader will not work.

Procedure 4: Entering a New Reader

The procedure has five input and three output parameters.

Input parameters:

• Full name with initials;
• Address;
• Date of Birth;
• Home phone;
• The phone is working.

Output parameters:

• Library card number;
• An indication of whether the reader was previously registered in the library (0 was not, 1 was);
• The number of books a reader owns.

Procedure 5. Calculation of fines in monetary terms for debtor readers

The procedure works with a cursor that contains a list of library card numbers of all debtors. During the work process, a global temporary table ##DOLG should be created, in which for each debtor his total debt in monetary terms for all books that he held longer than the return period will be entered. Cash compensation is calculated at 0.5% of the price per book per day of delay.

Work order

• copies of screens (screenshots) confirming changes made to the databases;
• the contents of database tables that are required to verify correct operation;
• text of the stored procedure with comments;
• the process of running a stored procedure and outputting the results of the work.

Additional tasks

The following additional stored procedures are for individual jobs.

Procedure 6. Counting the number of books on a given subject area that are currently available in the library in at least one copy. The subject area is passed as an input parameter.

Procedure 7. Entering a new book indicating the number of copies. When entering copies of a new book, be sure to enter their correct accession numbers. Think about how you can do this. As a reminder, you have the Max and Min functions, which let you find the maximum or minimum value of any numeric attribute using a Select query.

Procedure 8. Formation of a table with a list of debtor readers, that is, those who should have returned books to the library, but have not yet returned them. In the resulting table, each debtor reader should appear only once, regardless of how many books he owes. In addition to your full name and library card number, you must indicate your address and telephone number in the resulting table.

Procedure 9. Search for a free copy using a given book title. If there is a free copy, the procedure returns the inventory number of the copy; if not, then the procedure returns a list of readers who have this book, indicating the date of return of the book and the reader's phone number.

Procedure 10. Displaying a list of readers who do not currently have any books in their hands. Please indicate your name and phone number in the list.

Procedure 11. Displaying a list of books indicating the number of copies of a given book in the library and the number of free copies at the moment.

print version