SQL - Lesson 15. Stored procedures. Part 1.

As a rule, when working with a database, we use the same queries, or a set of sequential queries. Stored procedures allow you to combine a sequence of queries and store them on the server. This is very handy tool, and now you will see this. Let's start with the syntax:

CREATE PROCEDURE procedure_name (parameters) begin statements end

Parameters are the data that we will pass to the procedure when it is called, and operators are the requests themselves. Let's write our first procedure and make sure it's convenient. In lesson 10, when we added new records to the shop database, we used a standard query to add the form:

INSERT INTO customers (name, email) VALUE ("Ivanov Sergey", " [email protected]");

Because We will use a similar request every time we need to add a new customer, so it is quite appropriate to formalize it in the form of a procedure:

CREATE PROCEDURE ins_cust(n CHAR(50), e CHAR(50)) begin insert into customers (name, email) value (n, e); end

Pay attention to how parameters are specified: you need to give a name to the parameter and indicate its type, and in the body of the procedure we already use parameter names. One caveat. As you remember, the semicolon means the end of the request and sends it for execution, which in in this case unacceptable. Therefore, before writing a procedure, it is necessary to redefine the c separator; to "//" so that the request is not sent ahead of time. This is done using the DELIMITER // operator:

Thus, we have indicated to the DBMS that commands should now be executed after //. It should be remembered that redefining the separator is carried out only for one session, i.e. the next time you work with MySql, the separator will again become a semicolon and, if necessary, it will have to be redefined again. Now you can place the procedure:

CREATE PROCEDURE ins_cust(n CHAR(50), e CHAR(50)) begin insert into customers (name, email) value (n, e); end //

So, the procedure has been created. Now, when we need to enter a new customer, we just need to call it, specifying the necessary parameters. To call a stored procedure, use the CALL statement, followed by the name of the procedure and its parameters. Let's add a new customer to our Customers table:

call ins_cust("Sychov Valery", " [email protected]")//

Agree that this is much easier than writing a full request every time. Let's check if the procedure works by looking to see if a new customer has appeared in the Customers table:

Appeared, the procedure works, and will always work until we delete it using the operator DROP PROCEDURE procedure_name.

As mentioned at the beginning of the lesson, procedures allow you to combine a sequence of queries. Let's see how it's done. Remember in lesson 11 we wanted to find out how much the supplier “House of Printing” brought us goods for? To do this, we had to use subqueries, joins, calculated columns, and views. What if we want to know how much goods another supplier brought us? You will have to create new queries, joins, etc. It's easier to write a stored procedure for this action once.

It would seem that the easiest way is to take the view and query for it already written in Lesson 11, combine it into a stored procedure and make the vendor identifier (id_vendor) an input parameter, like this:

CREATE PROCEDURE sum_vendor(i INT) begin CREATE VIEW report_vendor AS SELECT magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND id_incoming= ( SELECT id_incoming FROM incoming WHERE id_vendor=i); SELECT SUM(summa) FROM report_vendor; end //

But the procedure will not work that way. The whole point is that views cannot use parameters. Therefore, we will have to slightly change the sequence of requests. First, we will create a view that will display the vendor id (id_vendor), product id (id_product), quantity (quantity), price (price) and amount (summa) from the three tables Supplies (incoming), Magazine (magazine_incoming), Prices ( prices):

CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND magazine_incoming.id_incoming= incoming .id_incoming;

And then we will create a query that will sum up the amounts of supplies of the supplier we are interested in, for example, with id_vendor=2:

Now we can combine these two queries into a stored procedure, where the input parameter will be the vendor identifier (id_vendor), which will be substituted into the second query, but not into the view:

CREATE PROCEDURE sum_vendor(i INT) begin CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product= prices .id_product AND magazine_incoming.id_incoming= incoming.id_incoming; SELECT SUM(summa) FROM report_vendor WHERE id_vendor=i; end //

Let's check the operation of the procedure with different input parameters:

As you can see, the procedure runs once and then throws an error, telling us that the report_vendor view already exists in the database. This is because when the procedure is called for the first time, it creates a view. When accessed a second time, it tries to create the view again, but it already exists, which is why the error appears. To avoid this there are two options.

The first is to take the representation out of the procedure. That is, we will create the view once, and the procedure will only access it, but not create it. Don’t forget to delete already created in advance this procedure and representation:

DROP PROCEDURE sum_vendor // DROP VIEW report_vendor // CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product = prices.id_product AND magazine_incoming.id_incoming= incoming.id_incoming // CREATE PROCEDURE sum_vendor(i INT) begin SELECT SUM(summa) FROM report_vendor WHERE id_vendor=i; end //

Checking the work:

call sum_vendor(1)// call sum_vendor(2)// call sum_vendor(3)//

The second option is to add a command directly in the procedure that will delete the view if it exists:

CREATE PROCEDURE sum_vendor(i INT) begin DROP VIEW IF EXISTS report_vendor; CREATE VIEW report_vendor AS SELECT incoming.id_vendor, magazine_incoming.id_product, magazine_incoming.quantity, prices.price, magazine_incoming.quantity*prices.price AS summa FROM incoming, magazine_incoming, prices WHERE magazine_incoming.id_product= prices.id_product AND magazine_incoming.id_incoming= incoming .id_incoming; SELECT SUM(summa) FROM report_vendor WHERE id_vendor=i; end //

Before using this option, be sure to remove the sum_vendor procedure and then test the work:

As you can see, complex queries Or is it really easier to formalize their sequence once into a stored procedure, and then simply access it, specifying the necessary parameters. This significantly reduces the code and makes working with queries more logical.

Stored procedure is a special type of Transact-SQL statement package created using 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 information in system directory and its modifications.

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 a 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 repeatedly executing a specific group of statements when connecting to a 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 statements 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, @counter=@quantityDeleteEmployee 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 simple procedure, which displays the personnel numbers and 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 common language runtime, 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 picture below shows graphic diagram the previously outlined steps. Below is more detailed description this process.

First create the required program in some development environment like Visual Studio. Compile the finished program into object code using a 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 that is converted to an integer type data int 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 following example 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 - specifies 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.

Purpose of the work– learn to create and use stored procedures on the database server.

1. Study of all examples, analysis of the results of their execution in the SQL utility Server Management Studio. Checking the presence of created procedures in the current database.

2. Completing all examples and tasks during laboratory work.

3. Completing individual tasks according to options.

Explanations for performing the work

To master stored procedure programming, we use an example database called DB_Books, which was created in laboratory work No. 1. When completing examples and tasks, pay attention to the correspondence of the names of the database, tables and other project objects.

Stored procedures are a set of commands consisting of one or more SQL statements or functions and stored in a database in compiled form.

Types of Stored Procedures

System stored procedures are designed to perform various administrative actions. Almost all server administration activities are performed with their help. We can say that system stored procedures are an interface that provides work with system tables. System stored procedures have the sp_ prefix and are stored in system base data and can be called in the context of any other database.

Custom stored procedures implement certain actions. Stored procedures are 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 short time, 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 are 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 to any connection 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.

Creating, modifying stored procedures

Creating a stored procedure involves solving the following problems: planning access rights. When you create a stored procedure, be aware that it will have the same access rights to database objects as the user who created it; defining the parameters of a stored procedure; stored procedures can have input and output parameters; stored procedure code development. The procedure code can contain a sequence of any SQL commands, including calls to other stored procedures.

The syntax of the operator for creating a new or changing an existing stored procedure in MS SQL Server notation:

( CREATE | ALTER ) PROC[ EDURE] procedure_name [ ;number] [ ( @parameter_name data_type ) [ VARYING ] [ = DEFAULT ] [ OUTPUT ] ] [ ,... n] [ WITH ( RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION ) ] [ FOR REPLICATION] AS sql_statement [ ... 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. Therefore, to place the stored procedure you are creating in a specific database, you must issue the CREATE PROCEDURE command in the context of that database. When accessing objects of the same database from the body of a stored procedure, you can use shortened names, i.e., without specifying the database name. When you need to access objects located in other databases, specifying the database name is mandatory.

To pass input and output data, the parameter names in the stored procedure you create must begin with the @ character. You can specify multiple parameters in a single stored procedure, 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 of parameters of a stored procedure, any types are suitable SQL data, including user-defined ones. However, the CURSOR data type can only be used as the output parameter of a 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 a stored procedure. However, this does not mean that the parameter is not suitable for passing values ​​to a stored procedure. Specifying the OUTPUT keyword instructs the server, when exiting a stored procedure, to assign the current parameter value to the local variable that was specified as the parameter value when the procedure was called. 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 normal parameters are not permitted. The VARYING keyword is used in conjunction with the OUTPUT parameter, which is of type CURSOR. It specifies that the output will be the result set.

The DEFAULT keyword represents the value that the corresponding parameter will take by default. Thus, when calling a procedure, you do not have to explicitly specify the value of the corresponding parameter.

Since the server caches the query execution plan and compiled code, the next time the procedure is called, the ready-made values ​​will be used. However, in some cases it is still necessary to recompile the procedure code. Specifying the RECOMPILE keyword instructs the system to create an execution plan for the stored procedure each time it is called.

The FOR REPLICATION parameter is required when replicating data and including the created stored procedure as an article in a publication. The ENCRYPTION keyword instructs the server to encrypt the stored procedure code, which can provide protection against the use of proprietary algorithms that implement the stored procedure. The AS keyword is placed at the beginning of the stored procedure body itself. The procedure body can use almost all SQL commands, declare transactions, set locks, and call other stored procedures. You can exit a stored procedure using the RETURN command.

Removing a Stored Procedure

DROP PROCEDURE (procedure_name) [,... n]

Executing a Stored Procedure

To execute a stored procedure, use the command: [ [ EXEC [ UTE] procedure_name [ ;number] [ [ @parameter_name= ] ( value | @variable_name) [ OUTPUT ] | [ DEFAULT ] ] [ ,... n]

If the stored procedure call is not the only command in the batch, then the EXECUTE command is required. Moreover, this command is required to call a procedure from the body of another procedure or trigger.

The use of the OUTPUT keyword when calling a procedure is allowed only for parameters that were declared when creating the procedure with keyword OUTPUT.

When the DEFAULT keyword is specified for a parameter when calling a procedure, the default value will be used. Naturally, the specified word DEFAULT is allowed only for those parameters for which a default value is defined.

The syntax of the EXECUTE command shows that parameter names can be omitted when calling a procedure. However, in this case, the user must specify the values ​​for the parameters in the same order in which they were listed when creating the procedure. You cannot assign a default value to a parameter by simply omitting it during enumeration. If you want to omit parameters that have a default value, it is enough to explicitly specify the parameter names when calling the stored procedure. Moreover, in this way you can list parameters and their values ​​in any order.

Note that when calling a procedure, either parameter names with values, or only values ​​without a parameter name are specified. Combining them is not allowed.

Using RETURN in a stored procedure

Allows you to exit the procedure at any point according to a specified condition, and also allows you to convey the result of the procedure as a number, by which you can judge the quality and correctness of the procedure. An example of creating a procedure without parameters:

CREATE PROCEDURE Count_Books AS SELECT COUNT (Code_book) FROM Books GO

Task 1.

EXEC Count_Books

Check the result.

An example of creating a procedure with an input parameter:

CREATE PROCEDURE Count_Books_Pages @Count_pages AS INT AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages GO

Task 2. Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Run it using the command

EXEC Count_Books_Pages 100

Check the result.

An example of creating a procedure with input parameters:

CREATE PROCEDURE Count_Books_Title @Count_pages AS INT , @Title AS CHAR (10 ) AS SELECT COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GO

Task 3. Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Run it using the command

EXEC Count_Books_Title 100 , "P%"

Check the result.

An example of creating a procedure with input parameters and an output parameter:

CREATE PROCEDURE Count_Books_Itogo @Count_pages INT , @Title CHAR (10 ) , @Itogo INT OUTPUT AS SELECT @Itogo = COUNT (Code_book) FROM Books WHERE Pages>= @Count_pages AND Title_book LIKE @Title GO

Task 4. Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Run using the command set:

Sql> Declare @q As int EXEC Count_Books_Itogo 100, "P%", @q output select @q

Check the result.

An example of creating a procedure with input parameters and RETURN:

CREATE PROCEDURE checkname @param INT AS IF (SELECT Name_author FROM authors WHERE Code_author = @param) = "Pushkin A.S." RETURN 1 ELSE RETURN 2

Task 5. Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Run it using the commands:

DECLARE @return_status INT EXEC @return_status = checkname 1 SELECT "Return Status" = @return_status

An example of creating a procedure without parameters to increase the value of a key field in the Purchases table by 2 times:

CREATE PROC update_proc AS UPDATE Purchases SET Code_purchase = Code_purchase* 2

Task 6. Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Run it using the command

EXEC update_proc

An example of a procedure with an input parameter to obtain all information about a specific author:

CREATE PROC select_author @k CHAR (30 ) AS SELECT * FROM Authors WHERE name_author= @k

Task 7.

EXEC select_author "Pushkin A.S." or select_author @k= "Pushkin A.S." or EXEC select_author @k= "Pushkin A.S."

An example of creating a procedure with an input parameter and a default value to increase the value of a key field in the Purchases table by a specified number of times (2 times by default):

CREATE PROC update_proc @p INT = 2 AS UPDATE Purchases SET Code_purchase = Code_purchase * @p

The procedure does not return any data.

Task 8. Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Run it using the commands:

EXEC update_proc 4 or EXEC update_proc @p = 4 or EXEC update_proc --the default value will be used.

An example of creating a procedure with input and output parameters. Create a procedure to determine the number of orders completed during a specified period:

CREATE PROC count_purchases @d1 SMALLDATETIME, @d2 SMALLDATETIME, @c INT OUTPUT AS SELECT @c= COUNT (Code_purchase) FROM Purchases WHERE Date_order BETWEEN @d1 AND @d2 SET @c = ISNULL(@c, 0 )

Task 9. Create this procedure in the Stored Procedures section of the DB_Books database using the SQL server Management Studio utility. Run it using the commands:

DECLARE @c2 INT EXEC count_purchases '01-jun- 2006 ', '01- jul- 2006 ', @c2 OUTPUT SELECT @c2

Options for tasks laboratory work №4

General provisions. In SQL Server Management Studio, create new page for the code (the “Create request” button). Programmatically make the created DB_Books database active using the Use statement. Create stored procedures using Create procedure statements, and define the names of the procedures yourself. Each procedure will execute one SQL query that was executed in the second lab. Moreover, the SQL code of the queries needs to be changed so that they can transmit the values ​​of the fields used to search.

For example, the initial task and request in laboratory work No. 2:

/*Select from the supplier directory (Delivery table) the names of companies, telephone numbers and INN (fields Name_company, Phone and INN), whose company name (field Name_company) is “OJSC MIR”.

SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = "OJSC MIR"

*/ –In this work the following procedure will be created:

CREATE PROC select_name_company @comp CHAR (30 ) AS SELECT Name_company, Phone, INN FROM Deliveries WHERE Name_company = @comp

–To start the procedure, use the command:

EXEC select_name_company "JSC MIR"

List of tasks

Create a new program in SQL Server Management Studio. Programmatically make active the individual database created in laboratory work No. 1 using the Use statement. Create stored procedures using Create procedure statements, and define the names of the procedures yourself. Each procedure will perform one SQL query, which are presented in the form of separate tasks according to options.

Option 1

1. Display a list of employees who have at least one child.

2. Display a list of children who received gifts during the specified period.

3. Display a list of parents who have minor children.

4. Display information about gifts with a value greater than the specified number, sorted by date.

Option 2

1. Display a list of devices with the specified type.

2. Display the number of repaired devices and total cost repairs from the specified specialist.

3. Display a list of device owners and the number of their requests, sorted by the number of requests in descending order.

4. Display information about craftsmen with a rank greater than the specified number or with a hiring date less than the specified date.

Option 3

2. Display a list of sales codes that sold flowers for an amount greater than the specified number.

3. Display the date of sale, amount, seller and flower according to the specified sale code.

4. Display a list of flowers and the variety for flowers with a height greater than the specified number or blooming.

Option 4

1. Display a list of medications with the specified indication for use.

2. Display a list of delivery dates for which more than the specified number of the drug of the same name was sold.

3. Display the delivery date, amount, full name of the manager from the supplier and the name of the medicine by receipt code greater than the specified number.

Option 5

2. Display a list of decommissioned equipment for the specified reason.

3. Display the date of receipt, name of the equipment, full name of the person responsible and the date of write-off for equipment written off during the specified period.

4. Display a list of equipment with a specified type or with a receipt date greater than a certain value

Option 6

1. Display a list of dishes with a weight greater than the specified number.

2. Display a list of products whose names contain the specified word fragment.

3. Display the volume of the product, the name of the dish, the name of the product with the dish code from the specified initial value to a certain final value.

4. Display the order of preparation of a dish and the name of a dish with the amount of carbohydrates greater than a certain value or the amount of calories greater than a specified value.

Option 7

1. Display a list of employees with the specified position.

3. Display the registration date, document type, full name of the registrar and name of the organization for documents registered during the specified period.

4. Display a list of registered documents with a specific document type or with a registration date greater than the specified value.

Option 8

1. Display a list of employees with the specified reason for dismissal.

3. Display the date of registration, reason for dismissal, full name of the employee for documents registered during the specified period.

Option 9

1. Display a list of employees who took leave of the specified type.

2. Display a list of documents with a registration date in the specified period.

3. Display the date of registration, type of leave, full name of the employee for documents registered during the specified period.

4. Display a list of registered documents with a document code in the specified range.

Option 10

1. Display a list of employees with the specified position.

2. Display a list of documents whose content contains the specified word fragment.

3. Display the registration date, document type, full name of the sender and name of the organization for documents registered during the specified period.

4. Display a list of registered documents with the specified document type or with a document code less than a certain value.

Option 11

1. Display a list of employees assigned to the specified position.

2. Display a list of documents with a registration date in the specified period.

3. Display the registration date, position, full name of the employee for documents registered during the specified period.

4. Display a list of registered documents with a document code in the specified range.

Option 12

3. Display a list of people who rented equipment and the number of their requests, sorted by the number of requests in descending order.

Option 13

1. Display a list of equipment with the specified type. 2. Display a list of equipment that has been written off by a specific employee.

3. Display the amount of decommissioned equipment, grouped by equipment type.

4. Display information about employees with a hire date greater than a certain date.

Option 14

1. Print a list of flowers with the specified leaf type.

2. Display a list of receipt codes for which flowers were sold for amounts greater than a certain value.

3. Display the receipt date, amount, name of the supplier and colors by a specific supplier code.

4. Display a list of flowers and variety for flowers with a height greater a certain number or blooming.

Option 15

1. Display a list of clients who checked into the rooms during the specified period.

2. Display the total amount of payments for rooms for each client.

3. Display the arrival date, room type, full name of clients registered during the specified period.

4. Display a list of registered clients in rooms of a certain type.

Option 16

1. Display a list of equipment with the specified type.

2. Display a list of equipment that was rented by a specific client.

3. Display a list of people who rented equipment and the number of their requests, sorted by the number of requests in descending order.

4. Display information about clients sorted by address.

Option 17

1. Display a list of valuables with a purchase price greater than a certain value or a warranty period greater than a specified number.

2. Display a list of locations of material assets whose names contain the specified word.

3. Display the sum of the value of values ​​with a code in the specified range.

4. Display a list of financially responsible persons with the date of employment in the specified range.

Option 18

1. Display a list of repairs performed by a specific technician.

2. Display a list of work stages included in the work whose title contains the specified word.

3. Display the sum of the cost of repair work stages for work with a code in the specified range.

4. Display a list of foremen with the hiring date in the specified range.

Option 19

1. Display a list of medications with a specific indication.

2. Display a list of receipt numbers for which more than a certain number of drugs were sold.

3. Display the date of sale, amount, cashier’s name and medicine on the receipt with the specified number.

4. Display a list of medications and units of measurement for medications with a quantity in the package greater than the specified number or a drug code less than a certain value.

Option 20

1. Display a list of employees with the specified position.

2. Display a list of documents whose content contains the specified word fragment.

3. Display the registration date, document type, full name of the executor and the fact of execution for documents registered during the specified period.

4. Display a list of registered documents with the specified document type or document code in a certain range.