Stored procedures in T-SQL - creation, modification, deletion. Creating stored procedures in microsoft sql server Sql create a stored procedure

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 an additional option for 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 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 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, @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 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. The following is a more detailed description of this process.

First, create the required program in a development environment such as 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. Using these directives allows you to specify class names in source code 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 data type 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 the 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.

When working with SQL Server, users can create their own procedures that implement certain actions. Stored procedures are full-fledged database objects, and therefore each of them is stored in a specific database. A direct call to a stored procedure is only possible if it is done in the context of the database where the procedure is located.

Types of Stored Procedures

SQL Server has several 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, which ultimately comes down to changing, adding, deleting and retrieving data from system tables of both user and system databases. System stored procedures are prefixed with sp_, 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 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 starting 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.

Triggers

Triggers are a type of stored procedure. They are executed when a data manipulation language (DML) operator is executed on the table. Triggers are used to check data integrity and also to roll back transactions.

Trigger is a compiled SQL procedure, the execution of which is conditioned by the occurrence of certain events within the relational database. The use of triggers is, for the most part, very convenient for database users. Still, their use often involves additional resource costs for I/O operations. When the same results (with much less overhead) can be achieved using stored procedures or application programs, the use of triggers is not practical.

Triggers is a special SQL server tool used to maintain data integrity in a database. Integrity constraints, rules, and defaults may not always achieve the desired level of functionality. It is often necessary to implement complex data verification algorithms to ensure their reliability and reality. In addition, sometimes you need to monitor changes in table values so that the associated data can be modified as needed. Triggers can be thought of as a kind of filters that come into effect after all operations have been completed in accordance with rules, standard values, etc.

Trigger is a special type of stored procedure that is launched automatically by the server when an attempt is made to change data in tables to which triggers are associated. Every Trigger is tied to a specific table. All data modifications it makes are considered as one transaction. If an error or data integrity violation is detected, the transaction is rolled back. Changes are therefore prohibited. Any changes already made by the trigger are also undone.

Creates trigger only the database owner. This restriction allows you to avoid accidental changes to the structure of tables, ways of connecting other objects to them, etc.

Trigger It is a very useful and at the same time dangerous remedy. So, if the logic of its operation is incorrect, you can easily destroy an entire database, so triggers must be debugged very carefully.

Unlike a regular subroutine, trigger is executed implicitly whenever a trigger event occurs, and it has no arguments. Activating it is sometimes called firing a trigger. Using triggers, the following goals are achieved:

Validating the correctness of data entered and enforcing complex data integrity constraints that are difficult, if not impossible, to maintain using integrity constraints set on a table;

issuing warnings reminding you to perform certain actions when updating a table implemented in a certain way;

accumulation of audit information by recording information about the changes made and those persons who performed them;

replication support.

The basic format of the CREATE TRIGGER command is shown below:

<Определение_триггера>::=

CREATE TRIGGER trigger_name

BEFORE | AFTER<триггерное_событие>

ON<имя_таблицы>

<список_старых_или_новых_псевдонимов>]

<тело_триггера>

Trigger events consist of inserting, deleting, and updating rows in a table. In the latter case, you can specify specific table column names for the trigger event. The timing of the trigger is determined using the BEFORE keywords ( Trigger runs before the events associated with it are executed) or AFTER (after they are executed).

The actions performed by the trigger are specified for each row (FOR EACH ROW) covered by the event, or only once for each event (FOR EACH STATEMENT).

Incorrectly written triggers can lead to serious problems, such as dead locks. Triggers can block many resources for long periods of time, so special attention should be paid to minimizing access conflicts.

Trigger can be created only in the current database, but it is possible to access other databases within the trigger, including those located on a remote server.

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 the system database. 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 SQL data types are suitable, 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. The use of any expressions or constants that are allowed for regular parameters is not permitted.

The VARYING keyword is used in conjunction with

Stored procedure stored procedure) is a named database program object. SQL Server has several types of stored procedures.

System stored procedures system stored procedures) are supplied by DBMS developers and are used to perform actions with the system directory or obtain system information. Their names usually begin with the prefix "sp_". You run all types of stored procedures using the EXECUTE command, which can be shortened to EXEC. For example, the sp_helplogins stored procedure, run without parameters, produces two reports about account names (English) logins) and the corresponding users in each database (English) users).

EXEC sp_helplogins;

To give an idea of the actions performed using system stored procedures, Table 10.6 shows some examples. In total, there are more than a thousand system stored procedures in SQL Server.

Table 10.6

SQL Server System Stored Procedure Examples

The user can create stored procedures in user databases and in the database for temporary objects. In the latter case, the stored procedure will be temporal. As with temporary tables, the name of a temporary stored procedure must begin with the prefix "#" if it is a local temporary stored procedure, or with "##" if it is a global one. A local temporary procedure can only be used within the connection in which it was created, a global one can also be used within other connections.

SQL Server programmable objects can be created using either Transact-SQL tools or assemblies (English) assembly) in the CRL (Common Language Runtime) environment of the Microsoft.Net Framework. This tutorial will only cover the first method.

To create stored procedures, use the CREATE PROCEDURE (can be shortened to PROC) statement, the format of which is given below:

CREATE (PROC I PROCEDURE) proc_name [ ; number ]

[(gparameter data_type)

[“default] |

[WITH [ ,...n ] ]

[FOR REPLICATION]

AS ([ BEGIN ] sql_statement [;] [ ...n ] [ END ] )

If a stored procedure (or trigger, function, view) is created with the ENCRYPTION option, its code is transformed in such a way that the text becomes unreadable. At the same time, as noted in, the algorithm used was transferred from earlier versions of SQL Server and cannot be considered as a reliable protection algorithm - there are utilities that allow you to quickly perform the reverse conversion.

The RECOMPILE option specifies that the system will recompile the text each time the procedure is called. In the normal case, the procedure compiled on the first run is stored in the cache, which allows for increased performance.

EXECUTE AS specifies the security context in which the procedure is to be executed. Next, one of the values f CALLER | SELF | OWNER | "user_name"). CALLER is the default and means that the code will be executed in the security context of the user calling this module. Accordingly, the user must have permissions not only to the programmable object itself, but also to other database objects affected by it. EXECUTE AS SELF means using the context of the user creating or modifying the programmable object. OWNER specifies that the code will be executed in the context of the procedure's current owner. If no owner is specified for it, then the owner of the schema to which it belongs is assumed. EXECUTE AS "user_name" allows you to explicitly specify the username (in single quotes).

Parameters can be specified for a procedure. These are local variables used to pass values to a procedure. If a parameter is declared with the keyword OUTPUT (or OUT for short), it is an output value: the value given to it in the procedure after its completion can be used by the program that called the procedure. The READONLY keyword means that the value of the parameter cannot be changed inside the stored procedure.

Parameters can be assigned default values, which will be used if the parameter value is not explicitly specified when calling the procedure. Let's look at an example:

CREATE PROC surma (@a int, @b int=0,

©result int OUTPUT) AS

SET @result=0a+0b

We have created a procedure with three parameters, and the @b parameter has a default value of =0, and the @result parameter is an output parameter: it returns the value to the calling program. The actions performed are quite simple - the output parameter receives the value of the sum of two input ones.

When working in SQL Server Management Studio, the created stored procedure can be found in the programmable database objects section (English) Programmability) in the subsection for stored procedures (Fig. 10.2).

When calling a procedure, you can use both variables and constants as input parameters. Let's look at two examples. In the first, the input parameters of the procedure are explicitly specified as constants, and the OUTPUT keyword is specified for the output parameter in the call. The second option uses the value of a variable as the first input parameter, and specifies that the default value should be used for the second parameter using the DEFAULT keyword:

Rice. 10.2.

DECLARE @с int;

EXEC summa 10.5,@c OUTPUT;

PRINT 0c; – 15 will be displayed

DECLARE Gi int = 5;

– when calling, use the default value

EXEC summa Gi,DEFAULT , 0c OUTPUT;

PRINT 0c; – 5 will be displayed

Let us now consider an example with the analysis of the return code with which the procedure ends. Suppose we need to calculate how many books in the Bookl table are published in a given range of years. Moreover, if the initial year is greater than the final year, the procedure returns “1” and does not count, otherwise, we count the number of books and return 0:

CREATE PROC dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) AS

IF 0FirsYear>0LastYear RETURN 1

SET @result= (SELECT COUNT(*) FROM dbo.Bookl

WHERE BETWEEN 0FirsYear AND 0LastYear);

Let's consider a variant of calling this procedure, in which the return code is stored in the integer variable 0ret, after which its value is analyzed (in this case it will be 1). The CAST function used in the PRINT statement is used to convert the value of the integer variable Gres to a string type:

DECLARE 0ret int, Gres int

EXEC Gret = rownum 2004, 2002, Gres OUT;

IF 0ret=l PRINT "Start year is greater than end year"

PRINT "Number of books" + CAST(Gres as varchar(20))

Stored procedures can not only read data from a table, but also modify data and even create tables and a number of other database objects.

However, you cannot create schemas, functions, triggers, procedures, and views from a stored procedure.

The following example illustrates both these capabilities and issues related to the scope of temporary objects. The following stored procedure checks for the existence of temporary table #TaL2; if this table does not exist, it creates it. After this, the values of two columns are entered into table #TaL2, and the contents of the table are displayed using the SELECT statement:

CREATE PROC My_Procl (@id int, @name varchar(30))

IF OBJECT_ID("tempdb.dbo.#Tab21) IS NULL

INSERT INTO dbo.#Tab2 (id, name)VALUES (0id,0name)

SELECT * FROM dbo. #Tab2 –№1

Before calling the stored procedure for the first time, we will create the temporary table #TaL2 used in it. Pay attention to the EXEC operator. In the previous examples, parameters were passed to the procedure “by position”, but in this case a different format for passing parameters is used – “by name”, the name of the parameter and its value are explicitly indicated:

CREATE TABLE dbo.#Tab2 (id int, name varchar(30));

EXEC My_Procl 0name="lvan", 0id=2;

SELECT * FROM dbo.#Tab2; –№2

In the above example, the SELECT statement will be processed twice: the first time – inside the procedure, the second time – from the calling code fragment (marked with the comment “No. 2”).

Before the second call to the procedure, we will delete the temporary table #TaL2. Then a temporary table of the same name will be created from the stored procedure:

DROP TABLE dbo.#Tab2;

EXEC My_Procl 0name="Ivan", 0id=2;

SELECT * FROM dbo.#Tab2; –№2

In this case, only the SELECT statement located inside the procedure (with the comment “Xa 1”) will display data. Executing SELECT "No. 2" will result in an error, since the temporary table created in the stored procedure will already be deleted from the tempdb database at the time the procedure returns.

You can drop a stored procedure using the DROP PROCEDURE statement. Its format is presented below. You can delete several stored procedures with one statement, listing them separated by commas:

DROP (PROC I PROCEDURE) ( procedure ) [

For example, let's delete the previously created summa procedure:

DROP PROC summa;

You can make changes to an existing procedure (and in fact, redefine it) using the ALTER PROCEDURE statement (allowed

abbreviation PROC). With the exception of the ALTER keyword, the format of the statement is essentially the same as that of CREATE PROCEDURE. For example, let's change the dbo procedure. rownum, setting it to execute in the owner's security context:

ALTER PROC dbo.rownum (SFirsYear int,

SLastYear int, Sresult int OUTPUT)

WITH EXECUTE AS Owner – installable option

IF 0FirsYear>0LastYear RETURN 1 ELSE BEGIN

SET 0result= (SELECT COUNT(*) FROM dbo.Bookl

WHERE BETWEEN SFirsYear AND SLastYear);

In some cases, it may be necessary to dynamically generate a command and execute it on the database server. This problem can also be solved using the EXEC operator. The example below retrieves records from the Bookl table if the Year attribute is equal to the value specified by the variable:

DECLARE 0у int = 2000;

EXEC ("SELECT * FROM dbo.Bookl WHERE = "+@y) ;

The execution of dynamically generated instructions creates the preconditions for implementing computer attacks such as “SQL injection” (English) SQL injection). The essence of the attack is that the attacker injects his own SQL code into a dynamically generated query. This typically occurs when the parameters being substituted are taken from the results of user input.

Let's change the previous example slightly:

DECLARE 0у varchar(100);

SET 0у="2ООО"; – we received this from the user

If we assume that we received the string value assigned in the SET statement from the user (no matter how, for example, through a web application), then the example illustrates the “normal” behavior of our code.

DECLARE 0у varchar(100);

SET 0у="2000; DELETE FROM dbo.Book2"; – injection

EXEC("SELECT * FROM dbo.Book2 WHERE ="+0y);

In such cases, it is recommended, if possible, to use the system stored procedure sp_executcsql, which allows you to control the type of parameters, which is one of the barriers to SQL injections. Without considering its format in detail, let's look at an example similar to the one presented earlier:

EXECUTE sp_executesql

N"SELECT * FROM dbo.Bookl WHERE =0y",

This explicitly specifies the type of parameter used in the query, and SQL Server will control it during execution. The letter "N" before the quotation marks indicates that this is a Unicode literal constant, as required by the procedure. A parameter can be assigned not only a constant value, but also the value of another variable.

The concept of stored procedures is defined. Provides examples of creating, modifying, and using stored procedures with parameters. The definition of input and output parameters is given. Examples of creating and calling stored procedures are provided.

The concept of a stored procedure

Stored procedures are groups of interconnected SQL statements, the use of which makes the programmer’s work easier and more flexible, since stored procedure is often much simpler than a sequence of individual SQL statements. Stored procedures are a set of commands consisting of one or more SQL statements or functions and stored in a compiled form in a database. Execution in the database stored procedures Instead of individual SQL statements, the user has the following benefits:

the necessary operators are already contained in the database;
they all passed the stage parsing and are in executable format; before executing a stored procedure SQL Server generates an execution plan for it, performs its optimization and compilation;
stored procedures support modular programming, as they allow you to break large tasks into independent, smaller and easier-to-manage parts;
stored procedures may cause others stored procedures and functions;
stored procedures can be called from other types of application programs;
usually, stored procedures execute faster than a sequence of individual statements;
stored procedures easier to use: they can consist of tens or hundreds of commands, but to run them you just need to specify the name of the desired one stored procedure. This allows you to reduce the size of the request sent from the client to the server, and therefore the load on the network.

Storing procedures in the same place where they are executed reduces the amount of data transferred over the network and improves overall system performance. Application stored procedures simplifies the maintenance of software systems and making changes to them. Typically, all integrity constraints in the form of rules and data processing algorithms are implemented on the database server and are available to the end application as a set stored procedures, which represent the data processing interface. To ensure data integrity, as well as for security purposes, the application usually does not receive direct access to the data - all work with it is carried out by calling certain stored procedures.

This approach makes it very simple to modify data processing algorithms, which immediately become available to all network users, and provides the ability to expand the system without making changes to the application itself: just change stored procedure on the database server. The developer does not need to recompile the application, create copies of it, or instruct users to work with the new version. Users may not even be aware that changes have been made to the system.

Stored procedures exist independently of tables or any other database objects. They are called by the client program, another stored procedure or trigger. The developer can manage access rights to stored procedure, allowing or prohibiting its execution. Change code stored procedure permitted only by its owner or a member of a fixed database role. If necessary, you can transfer ownership of it from one user to another.

Stored procedures in MS SQL Server environment

When working with SQL Server, users can create their own procedures that implement certain actions. Stored procedures are full-fledged database objects, and therefore each of them is stored in a specific database. Direct call 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 the system database. 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 ) PROC procedure_name [;number] [(@parameter_name data_type ) [=default] ][,...n] AS sql_operator [...n]

Let's look at the parameters of this command.

The VARYING keyword is used in conjunction with the OUTPUT parameter, which is of type CURSOR. It determines that output parameter there will be a result set.

The DEFAULT keyword represents the value that the corresponding default parameter. 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 stored procedure every time she calls.

The FOR REPLICATION parameter is required when replicating data and enabling the created stored procedure as an article for publication.

The ENCRYPTION keyword instructs the server to encrypt the code stored procedure, which can provide protection against the use of proprietary algorithms that implement the work stored procedure.

The AS keyword is placed at the beginning of the body itself stored procedure, i.e. a set of SQL commands with the help of which this or that action will be implemented. In the body of the procedure, almost all SQL commands can be used, transactions can be declared, locks can be set, and others can be called. stored procedures. Exit from stored procedure can be done using the RETURN command.

Removing a Stored Procedure carried out by the command:

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

Executing a Stored Procedure

For execute a stored procedure The command used is:

[[ EXEC [ UTE] procedure_name [;number] [[@parameter_name=](value | @variable_name) |][,...n]

If the call stored procedure is not the only command in the package, the presence of 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 a procedure with the OUTPUT keyword.

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

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 a procedure. Assign to parameter default value, you can’t just skip it when listing. If you want to omit the parameters for which it is defined default value, it is enough to explicitly specify the parameter names when calling 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.

Example 12.1. Procedure without parameters. Develop a procedure for obtaining the names and costs of goods purchased by Ivanov.

CREATE PROC my_proc1 AS SELECT Product.Name, Product.Price*Transaction.Quantity AS Cost, Customer.Last Name FROM Customer INNER JOIN (Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode) ON Customer.CustomerCode=Transaction.CustomerCode WHERE Customer .Last name='Ivanov' Example 12.1. Procedure for obtaining the names and values of goods purchased by Ivanov.