Stored procedures in T-SQL - create, modify, delete. How to write stored procedures correctly in SQL Server ms sql stored procedures

Stored procedure types

There are several types in SQL Server stored procedures.

• Systemic stored procedures are designed to perform various administrative actions. Almost all server administration actions are performed with their help. We can say that the system stored procedures are an interface providing work with system tables, which ultimately boils down to changing, adding, deleting and retrieving data from system tables of both user and system databases. Systemic 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 carry out 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 some 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 were created. When you create such a procedure, you need to give it a name that starts with a single # character. Like all temporary objects, stored procedures of this type are automatically deleted when the user disconnects, restarts or stops the server. Global temporary stored procedures available for any server connections that have the same procedure. To define it, you just need to give it a name starting with ## symbols. These procedures are deleted when the server is restarted or stopped, or when the connection in the context of which they were created is closed.

Creating, Modifying, and Deleting Stored Procedures

Creation stored procedure involves the solution of the following tasks:

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

Creating a new one and modifying an existing one stored procedure is done with the following command:

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

Let's consider the parameters of this command.

Using the prefixes sp_, #, ##, the created procedure can be defined as system or temporary. 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 on a specific database, you must run the CREATE PROCEDURE command in the context of that database. When handling out of body stored procedure you can use shortened names for objects of the same database, that is, without specifying the database name. When you need to refer to objects located in other databases, specifying the name of the database is required.

The number in the name is the identification number stored procedure, which uniquely identifies it in a group of procedures. For the convenience of managing procedures, 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 whose names, like the names of local variables, must begin with the @ symbol. One stored procedure many parameters can be specified, separated by commas. The body of a procedure should not use local variables whose names are the same as the names of the parameters of this procedure.

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

The presence of the OUTPUT keyword means that the corresponding parameter is intended to return data from stored procedure... However, this does not mean at all 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 as the parameter value when calling the procedure. Note that when the OUTPUT keyword is specified, the value of the corresponding parameter when calling a procedure can only be set using a local variable. You are not allowed to use any expressions or constants that are valid for normal parameters.

The VARYING keyword is used in conjunction with

In that study guide you will learn how create and drop procedures in SQL Server(Transact-SQL) with syntax and examples.

Description

In SQL Server, a procedure is a stored program that you can pass parameters to. It does not return a value like a function. However, it can return a success / failure status to the procedure that called it.

Create Procedure

You can create your own stored procedures in SQL Server (Transact-SQL). Let's take a closer look.

Syntax

The syntax for Procedures in SQL Server (Transact-SQL) is:

CREATE (PROCEDURE | PROC) procedure_name
[@parameter datatype
[VARYING] [= default] [OUT | OUTPUT | READONLY]
, @parameter datatype
[VARYING] [= default] [OUT | OUTPUT | READONLY]]
[WITH (ENCRYPTION | RECOMPILE | EXECUTE AS Clause)]
[FOR REPLICATION]
AS
BEGIN
executable_section
END;

Parameters or arguments

schema_name is the name of the schema to which the stored procedure belongs.
procedure_name is the name to assign this procedure in SQL Server.
@parameter - one or more parameters are passed to the procedure.
type_schema_name is the schema that owns the data type, if applicable.
Datatype is the data type for @parameter.
VARYING - Set for cursor parameters when the result set is an output parameter.
default is the default value to assign to the @parameter.
OUT - This means @parameter is an out parameter.
OUTPUT - This means @parameter is an output parameter.
READONLY - This means @parameter cannot be overwritten by the stored procedure.
ENCRYPTION - This means the source of the stored procedure will not be saved as plain text in the SQL Server system views.
RECOMPILE - This means the query plan will not be cached for this stored procedure.
EXECUTE AS - Sets the security context for executing the stored procedure.
FOR REPLICATION - This means that the stored procedure is executed only during replication.

Example

Let's look at an example of how to create a stored procedure in SQL Server (Transact-SQL).
Below is a simple example of the procedure:

Transact-SQL

CREATE PROCEDURE FindSite @site_name VARCHAR (50) OUT AS BEGIN DECLARE @site_id INT; SET @site_id = 8; IF @site_id< 10 SET @site_name = "yandex.com"; ELSE SET @site_name = "google.com"; END;

This procedure is called FindSite. It has one parameter called @site_name, which is an output parameter that is updated based on the @site_id variable.

Then you can refer to a new stored procedure called FindSite as follows.

Stored procedure is a special type of batch of Transact-SQL statements 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 persisted on the server side to improve performance and repeatability.

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 the 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 parameter list. Thus, 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 associated error message.

The stored procedure is precompiled before being 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 an important benefit in that it eliminates (in almost all cases) re-compilation of the procedure and provides 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, a call to 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 significant.

Stored procedures can also be used for the following purposes:

to create a log of actions with database tables.

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

Stored procedures that generate logs for writes and / or reads on tables provide additional option ensuring the security of the database. Using these procedures, the database administrator can track modifications made to the database by users or applications.

Creating and Executing Stored Procedures

Stored procedures are created using the 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 as the owner of the generated 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 specified by the type1 parameter. Procedure parameters are local within a procedure, just as local variables are local within a package. Procedure parameters are values ​​that are passed by the caller to the procedure for use in it. The default1 parameter defines the default value for the corresponding procedure parameter. (The default can also be NULL.)

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

As mentioned earlier, the precompiled form of the procedure is stored in the database and used each time it is called. If, for some reason, the stored procedure needs to be compiled every time it is called, when declaring the procedure, use option WITH RECOMPILE... Using the WITH RECOMPILE option negates one of the most important advantages Stored Procedures: Improving performance with a single compilation. Therefore, the WITH RECOMPILE option should only be used when making frequent changes to the database objects used by the stored procedure.

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

By default, only members of the sysadmin fixed server role and the db_owner or db_ddladmin fixed database role can use the CREATE PROCEDURE statement. But members of these roles can assign this right to other users using the instruction 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 mentioned earlier, to separate two packets, use GO instruction... The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in the same package. The IncreaseBudget stored procedure increases budgets for all projects by a specific number of percent, specified by the @percent parameter. The procedure also defines a default percentage value (5), which is used if this argument is not present during the procedure.

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 basic stored procedures, which are always stored in the current database, it is possible to create temporary stored procedures that are always placed in the temporary system database tempdb. One reason for creating temporary stored procedures may be to avoid the repetitive execution of a specific group of statements when connecting to a database. You can create local or global temporary procedures. For this, the name of the local procedure is specified with a single # symbol (#proc_name), and the name of the global procedure is specified with a double (## proc_name).

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

The life cycle of a stored procedure consists of two stages: its creation and its execution. Each procedure is created once, and executed repeatedly. The stored procedure is executed by EXECUTE statements a user who owns a procedure or has EXECUTE permission 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

Except for the return_status parameter, all parameters of the EXECUTE statement have the same logical meaning as the parameters of the same name for the CREATE PROCEDURE statement. The return_status parameter defines an integer variable that stores the return state 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 the named parameters is not important, but the values ​​of the unnamed parameters must be supplied in the order in which they are defined in the CREATE PROCEDURE statement.

DEFAULT clause provides default values ​​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 keyword DEFAULT then an error occurs.

When the EXECUTE statement is the first statement in 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 for 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 procedure in the example illustrates the use of stored procedures as part of the referential integrity process (in in this case between the Employee and Works_on tables). A similar stored procedure can be used within a trigger definition that actually enforces referential integrity.

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

This stored procedure can be executed using the following instructions:

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

This procedure counts the number of projects on which the employee with personnel number @empId is busy, and assigns the resulting value to the parameter © counter. After deleting all rows for a given personnel number 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 above example, the DeleteEmployee procedure passes the @counter parameter to the calling procedure, so 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, the EXECUTE statement is entered WITH RESULTS SETS clause, through which, when certain conditions are met, you can change the form of the result set of the 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 parameter of the procedure and must be specified when calling it. Performing this procedure displays a table with two columns, the headers of which coincide with the names of the corresponding columns of the database table, i.e. Id and LastName. SQL Server 2012 uses the new WITH RESULTS SETS clause to change the result column headings (as well as their data type). The application of this proposal is shown in the example below:

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

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

As you can see, running the stored procedure using the WITH RESULT SETS clause in the EXECUTE statement allows you to change the names and data type of the columns in the result set returned by the procedure. Thus, this new functionality provides more 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 statement ALTER PROCEDURE to modify the structure of stored procedures. ALTER PROCEDURE is typically used to modify Transact-SQL statements within a procedure. All parameters of the ALTER PROCEDURE statement have the same meaning as the parameters of the same name for the CREATE PROCEDURE statement. The main purpose of using this statement is to avoid overriding existing stored procedure rights.

The Database Engine supports data type CURSOR... 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 to enable users to display that result line by line.

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

Stored Procedures and the CLR

SQL Server supports the Common Language Runtime (CLR), which enables the development of various database objects (stored procedures, UDFs, triggers, UDFs, and UDDs) using C # and Visual basic... The CLR also allows you to execute these objects using the common runtime system.

The CLR is allowed and denied by the option clr_enabled system procedure sp_configure, which is started for execution by the instruction RECONFIGURE... The example below shows how the CLR can be enabled using the sp_configure system procedure:

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 order:

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

Using the instruction CREATE ASSEMBLY, create the corresponding executable file.

Execute the procedure using the EXECUTE statement.

The figure below shows a flowchart of the previously outlined steps. The following is more detailed description this process.

First create the required program in some development environment like Visual studio... Compile ready-made program into object code using the C # or Visual Basic compiler. This code is saved in a dynamic link library (.dll) file, which is the source for the CREATE ASSEMBLY statement, which generates executable intermediate code. Next, run the CREATE PROCEDURE statement to save the executable 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. The using directives at the beginning of the program specify the namespaces required to execute the program. The use of these directives allows you to specify in source code class names without explicitly specifying the corresponding namespaces. Next, the StoredProcedures class is defined, for which the SqlProcedure attribute which informs the compiler that this class is a stored procedure. The CountEmployees () method is defined inside the class code. The connection to the database system is established through an instance of the class SqlConnection... To open a connection, the Open () method of that instance is used. A CreateCommand () method allows you to access an instance of the 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 is called ExecuteScalar () method a SqlCommand instance. This method returns a scalar value that is converted to the int data type and assigned to rows.

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

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 an appropriate object for which you can create CLR stored procedures, UDFs, and triggers. This statement has the following syntax:

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

Assembly_name 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 included. It defines the set of access rights granted to the assembly code. The SAFE set of rights is the most restrictive. Assembly code that has these rights cannot access external system resources such as files. The EXTERNAL_ACCESS privilege set allows assembly code to access specific external system resources, while the UNSAFE privilege set grants unrestricted access to resources, both inside and outside the database system.

The user must be able to execute the CREATE ASSEMBLY statement to preserve information about the assembly code. An assembly is owned by the user (or role) who is executing this statement. You can make the assembly owner another user 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 statement also adds or removes files associated with the corresponding assembly. DROP ASSEMBLY Statement removes the specified assembly and all associated files from the current database.

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

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

The CREATE PROCEDURE statement in the example differs from the same statement in the examples earlier in that it contains EXTERNAL NAME parameter... This parameter indicates that the code is generated by the CLR. The name in this sentence has 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, specifies the name of the method that is specified 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 - Returns 7

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

SQL stored procedures are executable software modules that can be stored as various objects. In other words, it is an object that contains SQL statements. These stored procedures can be executed in the application client to obtain good performance... In addition, such objects are often called from other scripts or even from some other section.

Introduction

Many people think that they are similar to different procedures (respectively, except for MS SQL). Perhaps this is true. They have similar parameters and can produce similar values. Moreover, in some cases they touch. For example, they can be combined with DDL and DML databases and user functions (codenamed UDF).

In reality, SQL stored procedures have a wide range of advantages that set them apart from similar processes. Security, programming variability, productivity - all this attracts users working with databases more and more. The peak of the popularity of procedures came in 2005-2010, when a program from Microsoft called "SQL Server Management Studio" was released. With its help, working with databases has become much easier, more practical and more convenient. From year to year this was gaining popularity among programmers. Today, it is an absolutely familiar program, which for users "communicating" with databases, is on a par with Excel.

When a procedure is called, it is instantly processed by the server itself without unnecessary processes and user intervention. After that, you can carry out any deletion, execution, change. All this is the responsibility of the DDL operator, which alone performs the most complex operations for processing objects. Moreover, all this happens very quickly, and the server is not actually loaded. Such speed and performance allow very fast transfer of large amounts of information from the user to the server and vice versa.

To implement this technology for working with information, there are several programming languages. These include, for example, PL / SQL from Oracle, PSQL in InterBase and Firebird systems, as well as the classic Microsoft Transact-SQL. All of them are designed for creating and executing stored procedures, which allows large database handlers to use their own algorithms. This is also necessary so that those who manage such information can protect all objects from unauthorized access by third parties and, accordingly, the creation, modification or deletion of certain data.

Productivity

These database objects can be programmed in a variety of ways. This allows users to choose the type of method used that is most appropriate, which saves time and effort. In addition, the procedure is handled by itself, which avoids the huge time-consuming exchange between the server and the user. Also, the module can be reprogrammed and changed in the desired direction at absolutely any time. It is especially worth noting the speed with which the SQL stored procedure is launched: this process is faster than others, similar to it, which makes it convenient and versatile.

Safety

This type of information processing differs from similar processes in that it guarantees increased security. This is ensured due to the fact that access of other users to procedures can be completely and completely excluded. This will allow the administrator to conduct operations with them independently, without fear of intercepting information or unauthorized access to the database.

Data transfer

The relationship between the SQL stored procedure and the client application is through the use of parameters and return values. The latter does not need to transfer data to the stored procedure, however, this information (mainly at the request of the user) is processed for SQL. After the stored procedure has completed its work, it sends data packets back (but, again, optionally) to the calling application using different methods, with the help of which both a call to a SQL stored procedure and a return can be made, for example:

Passing data using a parameter of type Output;

Transferring data using a return statement;

Passing data using a select operator.

Now let's figure out what this process looks like from the inside.

1. Creating an EXEC Stored Procedure in SQL

You can create a procedure in MS SQL (Managment Studio). After the procedure is created, it will be listed in a programmable database node, in which the creation procedure is performed by the operator. For execution, SQL stored procedures use an EXEC process that contains the name of the object itself.

When a procedure is created, its name appears first, after which one or more parameters assigned to it are produced. The parameters can be optional. After the parameter (s), that is, the body of the procedure, are written, you need to carry out some necessary operations.

The fact is that the body can have local variables located in it, and these variables are also local to procedures. In other words, they can only be viewed within the body of a Microsoft SQL Server procedure. Stored procedures are then considered local.

Thus, to create a procedure, we need the name of the procedure and at least one parameter as the body of the procedure. Note that a great option in this case is to create and execute a procedure named schema in the classifier.

The body of the procedure can be of any kind, for example, such as creating a table, inserting one or more rows of a table, establishing the type and nature of the database, and so on. However, the body of the procedure restricts the execution of certain operations in it. Some of the important limitations are listed below:

The body should not create any other stored procedure;

The body must not create a false impression of the object;

The body shouldn't create any triggers.

2. Setting a variable in the body of the procedure

You can make variables local to the body of the procedure, and then they will be located exclusively inside the body of the procedure. It is good practice to create variables at the beginning of the stored procedure body. But you can also set variables anywhere in the body of this object.

Sometimes you will notice that several variables are set on one line, and each variable parameter is separated by a comma. Also note that the variable is prefixed with @. In the body of the procedure, you can set the variable wherever you want. For example, the @ NAME1 variable can be declared towards the end of the procedure body. In order to assign a value to a declared variable, a set of personal data is used. Unlike a situation where more than one variable is declared on one line, in such a situation only one set of personal data is used.

Often users ask the question: "How to assign several values ​​in one statement in the body of a procedure?" Well. An interesting question, but it's much easier to do than you think. Answer: using pairs such as "Select Var = value". You can use these pairs by separating them with a comma.

In the most various examples people show how to create a simple stored procedure and execute it. However, a procedure can take parameters such that the calling process will have values ​​close to it (but not always). If they coincide, then the corresponding processes begin inside the body. For example, if you create a procedure that will accept the city and region from the caller and return information about how many authors belong to the corresponding city and region. The routine will query the tables of the authors of the database, for example Pubs, to do this author count. To get these databases, for example, Google downloads the SQL script from the SQL2005 page.

In the previous example, the procedure takes two parameters, which in English will be conventionally named @State and @City. The data type corresponds to the type defined in the application. The body of the procedure has internal variables @TotalAuthors (total authors), and this variable is used to display their number. Next, the query selection section appears, which calculates everything. Finally, the calculated value is displayed in the output window using a print statement.

How to execute a stored procedure in SQL

There are two ways to complete the procedure. The first path shows, by passing parameters, how a comma-separated list is executed after the procedure name. Let's say we have two values ​​(as in the previous example). These values ​​are collected using the procedure parameters @State and @City. Order is important in this way of passing parameters. This method is called ordinal passing of arguments. In the second method, the parameters are already directly assigned, in which case the order is not important. This second method is known as passing named arguments.

The procedure may deviate somewhat from the typical one. Everything is the same as in the previous example, but here the parameters are shifted. That is, @City is stored first and @State is stored next to the default. The default parameter is usually highlighted separately. SQL stored procedures are passed as just parameters. In this case, provided, the "UT" parameter replaces the default "CA". In the second execution, only one argument value passes for the @City parameter, and the @State parameter defaults to "CA". Experienced programmers advise that all default variables be near the end of the parameter list. Otherwise, execution is not possible, and then you have to work with passing named arguments, which is longer and more complicated.

4. SQL Server Stored Procedures: Return Methods

There are three important ways to send data in a called stored procedure. They are listed below:

Returning the value of a stored procedure;

Stored procedure parameter output;

Selecting one of the stored procedures.

4.1 Returning SQL Stored Procedure Values

In this technique, the procedure assigns a value to a local variable and returns it. The procedure can also return a constant value directly. In the following example, we have created a procedure that returns the total number of authors. If you compare this procedure with previous ones, you can see that the print value is reversed.

Now let's see how to execute the procedure and print the return value. The execution of the procedure requires setting a variable and printing, which is carried out after the whole process. Note that instead of a print statement, you can use a Select statement such as Select @RetValue and OutputValue.

4.2 Parameter Output for SQL Stored Procedures

The return value can be used to return a single variable, as we saw in the previous example. Using the Output parameter allows a procedure to send one or more variable values ​​to the caller. The output parameter is denoted just the same by this keyword "Output" when creating a procedure. If a parameter is specified as an output parameter, then the procedure object must assign a value to it. SQL stored procedures, examples of which can be seen below, are then returned with summary information.

Our example will have two output names: @TotalAuthors and @TotalNoContract. They are indicated in the parameter list. These variables are assigned values ​​within the body of the procedure. When we use out parameters, caller can see the value set inside the body of the procedure.

Also, in the previous script, two variables are declared to see the values ​​that set the MS SQL Server stored procedures in the out parameter. Then the procedure is performed by supplying the normal value of the "CA" parameter. The following parameters are output and therefore the declared variables are passed in the specified order. Note that when passing through the variables, the output keyword is also set here. After the procedure is successful, the values ​​returned by the output parameters are displayed in a message box.

4.3 Selecting one of the SQL stored procedures

This technique is used to return a set of values ​​as a data table (RecordSet) to the calling stored procedure. In this SQL example, a stored procedure with @AuthID parameters queries the Authors table by filtering the returned records with this @AuthId parameter. The Select statement decides what should be returned to the caller of the stored procedure. When the stored procedure runs, AuthId is passed back. Such a procedure here always returns only one record, or none at all. But the stored procedure does not have any restrictions on returning more than one record. It is not uncommon to find examples in which the return of data using selected parameters with the participation of calculated variables occurs by providing multiple totals.

Finally

A stored procedure is a pretty serious piece of software that returns or passes, and sets the required variables through the client application. Because the stored procedure executes on the server itself, huge amounts of data exchange between the server and the client application (for some computations) can be avoided. This allows to reduce the load on the SQL servers, which, of course, goes into the hands of their holders. T SQL stored procedures are one of the subspecies, but their study is necessary for those who are engaged in creating impressive databases. There is also a large, even a huge number of nuances that can be useful when studying stored procedures, but this is needed more for those who are planning to get involved in programming closely, including professionally.

Last updated: 08/14/2017

Often, a data operation is a set of instructions that must be executed in a specific sequence. For example, when adding a purchase item, you need to enter data into the table of orders. However, before that you need to check if the purchased item is in stock. In this case, you may need to check a number of additional conditions. That is, in fact, the process of buying a product encompasses several actions that must be performed in a specific sequence. And in this case, it would be more optimal to encapsulate all these actions into one object - stored procedure(stored procedure).

That is, in essence, stored procedures are a set of instructions that are executed as a whole. Thus, stored procedures make it possible to simplify complex operations and put them into a single object. The process of purchasing goods will change; accordingly, it will be enough to change the procedure code. That is, the procedure also makes it easier to manage the code.

Stored procedures also allow you to restrict access to data in tables and thereby reduce the likelihood of deliberate or unconscious unwanted actions in relation to this data.

And another important aspect is performance. Stored procedures generally run faster than regular SQL statements. This is because the procedure code is compiled once the first time it is run, and then saved in the compiled form.

The CREATE PROCEDURE or CREATE PROC command is used to create a stored procedure.

So the stored procedure has three key features: code simplification, safety and performance.

For example, let's say there is a table in the database that stores product data:

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

Let's create a stored procedure to retrieve data from this table:

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

Because the CREATE PROCEDURE command must be called in a separate package, the GO command is used after the USE command that installs the current database to define a new package.

The procedure name must be followed by the AS keyword.

To separate the procedure body from the rest of the script, the procedure code is often placed in a BEGIN ... END block:

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

After adding the procedure, we can see it in the database node in SQL Server Management Studio in the subnode Programmability -> Stored Procedures:

And we will be able to control the procedure through the visual interface as well.

Procedure execution

The EXEC or EXECUTE command is called to execute the stored procedure:

EXEC ProductSummary

Removing a procedure

To remove a procedure, use the DROP PROCEDURE command:

DROP PROCEDURE ProductSummary