Use stored procedures. What is the difference between the stored procedure and representation? Deleting stored procedure on T-SQL - Drop Procedure instruction

Announcement of the procedure

Create Procedure. [({In | Out | Inout} [,…])]
[Dynamic Result Set. ]
Begin. [Atomic]

End.

Keywords
. IN (INPUT) - input parameter
. OUT (OUTPUT) - Output parameter
. INOUT - input and output, as well as field (without parameters)
. Dynamic Result SET shows that the procedure can open the specified number of cursors that will remain open after the return from the procedure.

Notes
It is not recommended to use many parameters in stored procedures (primarily large numbers and symbolic strings) due to network and stack overload. In practice, in existing Transact-SQL, PL / SQL and Informix dialects, there is a significant difference from the standard, both in the declaration and use of parameters, the declaration of variables and in the subroutine call. Microsoft recommends applying the following approximation to estimate the size of the stored procedure cache:
\u003d (maximum number of simultaneously working users) * (size itself large Plan Execution) * 1.25. Determining the size of the execution plan in the pages can be done using the command: DBCC Memusage.

Call procedure

In many existing DBMS, the call of stored procedures is performed using the operator:

Execute Procedure [(][)]

Note: Calling stored procedures can be made from the application, another stored procedure or interactive mode.

An example of announcement of the procedure

CREATE PROCEDURE PROC1 AS // declare the procedure
DECLARE CUR1 CURSOR FOR SELECT SNAME, CITY FROM SALESPEOPLE WHERE RATING\u003e 200/9 declare
Open Cur1 // Open the cursor
Fetch Next From Cur1 // Read data from the cursor
While @@ fetch_status \u003d 0
Begin.
FETCH NEXT FROM CUR1
End.
Close Cur1 // Close Cursor
DEALLOCATE CUR1
EXECUTE PROC1 // Run the procedure

Polymorphism
Two subroutines with the same name can be created in the same diagram if the parameters of these two subroutines are to mean extent from each other so that they can be distinguished. In order to distinguish between two subroutines with the same name in the same diagram, each of them is given an alternative and unique name (Specific Name). Such a name can be explicitly indicated when the subroutine is determined. When calling subroutines, if there are several identical names, the definition of the desired subroutine is carried out in several steps:
. Initially define all procedures with specified nameAnd if there are no such, then all functions with the specified name.
. For further analysis, only those subroutines are left in relation to which this user It has a privilege for execution (execute).
. For them, those whose number of parameters corresponds to the number of call arguments is selected. The specified data types in parameters and their position are checked.
. If more than one subroutine remains, then the qualification name is chosen.
In practice, the Oracle polymorphism is supported for functions declared only in the package, [Email Protected] - in different scheme, and in Sybase and MS SQL Server Overload is prohibited.

Removal and change procedures
To delete the procedure, the operator is used:

To change the procedure, the operator is used:

Alter Procedure. [([{In | Out | Inout}])]
Begin. [Atomic]

End.

Privileges for execution of procedures

GRANT EXECUTE ON. To. |Public [With GRANT Option]

System procedures
Many DBMS (including SQL Server) have a certain set of built-in system stored procedures that can be used for their own purposes.

1. Turn on the line in your procedures - SET NOCOUNT ON: With each DML expression, SQL Server carefully returns us a message containing the number of processed records. This information It may be useful to us during the debugging of the code, but after it will be completely useless. Prescribing SET NOCOUNT ON, we turn off this feature. For stored procedures containing several expressions or \\ and cycles, this action can give a significant increase in performance, because the number of traffic will be significantly reduced.

Create Proc dbo.Procname
As
SET NOCOUNT ON;
- There is a procedure code
SELECT COLUMN1 FROM DBO.TBLTABLE1
- Transclude SET NOCOUNT in source
SET NOCOUNT OFF;
Go.

2. Use the schema name with the object name: Well, I think it's clear. This operation tells the server where to look for objects and instead of randomly shakes in its bins, he will immediately know where he needs to go and what to take. With a large number of databases, tables and stored procedures, it can significantly save our time and nerves.

Select * from dbo.mytable - that way do well
- instead
Select * from mytable - so do bad
- Common procedures
Exec DBO.myProc to - up well
--Instead
Exec MyProc - Close!

3. Do not use the prefix "SP_" in the name of its stored procedures: If the name of our procedure begins with SP_, SQL Server will first look for in its main database. The fact is that this prefix is \u200b\u200bused for personal internal servers stored procedures. Therefore, its use can lead to additional costs and even incorrect result, if the procedure with the same name as you have been found in its base.

4. Use IF EXISTS (SELECT 1) instead of If Exists (SELECT *): To check for the availability of an entry in another table, we use an IF Exist expression. This expression Returns TRUE if one of the internal expression is returned at least one sentence, it does not matter "1", all speakers or table. Accucted data, in principle, are not used. Thus, to compress traffic during data transfer, it is more logical to use "1", as shown below:

IF EXISTS (Select 1 from SysObjects
Where name \u003d "MYTABLE" AND TYPE \u003d "U")

5. Use Try-Catch to catch errors: Until 2005 servers, after each request, a huge number of error checks were written in the procedure. More code always consumes more resources and more time. Since 2005 SQL Server, "Ohm appeared more correct and convenient way solutions to this problem:

Begin Try.
--the code
End Try.
Begin Catch.
- Code Calm Errors
End Catch

Conclusion
In principle, today I have everything. I repeat once again that here are only those techniques that I personally used in your practice, and I can vouch for their effectiveness.

P.S.
My first post, do not judge strictly.

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

Concept of stored procedure

Stored procedures represent groups related to each other SQL operators, the application of which makes the work of a programmer easier and flexible because to perform stored procedure It often turns out to be much simpler than the sequence of individual SQL statements. Stored procedures are a set of commands consisting of one or more SQL statements or functions and stored in the database in compilation form. Performance in the database stored procedures Instead of individual SQL statements, gives the user the following advantages:

• the necessary operators are already contained in the database;
• all of them passed the stage syntactic analysis and are in the executable format; before performing stored procedure SQL Server generates an execution plan for it, performs its optimization and compilation;
• stored procedures Support modular programming since they allow you to break large tasks for independent, smaller and convenient in the control part;
• stored procedures may cause others stored procedures and functions;
• stored procedures may be caused from applied programs of other types;
• usually, stored procedures are performed faster than the sequence of individual operators;
• stored procedures It's easier to use: they can consist of dozens and hundreds of commands, but for their launch it is enough to specify only the name of the desired 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, provides a decrease in the amount of data transmitted over the network and improves the overall performance of the system. Application stored procedures Simplifies the accompaniment of software complexes and make changes to them. Usually all limitations of integrity in the form of rules and data processing algorithms are implemented on the database server and are available to the final application in the form of a set. stored procedureswhich represent the data processing interface. To ensure the integrity of the data, as well as for security purposes, the application usually does not receive direct access to data - all work with them is carried out by calling for certain stored procedures.

A similar approach makes a very simple modification of data processing algorithms, immediately becoming available for all network users, and provides the ability to expand the system without making changes to the application itself: sufficiently change stored procedure On the database server. The developer does not need to recompile the application, create copies of it, as well as instruct users about the need to work with the new version. Users may not suggest that changes are made to the system.

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

Stored procedures in MS SQL Server MS

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

• Systemic stored procedures Designed to perform various administrative actions. Almost all server administration actions are performed with their help. Can be said that system stored procedures They are an interface that provides work with system tables, which, ultimately, is reduced to changing, adding, deleting and sampling data from system tables of both user and system databases. Systemic stored procedures There are SP_ prefix, stored in a system database and can be caused in the context of any other database.
• Custom stored procedures We implement certain actions. Stored procedures - Full database object. As a result, each stored procedure Located in a specific database, where it is performed.
• Temporary stored procedures There are only 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 caused from that compound in which are created. When creating such a procedure, it needs to give a name starting from one symbol #. Like all temporary objects, stored procedures This type is automatically deleted when the user is disconnected, restart or stop the server. Global temporary stored procedures Available for any server connections on which there is the same procedure. To determine it, it is enough to give it a name starting with ## characters. These procedures are deleted when restarting or stopping the server, as well as when closing the connection, in the context of which they were created.

Creation, change and removal of stored procedures

Creature stored procedure suggests the following tasks:

• definition of type created stored procedure: temporary or user. In addition, you can create your own systemic stored procedureWhen you assign a name with the SP_ prefix and placing it in the system database. Such a procedure will be available in the context of any local server database;
• access rights planning. While creating stored procedure It should be borne in mind that it will have the same access rights to the database objects, which has created its user;
• definition parameters of stored procedure. Like the procedures that are part of most programming languages, stored procedures may have input and output parameters;
• code Development stored procedure. The procedure code may contain a sequence of any SQL commands, including the call of others. stored procedures.

Creating a new and change available stored procedure Exercised using the following command:

<определение_процедуры>:: \u003d (CREATE | ALTER) Proc name_name [; number] [(@ parameter_name type_data) [\u003d default]] [, ... n] as sql_ [... n]

Consider the parameters of this command.

Using SP_, #, ## prefixes created by the procedure can be defined as systemic or temporary. As can be seen from the command syntax, it is not allowed to specify the name of the owner to which the procedure created, as well as the name of the database, where it must be placed. So to post the created stored procedure In a specific database, you must execute the Create Procedure command in the context of this database. When handling body stored procedure The objects of the same database can use shortened names, i.e. without specifying the name of the database. When you need to refer to objects located in other databases, specifying the database name must.

Number in the name is an identification number. stored procedure, uniquely determining it in the procedure group. For ease of management procedures, logically the same type stored procedures You can group by assigning the same names to them, but different identification numbers.

To transfer input and output in the created stored procedure Parameters can be used, whose names, like local variables, should begin with the @ symbol. One stored procedure You can set many parameters separated by commas. In the body of the procedure, local variables should not be applied, whose names are coincided with the names of the parameters of this procedure.

To determine the type of data that will have the appropriate parameter Stored Procedure, Any SQL data types are suitable, including user-defined. However, the type of CURSOR data can only be used as output parameter stored procedure. With the keyword OUTPUT.

The presence of the keyword Output means that the corresponding parameter is designed to return data from stored procedure. However, this does not mean that the parameter is not suitable for transmitting values \u200b\u200bin stored procedure. Specifying the OUTPUT keyword prescribes the server when leaving stored procedure Assign the current value of the local variable parameter, which was specified when the procedure is called as the parameter value. Note that when specifying the keyword OUTPUT, the value of the corresponding parameter when calling the procedure can only be specified using a local variable. It is not allowed to use any expressions or constants permissible for conventional parameters.

Varying keyword is used in conjunction with the Output parameter having a type of CURSOR. It determines that output parameter There will be a resulting set.

The Default keyword is the value that will take appropriate the default parameter. Thus, when the procedure is called, you can not specify the value of the corresponding parameter.

Since the server caches the query execution plan and compiled code, upon subsequent call, the procedures will be used already ready. However, in some cases, it is still necessary to recompile the code of the procedure. Specifying the keyword Recompile prescribes the system to create an execution plan stored procedure With each call.

The For Replication parameter is in demand when data replication and the inclusion of the created stored procedure As an article to publish.

Encryption keyword prescribes server encryption code stored procedurethat can provide protection from the use of copyright algorithms implementing stored procedure.

AS keyword is located at the beginning of the body stored procedure. A set of SQL commands, with which this or that or another will be implemented. In the body of the procedure, almost all SQL commands can be applied, the transactions are declared, the lock is set and the other stored procedures. Exit from stored procedure You can implement using the Return command.

Removal of stored procedure carried out by the team:

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

Performing stored procedures

For performing stored procedures The command is used:

[[EXEC [UTE] name_name [; number] [[@ parameter_name \u003d] (value | @ name_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 the procedure from the body of another procedure or trigger.

Using the OUTPUT keyword when calling the procedure is allowed only for the parameters that were announced when creating a procedure With keyword output.

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

From the syntax of the Execute command, it is clear that the parameter names can be omitted when the procedure is called. However, in this case, the user must specify the values \u200b\u200bfor the parameters in the same order, in which they were listed when creating a procedure. Assign a parameter default valueJust missing it when listed is impossible. If you want to omit the parameters for which it is determined default value, sufficiently explicit parameter names when calling stored procedure. Moreover, in such a way, you can list the parameters and their values \u200b\u200bin an arbitrary order.

Note that when calling the procedure, either parameter names with values \u200b\u200bor only values \u200b\u200bwithout a parameter name are indicated. Their combination is not allowed.

Example 12.1. Procedure without parameters. Develop a procedure for obtaining titles and cost of goods acquired by Ivanov.

CREATE PROC MY_PROC1 AS SELECT PRODUCT. Note, product. Price * Transaction. Nality as cost, client.Familia from Client Inner Join (Product Inner Join Transaction ON Product. Power Supplies \u003d Transaction) ON Client. Codclienient \u003d Transaction. Codclienate Where Client . Familia \u003d 'Ivanov' Example 12.1. The procedure for obtaining the names and value of goods acquired by Ivanov.

For appeals to the procedure You can use commands:

Exec My_Proc1 or My_Proc1

The procedure returns a data set.

Example 12.2. Procedure without parameters. Create a procedure to reduce the price of the first grade product by 10%.

For appeals to the procedure You can use commands:

Exec My_Proc2 or MY_PROC2

The procedure does not return any data.

Example 12.3. Procedure with input parameter. Create a procedure to get the names and cost of goods that the specified client acquired.

CREATE PROC MY_PROC3 @K VARCHAR (20) AS SELECT PRODUCT. NOTE, PRODUCT. COMPANY * BACKGROUND AS COST, CLIENT.Familia from Client Inner Join (Product Inner Join Transaction Commodity. Power Supplies \u003d Transaction) ON Customer. Codclienient \u003d Transaction. Codeclient WHERE CLIENT.Familia [Email Protected] Example 12.3. The procedure for obtaining titles and cost of goods that have acquired the specified client.

For appeals to the procedure You can use commands:

Exec My_Proc3 "Ivanov" or My_Proc3 @ k \u003d "Ivanov"

Example 12.4. . Create a procedure to reduce the price of a specified type of product in accordance with the specified%.

For appeals to the procedure You can use commands:

Exec My_Proc4 "Wafers", 0.05 or Exec My_Proc4 @ T \u003d "Wafers", @ p \u003d 0.05

Example 12.5. Procedure with input parameters and default values. Create a procedure to reduce the price of a specified type of product in accordance with the specified%.

Create Proc My_Proc5 @t Varchar (20) \u003d 'Candy`, @p Float \u003d 0.1 AS Update Goods SET Price \u003d Price * ( [Email Protected]) Whery type [Email Protected] Example 12.5. The procedure with input parameters and default values. Create a procedure to reduce the price of a specified type of product in accordance with the specified%.

For appeals to the procedure You can use commands:

Exec My_Proc5 "Wafers", 0.05 or Exec My_Proc5 @ T \u003d "Waffles", @ p \u003d 0.05 or Exec My_Proc5 @ P \u003d 0.05

In this case, the price of sweets decreases (the type of type is not specified when the procedure is called and is taken by default).

In the latter case, both parameters (and type, and percentages) are not specified when the procedure is called, their values \u200b\u200bare taken by default.

Example 12.6. Procedure with input and output parameters. Create a procedure to determine the total cost of goods sold for a specific month.

CREATE PROC MY_PROC6 @M INT, @S Float Output As Select @ S \u003d SUM (product. Price * transaction. Nationality) from goods inner join deal ON goods. Power supplies \u003d transaction. Group by month Transaction. Data) [Email Protected] Example 12.6. The procedure with input and output parameters. Create a procedure to determine the total cost of goods sold for a specific month.

For appeals to the procedure You can use commands:

Declare @st Float Exec My_Proc6 1, @ St Output SELECT @ST

This block of commands allows you to determine the cost of goods sold in January ( input parameter Month is indicated equal to 1).

Create a procedure to determine the total number of goods purchased by the firm in which the specified employee works.

First we develop a procedure for determining the company where the employee works.

Example 12.7. Using nested procedures. Create a procedure to determine the total number of goods purchased by the firm in which the specified employee works.

Then we create a procedure that counts the total number of goods, which is purchased by the company you are interested in.

CREATE PROC MY_PROC8 @FAM VARCHAR (20), @kol int output as declare @firm varchar (20) Exec My_Proc7 @ Fam, @ Firm Output @ Kol \u003d SUM (transaction. Nationality) from client inner join deal on client. Codclienient \u003d Transaction.codclature group by client. Firm Having client.Firm [Email Protected] Example 12.7. Creating a procedure for determining the total number of goods purchased by the firm in which a specified employee works.

The procedure call is carried out using the command:

Declare @k int exec my_proc8 'Ivanov', @ k output select @k

When should I use stored procedures and when should I use views in SQL Server?

Permissions allow you to create dynamic requestsWhere can we transmit parameters?

Which one is the fastest, and on what basis is it faster than the other?

Views or stored procedures constantly save the memory?

What does this mean if someone says that the views create a virtual table, and procedures create a table of materials?

Please let me know about more points if they are.

Solutions Collecting from Web of "What is the difference between the stored procedure and representation?"

The form represents virtual Table. You can join several tables in the presentation and use the view to submit data as if the data came from one table.

Stored procedure uses parameters to execute a function ... Whether it is an update and insert data or returning individual values \u200b\u200bor data sets.

Creating representations and stored procedures - contains some information from Microsoft about when and why use them.

Let's say I have two tables:

tBL_USER Columns: .user_ID, .user_name, .user_pw

tBL_Profile columns: .profile_id, .user_id .profile_description

Therefore, if I am in a request from these tables Alot ... Instead of making a connection in each Peice SQL, I would define the form, for example:

Create View vw_user_profile as select auser_id, B.Profile_Description from TBL_USER A LEFT JOIN TBL_Profile B ON A.USER_ID \u003d B.USER_ID GO

Therefore, in the future, if I want to request Profile_Description by the user ID ... All I need to do is

Select Profile_Description from VW_USER_PROFILE WHERE USER_ID \u003d @ID

This code can be used in the stored procedure, for example:

CREATE PROCEDURE DBO.GETDESC @ID INT AS BEGIN SELECT PROFILE_DESCRIPTION FROM VW_USER_PROFILE WHERE USER_ID \u003d @ID END GO

So later I can call

Dbo.getdesc 25.

and I will get a description for the user ID 25, where 25 is your parameter.

Obviously, much more, but it is just the basic idea.

First you need to understand that both are different things. Stored procedures are best used for Insert-Update-Delete statements. And submissions are used for SELECT statements. And you must use both.

In the views you cannot change the data.

Views: This is a virtual table consisting of one or more rows and columns from different actual database tables. This is a row pattern and columns of several tables. You cannot transmit any parameters here.

Stored procedures: They are a set of pre-executed SQL applications in which you can send parameters as input data and receive output.

Presentations can be used in the stored procedure, but stored procedure cannot be used in Views ...!

The storage procedure is used when simple SQL is simply not enough. Storage procedures contain variables, cycles and calls for other stored procedures. This is a programming language, not the query language.

Presentations are static. Think about them as new tables with a specific layout, and the data in them are created "on the fly" using the request with which you created it. As in any SQL table, you can sort and filter it with WHERE, GROUP BY and ORDER BY.

It depends on what you do.

It depends on the database. Simple views simply start the query and filter the result. But such databases such as Oracle allow you to create a "materialized" representation, which is mainly a table that is automatically updated when the basic data is changed.

The materialized representation allows you to create indexes in the submission columns (especially on the calculated columns that do not exist anywhere in the database).

I do not understand what you are talking about.

The main difference is that when you request a view, this definition is inserted into your request. The procedure can also give the results of the query, but it is compiled and so quickly. Another option is indexed views.

SQL View is a virtual table based on sQL request SELECT. The presentation refers to one or more existing database tables or other views. This is an instant database snapshot, whereas the stored procedure is a group of transact-SQL operators, compiled in a single execution plan.

View - Simple demonstration of data stored in database tables, while the stored procedure is a group of operators that can be executed.

The presentation is faster because it displays the data from the tables to which the storage procedure performs SQL instructions.

Check this article: Viewing stored procedures. Exactly what you are looking for

@Patrick is right with what he said, but to answer your other questions, View will create yourself in memory, and depending on the type of JOINS, DATA and if any aggregation is done, it can be a rather hungry appearance.

Stored procedures perform all its processing or using Temp Hash Table, for example, # TMptable1, or in memory with @ TMptable1. Depending on what you want to say.

The stored procedure is similar to the function, but is called it directly name. Instead of functions that are actually used within the request itself.

Obviously, most of the time table of memory is faster if you do not extract a lot of data.

Mash is not completely right when he assumes that you cannot change the data in the presentation. So, from the point of view of Patrick

Create View VW_USER_PROFILE AS SELECT auser_id, B.Profile_Description from TBL_USER A Left Join TBL_Profile B ON A.USER_ID \u003d B.USER_ID

I can update the data ... as an example, I can do any of these ...

Update VW_USER_Profile Set Profile_Description \u003d "Manager" WHERE User_ID \u003d 4

Update TBL_Profile Set Profile_Description \u003d "Manager" WHERE User_ID \u003d 4

You can not insert into this idea, since not all fields are present in the entire table, and I assume that profile_id is the primary key and cannot be NULL. However, sometimes you can insert INSERT into the presentation ...

I created an idea for existing tableUsing ...

CREATE VIEW JUNK AS SELECT * FROM

INSERT INTO JUNK (Code, Name) Values \u200b\u200b("Glyn", "Glyn Roberts"), ("Mary", "Maryann Roberts")

DELETE FROM JUNK WHERE ID\u003e 4

And insert, and delete worked in this case

Obviously, you cannot update any fields that are aggregated or calculated, but any representation that is simply a direct representation must be updated.

If the view contains more than one table, you cannot insert or delete, but if the view is a subset of one table, you can usually.

In addition to the above comments, I would like to add a few comments on Views.

1. Presentations can be used to hide complexity. Imagine a script in which 5 people work on the project, but only one of them is too good with a database, such as complex associations. In such a scenario, it can create species that can be easily requested by other team members, as they request any single table.
2. Security can be easily implemented Views. Suppose we employee tables that contain sensitive columns such as The salary , sSN number . These columns should not be displayed for users who are not allowed to view them. In this case, we can create a presentation that will choose columns in a table that do not require authorization, such as name , age I. etc., without exposing vulnerable columns (for example, about the salary, etc., about which we mentioned earlier). Now we can delete permission for a direct query to the table. Employee. And just keep reading permission in the presentation. Thus, we can implement security using Views.

SQL stored procedures are an executable software module that can be stored in various objects. In other words, this is an object in which SQL instructions are contained. These stored procedures can be executed in the client of application programs to get good performance. In addition, such objects are often invoked from other scenarios or even from any other partition.

Introduction

Many believe that they are similar to the procedures of various (respectively, except MS SQL). Perhaps this is true. They have similar parameters, they can produce similar values. Moreover, in some cases they come into contact. For example, they are combined with DDL and DML databases, as well as with user functions (code name - UDF).

In reality, SQL stored procedures have wide spectrum Advantages that allocate them among such processes. Security, programming variability, productivity - All this attracts users working with databases, more and more. The peak of the popularity of the procedures fell for 2005-2010, when the program from Microsoft called "SQL Server Management Studio" was published. With it, it has been much easier to work with databases, more practical and more convenient. From year to year, this was gaining popularity in the programmers. Today is an absolutely familiar program that for users, "communicating" with databases, stood on a par with "Excel".

When calling a procedure, it is instantly processed by the server itself without unnecessary processes and user intervention. After that, you can exercise any removal, execution, change. For all this is replied by the DDL operator, which alone makes the most complex effects of object processing. And all this happens very quickly, and the server is actually not loaded. Such speed and performance allow you to very quickly transmit large amounts of information from the user to the server and vice versa.

To implement this technology, 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 intended to create and perform stored procedures, which allows in large database handlers to use their own algorithms. This is necessary for those who manage such information, can protect all objects from unauthorized access of third-party persons and, accordingly, creating, modifying or deleting certain data.

Productivity

These database objects can be programmed in various ways. This allows users to choose the type of method used, which will be the most appropriate, which saves strength and time. In addition, the procedure itself is processed, which avoids the huge temporary exchange costs between the server and the user. Also, the module can be reprogrammed and changed to the desired direction in an absolutely any time. It is especially worth noting the speed with which the start of the SQL stored procedure is started: this process occurs faster than others similar to it, which makes it convenient and universal.

Safety

This type of information processing is different from similar processes in that it guarantees increased safety. This is ensured due to the fact that the access of other users to the procedures can be eliminated entirely. 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 to use parameters and returned values. The latter cannot be transferred to the data in the stored procedure, however, this information (mainly on the user's request) and processed for SQL. After the stored procedure has completed its work, it sends data packets back (but, again, if desired) to the application that caused it using various methodsWith which it can be implemented as a call to the SQL stored procedure and refund, for example:

Data transmission using the OUTPUT type parameter;

Data transfer using the return operator;

Data transmission using the selection operator.

And now we'll figure it out how this process looks 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 into a programmable database node in which the creation procedure is performed by the operator. To perform the stored SQL procedures use the EXEC process that contains the name of the object itself.

When creating the procedure, its name appears first, after which one or more parameters assigned to it. Parameters may be optional. After the parameter (s), that is, the body of the procedure will be 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 local and relative to the procedures. In other words, they can be considered only inside the body of the procedure. Microsoft SQL. Server. Stored procedures in this case are 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. Please note that an excellent option in this case is the creation and execution of the procedure with the name of the scheme in the classifier.

The body of the procedure may have any kind of example, such as creating a table, insert one or more table rows, setting the type and character of the database, and so on. Nevertheless, the body of the procedure limits the execution of some operations in it. Some of the important restrictions are listed below:

The body should not create any other stored procedure;

The body should not create a false understanding of the object;

The body should not create any triggers.

2. Installing the variable in the body of the procedure

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

Sometimes you can see that several variables are installed in one line, and each variable parameter is separated by a comma. Also note that the variable has a prefix @. In the body of the procedure you can set the variable where you want. For example, the variable @ Name1 can be declared closer to the end of the body of the procedure. In order to assign the value of the announced variable, a set of personal data is used. Unlike the situation when declared more than one variable in one line, only one set of personal data is used in such a situation.

Often, users ask a question: "How to assign multiple values \u200b\u200bin one operator in the body of the procedure?" Well. The question is interesting, but it is much easier to do what you think. Answer: Using steam such as "SELECT VAR \u003d value." You can use these pairs, separating their comma.

In a wide variety of examples, people show the creation of a simple stored procedure and executing it. However, the procedure can take such parameters that the process causing it will have the values \u200b\u200bclose to it (but not always). If they match, the corresponding processes begin inside the body. For example, if you create a procedure that will take the city and the region from the caller and return data on how many authors refer to the corresponding city and region. The procedure will request the table of the authors of the database, for example, Pubs, to perform this counting of authors. To get these databases, for example, Google loads the SQL script from the SQL2005 page.

In the previous example, the procedure takes two parameters that english language Conditionally will be called @State and @city. The data type corresponds to the type defined in the application. The body of the procedure has internal variables @totalathors (total authors), and this variable is used to display their quantity. The following selection section appears, which counts everything. Finally, the calculated value is displayed in the output window using the print statement.

How to perform a stored procedure in SQL

There are two ways to perform the procedure. The first path shows passing the parameters as the list separated by the comma is executed after the procedure name. Suppose we have two values \u200b\u200b(as in the previous example). These values \u200b\u200bare collected using the parameter variables of the @State and @City procedure. In this method of transmitting parameters, the order is important. This method is called the ordinary transfer of arguments. In the second method, the parameters are already directly appointed, and in this case the order is not important. This second method is known as the transfer of named arguments.

The procedure can be somewhat deviated from typical. Stlikely, as in the previous example, but only here the parameters are shifted. That is, the parameter @City is stored first, and @state is stored next to the default value. The default parameter is usually separated separately. SQL stored procedures pass as simply parameters. In this case, provided that the UT parameter replaces the default "CA". In the second execution, only one value of the argument for the @City parameter passes, and the @State parameter takes the default "CA". Experienced programmers advise that all variables by default are closer to the end of the parameter list. Otherwise, the execution is not possible, and then you must work with the transfer of named arguments, which is longer and more difficult.

4. Stored SQL Server Procedures: Return Methods

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

Return the value of the stored procedure;

The output of the stored procedures;

The choice of one of the stored procedures.

4.1 Return of SQL Stored Procedure Values

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

Now let's see how to perform the procedure and display the value returned to it. The execution of the procedure requires the establishment of a variable and printing, which is carried out after the whole process. Please note that instead of the print statement, you can use the SELECT operator, for example, Select @retvalue, as well as OutputValue.

4.2 SQL stored procedure parameter output

Response value can be used to return one variable that we have seen in the previous example. Using the OUTPUT parameter allows the procedure to send one or more variable values \u200b\u200bfor the caller. The output parameter is indicated as if by this key word "OUTPUT" when creating a procedure. If the parameter is specified as the output parameter, the object of the procedure must assign a value to it. SQL stored procedures, examples of which can be seen below, in which case are returned with the final information.

In our example, there will be two output names: @totalathors and @totalnocontract. They are specified in the list of parameters. These variables assign values \u200b\u200bwithin the body of the procedure. When we use the output parameters, the caller can see the value set within the body of the procedure.

In addition, in the previous scenario, two variables are announced to see the values \u200b\u200bthat set the MS SQL Server stored procedures in the output parameter. Then the procedure is performed by submitting the normal value of the "CA" parameter. The following parameters They are output and, therefore, the declared variables are transmitted in the prescribed manner. Please note that when passing variables, the output keyword also set here. After the procedure is successful, the values \u200b\u200breturned by the output parameters are displayed on the messages window.

4.3 Selecting one of SQL stored procedures

This technique is used to return the set of values \u200b\u200bas a data table (RecordSet) to the causing stored procedure. In this example, the SQL stored procedure with the @authid parameters requests the "authors" table by filtering the returned entries using this @authid parameter. The SELECT statement decides that it must be returned to the calling stored procedure. When executing the stored procedure, the AuthID is transmitted back. Such a procedure here always returns only one entry or any one. But the stored procedure does not have any restrictions on the return of more than one record. Often, you can find examples in which the return of data using selected parameters with the participation of calculated variables occurs by providing several outcome values.

Finally

Stored procedure is quite serious software modulereturning or transmitting, as well as establishing the necessary variables due to the client application. Since the stored procedure is performed on the server itself, the exchange of data in huge volumes between the server and the client application (for some calculations) can be avoided. This allows you to reduce the load on the SQL server, which, of course, goes to the hand of their holders. One of the subspecies are stored T SQL procedures, but their study is needed to those who are engaged in the creation of impressive databases. There is also a large, even a huge number of nuances that can be useful when studying stored procedures, but it is necessary more for those who are planning to tightly engage in programming, including professionally.