SQL Server stored procedures. Stored procedure

purpose of work - Learn how to create and use stored procedures on the database server.

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

2. Perform all examples and tasks in the course of laboratory work.

3. Perform individual tasks by options.

Explanation of work

To master the programming stored procedures, use the database when the name database Db_books.which was created in laboratory work number 1. When performing examples and tasks, pay for compliance with the names of database, tables and other project objects.

Stored procedures There are a set of commands consisting of one or more SQL or functions operators and stored in the database in compilation form.

Types of stored procedures

System stored procedures are intended to perform various administrative actions. Almost all server administration actions are performed with their help. It can be said that system stored procedures are an interface that provides work with system tables. System stored procedures have the SP_ prefix, are stored in the system database and can be caused 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 performed.

Temporary stored procedures exist only a while, after which they are automatically destroyed by the server. They are divided into losal and global. Local temporary stored procedures can only be caused from that compound in which it is created. When creating such a procedure, it needs to give a name starting from one symbol #. Like all temporary objects, the stored procedures of this type are automatically deleted when the user is disconnected, restart or stop the server. Global temporary stored procedures are 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.

Creating, changing stored procedures

The creation of the stored procedure involves the decision of the following rates: planning access rights. When creating a stored procedure, it should be borne in mind that it will have the same access rights to the database objects, which created its user; Determining the parameters of store procedures, stored procedures may have input and output parameters; Development of code stored procedure. The procedure code may contain a sequence of any SQL commands, including calling other stored procedures.

The syntax of the operator of creating a new or change of the available stored procedure in MS SQL Server designations:

(CREATE | ALTER) PROC [EDURE] name_name [; number] [(@ parameter_name type_data) [varying] [\u003d default] [Output]] [, ... n] [with (Recompile | Encryption | recompile, encryption)] [For Replication] AS SQL_ Operator [... 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. Thus, to place the created stored procedure in a specific database, you must run the Create Procedure command in the context of this database. When contacting the body of the stored procedure to objects of the same database, you 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.

To transfer the input and output data in the created stored procedure, the parameter names must begin with the @ symbol. In 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 these parameters of the stored procedure, any SQL data types are suitable, including user-defined. However, the CURSOR data type can only be used as the output parameter of the stored procedure, i.e. With the keyword OUTPUT.

The presence of the OUTPUT keyword means that the corresponding parameter is designed to return data from the stored procedure. However, this does not mean that the parameter is not suitable for the transmission of values \u200b\u200bin the stored procedure. Specifying the OUTPUT keyword instructs the server when exiting the stored procedure to 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 the output parameter will be the resulting set.

The Default keyword is a value that will accept the corresponding 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 a stored procedure plan for each call.

The For Replication parameter is in demand when replicating data and the inclusion of the created stored procedure as an article in publication. Encryption keyword instructs the server to encrypt the code of the stored procedure, which can provide protection against the use of copyright algorithms that implement the work of the stored procedure. The key word AS is located at the beginning of the body of the stored procedure. In the body of the procedure, almost all SQL commands can be applied, declared transactions, block the lock and call other stored procedures. The output of the stored procedure can be done using the RETURN command.

Removal of stored procedure

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

Performing stored procedures

To perform the stored procedure, the command is used: [[EXEC [UTE] name_name [; number] [[@ parameter_name \u003d] (value | @_name_name) [Output] | [DEFAULT]] [, ... n]

If the call to the 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 solved only for the parameters that have been announced when creating a procedure with a key word Output.

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

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 the default value to the parameter by simply by passing it when listed, it is impossible. If the parameters are required for which the default value is defined, a sufficiently explicit parameter name indication when calling the 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 the names of the parameters with values \u200b\u200bor only the values \u200b\u200bwithout the parameter name are indicated. Their combination is not allowed.

Use Return in the stored procedure

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

CREATE PROCEDURE COUNT_BOOKS AS SELECT COUNT (Code_Book) from books go

Exercise 1.

Exec Count_Books.

Check out the result.

An example of creating a procedure with input parameter:

CREATE PROCEDURE COUNT_BOOKS_PAGES @COUT_PAGES AS INT AS SELECT COUNT (CODE_BOOK) from BOOKS WHERE PAGES\u003e \u003d @COUNT_PAGES GO

Task 2.. Create this procedure in the STORED PROCEDURES section of DB_BOOKS data through the SQL Server Management Studio utility. Run it using the command

Exec Count_Books_pages 100.

Check out the result.

An example of creating a procedure with input parameters:

CREATE PROCEDURE COUNT_BOOKS_TITLE @COUNT_PAGES AS INT, @TITLE AS CHAR (10) AS SELECT COUNT (CODE_BOOK) from BOOKS WHERE PAGES\u003e \u003d @Count_Pages and Title_Book Like @title go

Task 3. Create this procedure in the STORED PROCEDURES section of DB_BOOKS data through the SQL Server Management Studio utility. Run it using the command

Exec Count_Books_Title 100, "P%"

Check out the result.

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

CREATE PROCEDURE COUNT_BOOKS_ITOGO @COUNT_PAGES INT, @TITLE CHAR (10), @itogo int Output as select @itogo \u003d count (code_book) from books Where pages\u003e \u003d @count_pages and title_book Like @title go

Task 4. Create this procedure in the STORED PROCEDURES section of DB_BOOKS data through the SQL Server Management Studio utility. Run using command set:

SQL\u003e Declare @Q as int exec count_books_itogo 100, "p%", @Q Output Select @Q

Check out the result.

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

CREATE PROCEDURE CHECKNAME @PARAM INT AS IF (Select Name_Author from Authors Where code_author \u003d @Param) \u003d "Pushkin A.S." RETURN 1 ELSE RETURN 2

Task 5. Create this procedure in the STORED PROCEDURES section of DB_BOOKS data through the SQL Server Management Studio utility. Run it using commands:

Declare @return_status int exec @return_status \u003d checkname 1 select "Return Status" \u003d @return_status

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

CREATE PROC UPDATE_PROC AS UPDATE PURCHASES SET CODE_PURCHASE \u003d CODE_PURCHASE * 2

Task 6. Create this procedure in the STORED PROCEDURES section of DB_BOOKS data through the SQL Server Management Studio utility. Run it using the command

Exec Update_Proc

An example of a procedure with an input parameter for all information on a specific authors:

CREATE PROC SELECT_AUTHOR @K CHAR (30) AS SELECT * FROM AUTHORS WHERE NAME_AUTHOR \u003d @K

Task 7.

Exec Select_Author "Pushkin A.S." or SELECT_AUTHOR @ K \u003d "Pushkin A.S." or exec select_author @ k \u003d "Pushkin A.S."

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

Create Proc Update_Proc @p int \u003d 2 AS Update Purchases set code_purchase \u003d code_purchase * @p

The procedure does not return any data.

Task 8. Create this procedure in the STORED PROCEDURES section of DB_BOOKS data through the SQL Server Management Studio utility. Run it using commands:

Exec Update_Proc 4 or Exec Update_Proc @p \u003d 4 or Exec Update_Proc - The default value is used.

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

CREATE PROC COUNT_PURCHASES @ D1 SMALLDATETIME, @ D2 SmallDateTime, @c int Output as select @ c \u003d count (code_purchase) from Purchases Where Date_order Between @ d1 and @ d2 set @c \u003d isnull (@c, 0)

Task 9. Create this procedure in the STORED PROCEDURES section of DB_BOOKS data through the SQL Server Management Studio utility. Run it using commands:

Declare @ C2 int exec count_purchases '01 - Jun- 2006 ', '01 - Jul- 2006', @ C2 Output SELECT @ C2

Options of K. laboratory work №4

General. In the SQL Server Management Studio utility to create new page For code ("Create a query" button). Programmatically make an active DB_Books database using the USE operator. Create stored procedures using Create Procedure operators, and independently determine the names of the procedures. Each procedure will perform by one SQL query that were performed in the second laboratory work. Moreover, the SQL query code needs to be changed so that they can be transmitted to the field values \u200b\u200bfor which the search is carried out.

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

/ * Choose from the reference book providers (Deliveries table) names, phones and INN (Name_Company, Phone and Inn fields), whose company name (Name_Company field) "OAO World".

SELECT NAME_COMPANY, PHONE, INN FROM DELIVERIES WHERE NAME_COMPANY \u003d "OAO World"

* / - This work procedure will be created:

CREATE PROC SELECT_NAME_COMPANY @COMP CHAR (30) AS SELECT NAME_COMPANY, PHONE, INN FROM DELIVERIES WHERE_COMPANY \u003d @COMP

-The order of the procedure is used by the command:

Exec select_name_company "OJSC World"

Task list

In the SQL Server Management Studio utility, create a new program. Programmatically make an active individual database created in laboratory work number 1, using the USE operator. Create stored procedures using Create Procedure operators, and independently determine the names of the procedures. Each procedure will perform by one SQL query that are presented in the form of separate tasks by options.

Option 1

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

2. To withdraw a list of children who gave gifts at the specified period.

3. To withdraw a list of parents who have minors children.

4. To withdraw information about gifts with a cost of more than the specified number sorted by date.

Option 2.

1. Display the list of instruments with the specified type.

2. Remove the number of repaired devices and the total cost of repairs at the specified wizard.

3. To bring the list of instrument owners and the number of their appeals, sorted by the number of appeals descending.

4. Displays information about masters with a discharge more than the specified number or with a date of reception less than the specified date.

Option 3.

2. To display a list of sales codes for which the colors sold are more than the specified number.

3. Remove the sale, amount, seller and flower at the specified sale code.

4. Display a list of colors and grade for flowers with a height of the specified number or blooming.

Option 4.

1. To withdraw a list of drugs with the specified indication for use.

2. To display a list of deliveries, which sold more than the specified number of the same drug.

3. To withdraw the delivery date, the amount, the title of the head from the supplier and the name of the medication on the receipt code more than the specified number.

Option 5.

2. Display a list of written off equipment for the indicated reason.

3. To withdraw the date of receipt, the name of the equipment, the name of the responsible and the date of write-off for the equipment written off at the specified period.

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

Option 6.

1. List of dishes with weight more than specified number.

2. To withdraw a list of products, in the title of which the specified fragment of the word is found.

3. Remove the volume of the product, the name of the dish, the name of the product with a dish code from the specified initial value by a specific end value.

4. Display the process of cooking and the name of the dish with the amount of carbohydrates is greater than a certain value or the amount of calories is larger than the specified value.

Option 7.

1. To withdraw a list of employees with this post.

3. To withdraw the date of registration, the type of document, the name of the registrar and the name of the organization for documents registered in the specified period.

4. Displays a list of registered documents with a specific type of document or the date of registration is larger than the specified value.

Option 8.

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

3. To withdraw the date of registration, the reason for dismissal, the name of the employee for documents registered in the specified period.

Option 9.

1. To withdraw a list of employees who brave the vacation of the specified type.

2. Wire a list of documents on the registration date at the specified period.

3. To withdraw the date of registration, the type of vacation, the FIO of the employee for documents registered in the specified period.

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

Option 10.

1. To withdraw a list of employees with this post.

2. To withdraw a list of documents in which the specified fragment of the word is found.

3. To withdraw the date of registration, the type of document, the FIO of the sender and the name of the organization for documents registered in the specified period.

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

Option 11.

1. To withdraw a list of employees appointed to the specified position.

2. Wire a list of documents on the registration date at the specified period.

3. To withdraw the registration date, position, FULL NAME for documents registered at the specified period.

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

Option 12.

3. To withdraw a list of persons whoighing equipment to hire and the number of their appeals, sorted by the number of appeals descending.

Option 13.

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

3. Display the amount of written off equipment grouped by equipment.

4. Display information about employees with the date of employment more than a specific date.

Option 14.

1. Display the list of flowers with the specified sheet type.

2. Wire a list of income codes for which colors sold are more than a certain value.

3. Remove the date of receipt, the amount, the names of the supplier and colors on a specific code of the supplier.

4. List of colors and varieties for flowers with a height of more than a certain number or blooming.

Option 15.

1. To withdraw a list of clients who have come to the numbers during the specified period.

2. To withdraw a total amount of payments for each client.

3. Remove the date of arrival, the type of room, the names of customers registered at the specified period.

4. Wire a list of registered customers in a certain type.

Option 16.

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

2. Post a list of equipment that has taken a certain client to rent.

3. To withdraw a list of persons whoigh the equipment to hire and the number of their appeals sorted by the number of appeals descending.

4. Disport information sorted by addresses.

Option 17.

1. To display a list of values \u200b\u200bwith procurement cost more than a certain value or a warranty period more than specified number.

2. To bring a list of locations of material values, in the title of which the specified word is found.

3. Remove the value of the value of values \u200b\u200bwith the code in the specified range.

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

Option 18.

1. List repair workperformed by a specific master.

2. To withdraw a list of stages of work included in the work, in the title of which the specified word is found.

3. To derive the amount of the cost of the steps of repair work for work with the code in the specified range.

4. Wire a list of masters from the date of employment in the specified range.

Option 19.

1. To withdraw a list of drugs with a certain indication.

2. Wire a list of checks of checks for which more than a certain number of drugs sold.

3. To withdraw the date of sale, the amount, FULL NAME, and the cure for a check with the specified number.

4. Display a list of drugs and units of measurement for drugs with a package in packaging more than the specified number or code of the medication is less than a certain value.

Option 20.

1. To withdraw a list of employees with this post.

2. To withdraw a list of documents in which the specified fragment of the word is found.

3. To withdraw the date of registration, the type of document, FULL NAME, and the fact of execution for documents registered in the specified period.

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

22 Replies

In my experience in writing mainly WinForms Client / Server applications are simple conclusions to which I came:

Use stored procedures:

• For any difficult work with data. If you are going to do something really requires a cursor table or temp, it is usually the most fast way Do it in SQL Server.
• If you need to block data access. If you do not provide access to the table to users (or role or something else), you can be sure that the only way Interaction with data - through the joint venture you create.

Use special requests:

• For CRUD when you do not need to restrict data access (or do it differently).
• For simple search. Creating SP for a variety of search criteria is pain and maintenance difficult. If you can create a fairly fast search queryUse it.

In most of my applications, I used both SP and AD-HOC SQL, although I believe that I use SP less and less because they are ultimately the code like C #, it is only more difficult to control, test and maintain. I would recommend using AD-HOC SQL if you cannot find a specific reason.

Stored procedures are a contract for softwarewhich encapsulates actions taken against the database. Code in procedures and even the database diagram itself can be changed without affecting compiled, detailed code, so the inputs and outputs of the procedure remain unchanged.

Embedding requests in your application, you closely link yourself with your data model.

For the same reason, it is also not good practice to simply create stored procedures that are CRUD requests for each table in your database, as it is still closely connected. Instead, the procedures should be cumbersome, coarse-grained.

I think this is the main conflict between people who must support the database and people who develop user interfaces.

As a person with data, I would not consider working with the database to which they are addressed through ADHOC requests, because they are difficult to configure or manage them. How can I find out what will affect the change in the scheme? In addition, I do not think that users should provide direct access to the database tables for security reasons (and I mean not only SQL injection attacks, but also because it is a basic internal control that does not allow direct rights And requires all users. Use only ProcSs intended for the application to prevent possible fraud. Any financial system that allows you to directly insert, update or delete rights to tables, has a huge risk for fraud. It is bad.).

Databases are not object-oriented, and the code that seems good from an object-oriented point of view may be extremely poor from the database point of view.

Our developers inform us that they are pleased that all our access to databases are carried out via ProcS, because it significantly speeds up the error-dependent error, and then simply launches the PROC in the working environment, and does not create a new branch of the code and recompile and Reboot to production. We demand that all our processes be in subversive activities, so the source control is not a problem at all. If it is not in Subversion, it will periodically remove DBAS, so there is no resistance to the use of Source Control.

Stored procedures are definitely suitable ... they are compiled, have a fulfillment plan before starting work, and you can manage the rights to them.

I do not understand this problem with the source code on the stored procedure. You can definitely control them, if only you are a little disciplined.

Always start with a file.sql, which is the source of the stored procedure. Place it in version control after you wrote your code. Next time you want to edit your stored procedure, you will get it out of your source element Control than your database. If you follow this, you will have the same good source of management, like your code.

I would like to quote Tom Kyte from Oracle here ... Here is its rule about where to write the code ... Although a little unrelated, but I know well, I think.

Our application has a code layer that provides the contents of the query (and sometimes the challenge of the stored procedure). This allows us to:

• easy to get all requests when managing versions
• to make all changes for each query for different database servers
• eliminates the repetition of the same query code through our code

Access control is implemented on the middle layer, and not in the database, so we do not need stored procedures. This is some kind of the average road between special requests and stored procedures.

There are convincing arguments for both stored procedures that are in the central repository, but (potentially) is difficult to postpone, and special requests are easier to debug, because they are with your code, but they can also be harder to find in the code.

The argument that stored procedures are more efficient, no longer contains water. Link text

Google execution for the stored procedure VS Dynamic Query will show decent arguments anyway and probably better for you to take your own solution ...

Some things need to think about: who needs stored procedures, Anyways?

It is clear that this is a matter of your own needs and preferences, but it is very important to think about that when using special requests in a public-oriented environment, there is safety. Always make them parametrization and follow typical vulnerabilities such as SQL injections.

The saved procedures are great because they can be changed without recompiling. I will try to use them as often as possible.

I use AD-HOC for queries that are dynamically generated based on user input.

Procs for the reasons mentioned by others, as well as easier to configure Proc using a profiler or proc parts. So you do not need to tell someone about running your application to find out what is sent to the SQL server

If you are using AD-HOC requests, make sure they are parametrized

Parameted SQL or Sproc ... It does not matter from the performance point of view ... You can request the optimization of one of them.

For me, the last remaining advantage of the Sproc is that I can exclude a lot of rights to manage SQL rights, only giving your rights to the login to run SPROCS ... If you are using Parametized SQL, the login associated with your connection string has much more Rights (recording any type of selection operator on one of the tables to which they have access, for example).

I still prefer a parameterized SQL, although ...

The Sproc Performance Argument is controversial - 3 top RDBMs use query plan caching and for a while. He was documented ... or another 1995?

However, the embedding of SQL in your application is also a terrible design - the code service seems to be the missing concept for many.

If the application can start from zero using ORM (applications with a green field are far from several!), It is an excellent choice because your class model manages your database model and saves time.

If the ORM structure is not available, we used a hybrid approach to creating XML file. SQL resources to search for SQL rows as needed (they are then cached resource infrastructure). If SQL needs any minor manipulations performed in the code, if a large manipulation of the SQL string is required, we rethink this approach.

This hybrid approach facilitates the management of developers (perhaps we are a minority, since my team is quite bright to read the query plan), and deployment is a simple check from SVN. In addition, it simplifies Switching RDBM - just replace the SQL resource file (not as simple as the ORM tool, of course, but it works with outdated systems or not supported by the database)

My experience is that 90% of requests and / or stored procedures should not be recorded at all (at least manually).

Data access must be generated somehow automatically. You can decide whether you want to statically generate procedures during compilation or dynamically during execution, but if you want to add a column to the table (object property), you must change only one file.

I prefer to store all the data access In a program code, in which the level of data access performs direct SQL queries. On the other hand, logic Officewhich I placed in the database in the form of triggers, stored procedures, user functions and something else. An example of what I consider a decent database is to generate data - suppose that our client has the name of FIRSTNAME and LASTNAME. Now the user interface requires DisplayName, which is derived from some non-trivial logic. For this generation, I create a stored procedure, which is then started by a trigger whenever the string (or other source data) is updated.

It seems that this is a slightly widespread misunderstanding that the data access level is a database, and everything that concerns data access and data occurs there. It's just incorrect, but I see many projects that flow from this idea. Perhaps this is a local fenomonon.

In the previous article of this cycle, we looked at how data can be retrieved from tables, modify their structure, create, modify and delete databases and objects, which contained. In this article we will talk in more detail about the objects characteristic of server DBMS: ideas, triggers and stored procedures.

In the first article of this cycle, published in No. 3'2000 of our magazine, we noted that most modern server DBMS support submissions, triggers and stored procedures. Representations are also supported by many desktop DBMS, such as Access, Dbase, Clipper.

It should be noted that triggers and stored procedures are typically written in programming languages, which are procedural extensions of the SQL language. These extensions contain operators to describe algorithms, for example, Do ... While, if ... Then ... ELSE, missing in the SQL language itself (if you remember, SQL is an unpromising language, and you can formulate a task, but it is impossible to describe its execution algorithms). Unlike the SQL language, subordinate to the standard, its procedural extensions are not standardized, and different DBMS use different syntactic structures to implement the same algorithmic designs, but the discussion of differences in SQL extension syntax for different DBMSs is beyond the scope of this article.

To illustrate how you can use views, triggers and stored procedures, we have chosen Microsoft SQL Server 7.0 and the NorthWind Database, which is included in the delivery of this DBMS.

Before performing examples, pay attention to the fact that the implementation and method of storing triggers and stored procedures in the DBMS you use may differ from those shown in this article. In addition, the creation of server objects should have appropriate permissions provided by the database administrator.

Note also that some ODBC drivers do not support the call of stored procedures from client applications, even if those are supported by the DBMS itself. However, in this case, stored procedures can still be caused from triggers.

Let's start with the ideas, then discuss stored procedures and finish the chapter of the trigger overview.

Representation

The presentation is a virtual table that usually contains a set of columns of one or more tables. In fact, the view does not contain data, but only a SQL query type SELECT indicating which data and from which tables should be taken when accessing this representation. From this point of view, the presentation is a storage request.

In most cases, submission are used to ensure data security. For example, some categories of users may have access to the presentation, but not to the tables whose data is formed; In addition, the SQL query may contain the user parameter (the name under which the user registered), and in this case the data available when referring to the view will depend on the username.

Below are the main characteristics of the representations:

• presentations behave like tables;
• views do not contain data;
• presentations can use data from more than one table.

To create a view, we can use the CREATE VIEW SQL proposal, for its modification - the ALTER VIEW proposal, and to remove it - the Drop View offer.

We will start with the CREATE VIEW operator to create a view for the current database.

CREATE VIEW offer

Syntax Proposal to create a view resembles SQL SELECT proposal with several additional keywords. Below is its simplified syntax:

CREATE VIEW VIEW_NAME AS SELECT_STATEMENT

The view_name argument indicates the name of the presentation. The keyword used in Microsoft SQL Server allows you to hide the CREATE VIEW source text in the Syscomments table.

The AS keyword indicates which SELECT query will actually be performed when contacting the view. Please note that this query cannot contain ORDER BY keywords, Compute or Compute by, Into and cannot refer to a temporary table.

To modify the previously created view, you should use the Alter View proposal, which briefly described in the next section.

Offer Drop View.

This offer is used to delete views from the database. Note that when you delete a table from the database, all the views that link to it are removed. Using this offer, we must specify the name of the remote view. After the view is deleted, all information about it is removed from the system tables.

Another case when the view must be removed, may arise, provided that the structure of the tables on which it is based, has changed after creating a view. In this case, you can delete the view and then create it again using the CREATE VIEW offer.

Creating and using ideas

CREATE VIEW Offer is used to create representations that allow you to extract data that meet certain requirements. The presentation is created in the current database and stored as its separate object.

The best way to create a view is to create a Select query and, checking it, add the missing part of the CREATE VIEW proposal. Let's look at the source code for presenting Products by Category in the NorthWind database (Listing 1).

The first string highlighted in bold is something that the SQL proposal is different to create a view from the usual SELECT request that performs the data from selecting data. The SELECT proposal contained in this view selects the fields of two tables - the CategoryName field from the Categories table and the ProductName field, QuantityperUnit, Unitsinstock, Discontinued from the Products table. After that, the data of the two tables are associated via the CategoryID field, and only those products that still exist in the warehouse (see the criterion after the keyword WHERE) are included in the resulting data set. The result of the appeal to this representation is shown in Fig. one .

Now let's create a view showing all the territories of the eastern region. This view is based on the following query (Listing 2).

Making sure that the SELECT proposal returns the results that we need, we add the CREATE VIEW statement and assign the Eastterr name created by the presentation.

Instead of creating a manual presentation text, you can use visual tools that are usually included in the DBMS. In fig. 2 shows how the same view can be created using the VIEW Designer tool, which is an integral part of the Enterprise Manager included in Microsoft SQL Server.

The upper part of the View Designer allows you to specify how the tables are associated and what fields will be displayed in the view. Below you can specify aliases of tables and fields, restrictions on their values, the display method. The following is the source text of the presentation and the results of its execution.

Before we finish short review representations, let's talk a little about how to get additional information about them. In Microsoft SQL Server 7.0, we can use the following system stored procedures:

• to obtain information about the view, you can use the SP_HELP system stored procedure. For example, Sp_help Eastterr will return information about the newly created submission;
• to obtain the source text of the view, you can use the SP_HELPTEXT stored procedure;
• in order to find a list of tables from which the view depends, you can use the SP_DEPENDS system stored procedure;
• you can use the SP_RENAME system stored procedure to renaming the view.

In this section, we reviewed how to use submissions to obtain data that satisfy one or another criteria. However, back to the last example. In the NorthWind database there are four regions, and for the list of territories of all regions, we need four different views. This task could be simplified if we could pass the value of the RegionID as a parameter. This can be done using the stored procedure, which we will talk about in the next section.

Stored procedures

The stored procedure is a compiled SQL-proposal set, saved in the database as a named object and executed as a single code fragment. Stored procedures can receive and return parameters. When the user creates a stored procedure, the server compiles it and places it in the shared cache, after which the compiled code can be applied by several users. When the application uses the stored procedure, it transmits it the parameters if required, and the server performs the procedure without recompiling.

Stored procedures allow you to improve application performance. First, compared to conventional SQL requests sent from the client application, they require less time to prepare for execution, since they are already compiled and saved. Secondly, network traffic in this case is also less than if the SQL query is transmitted, since fewer data is transmitted over the network. Fig. 3 illustrates the call to the stored procedure by the client application.

Stored procedures are automatically recompiled if with objects to which they affect, any changes are made; In other words, they are always relevant. As mentioned above, stored procedures can receive parameters, which allows multiple applications Use the same procedure using different input sets.

Stored procedures are commonly used to support the reference integrity of the data and the implementation of the business rules. In the latter case, additional flexibility is achieved, since if the business rules change, you can change the text of the procedure, without changing client applications.

To create, change and remove procedures, there are special SQL-offers - Create Procedure, Alter Procedure and Drop Procedure. We will look at them in the next section.

Create Procedure offer

CREATE PROCEDURE Offer is used to create a stored procedure. It has the following simplified syntax:

Create Proc Proc_Name [(@Parameter Data_Type) [\u003d Default]] [...] as sql_statements

The proc_name argument sets the name of the stored procedure, which must be unique within the current database. The @Parameter argument determines the procedure parameter. In the CREATE PROCEDURE clause, you can define one or more parameters. If there is no default value for the parameter, it must be transferred by the user (or client application) when calling the procedure. In Microsoft SQL Server 7.0, the number of parameters of the stored procedure should not exceed 1024; By default, they may have null values.

Note, however, that some universal data access mechanisms can impose additional restrictions on the number of parameters of stored procedures. For example, the BDE driver for Oracle 8 is able to work only with procedures, the number of parameters of which does not exceed 10.

The Data_Type argument indicates the data type for the parameter. Default keyword can be used to set default values \u200b\u200b- it can be a constant or null. If the default value is specified, the procedure can be called without specifying the parameter value. If the procedure uses a parameter with a Like keyword, its default value may contain group characters (%, _, and [^]).

The OUTPUT keyword shows that this is the returned parameter.

The AS keyword indicates the action that the procedure must accomplish in the form of any number of SQL proposals and suggestions on the Procedural SQL extension characteristic of this server.

The procedure created by the Create Procedure offer will be saved in the current database. In Microsoft SQL Server, procedure names are contained in the SysObjects system table, and the source text is in the Syscomments table.

To change the previously created stored procedure, you should use the Alter Procedure proposal, which briefly described in the next section.

Offer Drop Procedure

This offer is used to remove stored procedures from the database. The Drop Procedure Offer receives one argument - the name of the remote procedure.

When you delete the stored procedure, it is deleted from the SysObjects and Syscomments system tables.

Creation and use of stored procedures

In the section on ideas, we paid attention to what would be convenient if we could pass the parameter to represent the parameter containing the value of the RegionID to select one of the four regions in the NorthWind database. Let us consider the request that returns the list of territories of the region:

Select Territories.TerritoryDescription, Region.regionDescription from Territories Inner Join Region ON territories.regionid \u003d Region.regionid WHERE TERRITORIES.REGIONID \u003d 1

To select another region, we need to change the condition in the WHERE clause in the last row of the request. Therefore, if we use a variable (call it regiD), we can choose one of the four regions without changing other parts of the query.

In the Northwind database, four regions with numbers from 1 to 4. This means that the REGID variable must be an integer type. The code of the stored procedure is below:

CREATE PROCEDURE SHOWREGION @REGID INT AS SELECTERIES.TERRITORYDESCRIPTION, REGION.REGIONDESRIFTION FROM TERRITORIES INNER JOIN REGION ON TERRITORIES.REGIONID \u003d Region.regionid Where territories.regionid \u003d @regid

Note that we left almost all the Select query text neverthent (it is in italics) and only added the Create Procedure proposal for the name of the newly created stored procedure (in the first line), the parameter declaration (in the second line) and keyword AS, indicating the beginning of proposals that actually perform actions.

The result of the execution of the created procedure in SQL Server Query Analyzer for Regid \u003d 2 is shown in Fig. 3.

Obviously, we can apply stored procedures not only to implement advanced versions of submissions or "intelligent" SELECT requests. Stored procedures provide mechanisms to automate many routine tasks.

In Microsoft SQL Server 7.0, we can also use systemic stored procedures for working with conventional stored procedures:

• sp_stored_procedures - shows a list of stored procedures;
• sp_helptext - shows the source text of the stored procedure;
• sp_Depends - shows information about the dependences of stored procedures;
• sp_procoption - sets the options of stored procedures or sets them;
• sp_recompile - recompile the procedure at the time of its next call;
• sP_RENAME - changes the name of the procedure.

System stored procedures

Since we are talking about Microsoft SQL Server, it should be noted a huge number of systemic stored procedures implemented in it. The names of the system stored procedures start with SP_ or XP_ and stored in the Master database. Above, we have already described some of the frequently used system stored procedures.

Please note that triggers should not return to the user data.

In the Create Trigger clause you can use two special tables. For example, the DELETED and INSERED tables have the same structure as the table for which the trigger is defined and contain the old and new values \u200b\u200bof the user-modified records. For example, we can use the following SQL proposal to search for remote records:

SELECT * from deleted

In tab. 3 shows the contents of the DELETED and INSERED tables for all possible data changes.

To change the existing trigger, you should use the Alter Trigger proposal. We will talk about it in the next section.

First, we need to add two new fields to the table in which this information will be contained. Let's call them updatedby (the name of the manager that has updated the record last) and UpdatedWhen (the time when recording has been changed). Then create a trigger named KeepTrack. Here is his code:

CREATE TRIGGER KEEPTRACK ON CUSTOMERS FOR INSERT, UPDATE AS UPDATE CUSTOMERS SET CUSTOMERS.UPDATEDBY \u003d user_name (), customers.updatedwhen \u003d getdate () from inserted, customers where inserted.customerid \u003d customers.customerid

As can be seen from the source text of the trigger, it is performed after each Insert and UPDATE operation in the Customers table. This trigger will save the name of the manager (database user) in the Customers.updatedBy field and the date and time of change - in the Customers.updatedWhen field. This data is extracted from the time table inserted.

As you can see, this trigger allows you to monitor changes and inserting new entries in the table.

Before finishing a brief overview of the triggers, we must report where you can find information about the available triggers. The SysObjects table stores information about triggers and their types, and the Syscomments table contains their source text.

Conclusion

In this part, we looked at several types of database objects - stored procedures, views and triggers. We learned the following:

• The representation is a virtual table, usually created as a subset of columns of one or several tables. To create a view, the CREATE VIEW proposal is applied, for modification - the ALTER VIEW proposal, and to delete the Drop View offer.
• The stored procedure is a compiled SQL-proposal set, saved in the database as a named object and executed as a single code fragment. To create a stored procedure, the CREATE PROCEDURE offer is applied to change - Alter Procedure, and for deletion - Drop Procedure.
• The trigger is a special type of stored procedure, which is automatically called when the data in a specific table is added, deleted or change using Insert, Delete or Update SQL offers. Triggers are created using the Create Trigger offer. To change the trigger, the ALTER TRIGGER proposal is used, and the Drop Trigger proposal is used.

ComputerPress 12 "2000

Stored procedure - This is a special type of TRANSACT-SQL instruction package, created using sQL language and procedural extensions. The main difference between the package and the stored procedure is that the latter is saved as a database object. In other words, stored procedures are saved on the server side to improve the performance and constancy of the execution of repeated tasks.

The Database Engine component 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 A component is provided by the Database Engine component and can be used to access information in the system directory and its modifications.

When creating a stored procedure, you can define an optional list of parameters. Thus, the procedure will take appropriate arguments each time its call. Stored procedures can return a value containing certain user Information or, in case of an error, the corresponding error message.

Stored procedure is pre-compiled before it is saved as an object in the database. The pre-compiled form of procedure is stored in the database and is used for each call. This property of stored procedures provides an important benefit consisting in the elimination (almost in all cases) of repeated compilation of the procedure and obtaining relevant improvement in productivity. This property of stored procedures also has a positive effect on the amount of data involved in the exchange between the database system and applications. In particular, to call the stored procedure, several thousand bytes may require less than 50 bytes. When multiple users perform repeating tasks using stored procedures, the accumulative effect of such savings can be quite significant.

Stored procedures can also be used for the following purposes:

to create log logs about actions with database tables.

The use of stored procedures provides the ability to manage security at a level, significantly exceeding the level of security provided by using Grant and Revoke instructions, with which users are provided with different access privileges. This is possible due to the fact that the authorization on the execution of the stored procedure does not depend on the authorization on the modification of the objects contained in this stored procedure, as described in the next section.

Stored procedures that create recording and / or reading logs are provided. additional opportunity ensuring database security. Using procedures, the database administrator can track modifications made to the database by users or application programs.

Creating and executing stored procedures

Stored procedures are created by the instructions. Create Procedure.which has the following syntax:

Create Proc Proc_Name [((@ Param1) Type1 [Varying] [\u003d default1])] (, ...) As Batch | EXTERNAL NAME METHOD_NAME Syntax Agreement

The Schema_Name parameter defines the name of the scheme that is assigned by the owner of the created stored procedure. The proc_name parameter defines the name of the stored procedure. The parameter @ Param1 is a parameter of the procedure (formal argument), whose data type is determined by the Type1 parameter. The parameters of the procedure are local within the procedure, just as local variables are local within the package. Procedure Parameters are values \u200b\u200bthat are transmitted by the calling object procedure for use in it. The Default1 parameter determines the default value for the corresponding parameter of the procedure. (The default value may also be NULL.)

Option Output. Indicates that the procedure parameter is returned, and it is possible to return the value from the stored procedure that calls the procedure or system.

As mentioned earlier, the pre-compiled form of procedure is stored in the database and is used in each call. If for some reason the stored procedure is required to compile each time it is called, when declaring the procedure is used wITH RECOMPILE OPTION. Using the option With Recompile reduces one of the most important advantages of stored procedures: performance improvement thanks to one compilation. Therefore, the WITH Recompile option should be used only with frequent changes of the database objects used by the stored procedure.

Execute AS Offer Determines the security context in which the stored procedure must be executed after it is called. By setting this context, using Database Engine you can manage the choice of user accounts for checking access to objects referenced by this stored procedure.

The default use of the Create Procedure statement can only members of the predefined SysAdmin server and the predefined DB_OWNER or DB_DDDLADMIN database role. But members of these roles can assign this right to other users using the instructions. GRANT CREATE PROCEDURE.

The example below shows the creation of a simple stored procedure to work with the Project table:

Use sampledb; Go Create Procedure IncReaseBudget (@percent int \u003d 5) AS Update Project Set Budget \u003d Budget + Budget * @ percent / 100;

As mentioned earlier, for the separation of two packages is used instruction Go.. The CREATE PROCEDURE instruction cannot be combined with other TRANSACT-SQL instructions in one package. The IncreaseEBudget Strolling Procedure increases budgets for all projects to a certain number of interest, determined by the @PERCENT parameter. The procedure also defines the value of the default percent number (5), which is used if this argument is missing during the procedure.

Stored procedures can refer to non-existent tables. This property allows you to execute the procedure code debugging, without creating the corresponding tables first and without even connecting to the final server.

Unlike the main stored procedures, which are always saved in the current database, it is possible to create temporary stored procedures that are always placed in the temporal system database of TEMPDB. One of the occasions to create temporary stored procedures may be the desire to avoid the recurring execution of a specific group of instructions when connected to the database. You can create local or global temporary procedures. To do this, the name of the local procedure is set with a single symbol # (#Proc_Name), and the name of the global procedure is double (## Proc_Name).

A local temporary stored procedure can only be executed by the user and only during a connection to the database in which it was created. All users can perform a global temporary procedure, but only until the last connection is completed in which it is executed (usually this is a compound of the creator of the procedure).

The life cycle of the stored procedure consists of two stages: its creation and its execution. Each procedure is created once, and is performed multiple times. Stored procedure is carried out by execute instructions The user who is the owner of the procedure or has the EXECUTE right to access this procedure. Execute instruction has the following syntax:

[] [@return_status \u003d] (proc_name | @proc_name_var) ([[@ Parameter1 \u003d] Value | [@ Parameter1 \u003d] @Variable] | Default) .. syntax agreements

With the exception of the Return_status parameter, all the Execute instruction parameters have the same logical value as the Create Procedure instruction parameters of the same name. The RETURN_STATUS parameter defines an integer variable in which the return state of the procedure is saved. The value of the parameter can be assigned using or a constant (VALUE), or a local variable (@Variable). The order of the values \u200b\u200bof the named parameters is not important, but the values \u200b\u200bof unnamed parameters must be provided in the order in which they are defined in the Create Procedure instructions.

Default Provides default values \u200b\u200bfor the procedure parameter, which was indicated in the definition of the procedure. When the procedure expects a value for a parameter for which the default value was defined and the parameter is missing, or the default keyword is indicated, then an error occurs.

When the Execute instruction is the first package instruction, the Execute keyword can be omitted. Nevertheless, it will be more reliable to include this word in each package. Using the Execute instruction is shown in the example below:

Use sampledb; EXECUTE INCREASEBUDGET 10;

The Execute instruction in this example performs the IncreaseeBudget stored procedure, which increases the budget of all projects by 10%.

The example below shows the creation of a stored procedure for data processing in Employee and Works_on tables:

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

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

This stored procedure can be run by the following instructions:

Declare @QuantityDeleteEmployee int; Execute Deleteemployee @ Empid \u003d 18316, @ [Email Protected] Output; Print N "Removed employees:" + Convert (Nvarchar (30), @quantityDeleteEmployee);

This procedure calculates the number of projects over which the employee is busy with the @empid table number, and assigns the parameter received value. © Counter. After deleting all the rows for this tablet number from the Employee and Works_on tables, the calculated value is assigned the @quantityDeleteEmployee variable.

The parameter value is returned by the calling procedure only if the OUTPUT option is specified. In the example above, the DeleteEmployeEE procedure transmits the @Counter parameter calling procedure, therefore, the stored procedure returns the value of the system. Therefore, the @Counter parameter must be specified both in the OUTPUT option when declaring the procedure and in the Execute instructions when it is called.

Offer WITH RESULTS SETS EXECUTE INSTRUCTIONS

In SQL Server 2012, the Execute instruction is entered offer with RESULTS SETSBy which when performing certain conditions, you can change the form of the resulting set of the stored procedure.

The following two examples will help explain this proposal. The first example is an introductory example that shows how the result may look when the Offer with Results Sets is omitted:

The EmployeesIndEpt procedure is a simple procedure that displays the tablet rooms and the names of all employees working in a specific department. The department number is the procedure parameter, and you need to specify when it is called. The execution of 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. To change the headlines of the result column (as well as their data type), the SQL Server 2012 applies a new offer with Results Sets. The application of this proposal is shown in the example below:

Use sampledb; Exec EmployeesIndept "D1" with RESULT Sets ((int not , [surname] char (20) not null));

The result of the stored procedure caused by this method will be as follows:

As you can see, starting the stored procedure using the proposal of the WITH RESULT SETS in the Execute instruction allows you to change the names and the type of data of the columns of the resulting set given by this procedure. Thus, this new functionality provides greater flexibility in the execution of stored procedures and placing their results into a new table.

Changing the structure of stored procedures

Database Engine also supports instructions Alter Procedure. To modify the structure of stored procedures. Alter ProCedure manual is usually applied to change TRANSACT-SQL instructions within the procedure. All parameters of the ALTER PROCEDURE instruction have the same value as the parameters of the CREATE PROCEDURE statement. The main purpose of using this instruction is to avoid override the existing rights of the stored procedure.

Database Engine component supports cURSOR Data Type. This data type is used to declare cursors in stored procedures. Cursor - This is a programming design used to store the query results (usually a row dialing) and to provide users with the ability to display this result line.

To remove one or group of stored procedures used dROP PROCEDURE INSTRUCTIONS. Delete the stored procedure can only its owner or members of the predefined DB_OWNER and SYSADMIN roles.

Stored Procedures and Wednesday CLR

SQL Server supports the CLR (Common Language Runtime) shared environment, which allows you to develop various database objects (stored user-defined procedures, user-defined triggers statistical functions and user data types), applying C # and Visual Basic languages. The CLR environment also allows you to perform these objects using the system of the overall implementation environment.

CLR environment is allowed and prohibited by option clr_enabled. system procedure sp_configure.which runs to execute instructions Reconfigure.. In the example below, it is shown how you can use the SP_CONFIGURE system procedure to allow the use of the CLR environment:

Use sampledb; Exec SP_Configure "Clr_Enabled", 1 reconfigure

To create, compile and save the procedure using the CLR environment, you need to perform the following sequence of steps in the specified order:

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

Using instructions Create Assembly., Create a corresponding executable file.

Perform the procedure using the Execute instruction.

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

First create a desired program in any development environment, for example Visual Studio.. Compile the finished program in the object code using the C # or Visual Basic compiler. This code is stored in the dynamic library file (.dll), which serves as a source for the Create Assembly instruction, creating an intermediate executive code. Next, execute the CREATE PROCEDURE instruction to save the executable code as the database object. Finally, run the procedure for execution using the Execute instruction already familiar to us.

The example below shows the source code of the stored procedure in C # language:

Using System.Data.SQlClient; using microsoft.sqlserver.server; Public Partial Class StoredProcedures (Int Countemployees () (Int Rows; SQLConnection Connection \u003d New SqlConnection ("Connection Connection \u003d True"); Connection.open (); SQLommand CMD \u003d Connection.CreateCommand (); cmd.commandText \u003d "SELECT Count (*) AS "Number of employees" + "from Employee"; rows \u003d (int) cmd.executescalar (); connecting.close (); Return rows;))

This procedure implements a request for counting the number of rows in the Employee table. In the Using Directives at the beginning of the program, the namespaces required for its execution are specified. The use of these directives allows you to specify in source code Class names without explicitly specifying the corresponding namespaces. Next is determined by the class StoredProCedures for which it is applied. sQLPROCEDURE attributewhich informs the compiler that this class is a stored procedure. Inside the class code, the CountemployeEes () method is defined. The connection to the database system is set by a class instance. SQLConnection.. To open the connection, the Open () method () of this instance is applied. BUT createCommand () method allows you to turn to the class instance Sqlcommnd.which the desired SQL command is passed.

In the following snippet code:

Cmd.commandText \u003d "SELECT COUNT (*) AS" Number of employees "+" from employee ";

select Instructions are used to count the number of rows in the Employee table and display the result. The text of the command is specified by assigning a CMD variable CMD property returned by the CreateCommand () method. Next is called executeScalar () method Sqlcommand instance. This method returns a scalar value that is converted to an integer type of INT data and is assigned to Rows variable.

Now you can compile this code using the Visual Studio environment. I added this class to the project named ClrStoredProCedures, so Visual Studio compiles the assembly to the same name with the * .dll extension. The example below shows the next step in creating a stored procedure: Creating an executed code. Before performing the code in this example, you need to find out the location of the compiled DLL file (usually located in the Debug project folder).

Use sampledb; Go Create Assembly ClpStoredProCedures from "D: \\ Projects \\ ClrStoredProcedures \\ Bin \\ Debug \\ ClproCedures.dll" with permission_set \u003d safe

The Create Assembly instruction takes the controlled code as input and creates the appropriate object for which you can create stored CLR media procedures defined by user function and triggers. This instruction has the following syntax:

CREATE ASSEMBLY ASSEMBLY_NAME [Authorization Owner_name] from (DLL_FILE) syntax agreements

The Assembly_Name parameter indicates the assembly name. In optional suggestion Authorization, the name of the role as the owner of this assembly is indicated. In the FROM clause, the path is indicated where you are downloaded.

Offer with permission_set. It is a very important suggestion of the Create Assembly instruction and should always be indicated. It defines a set of access rights provided by the assembly code. The SAFE Rightsset is the most limiting. The assembly code that has these rights cannot access external system resources, such as files. External_Access Rights Set Allows the Assembly Code to access certain external system resources, and the UNSAFE Rightsset provides unlimited access to resources, both inside and outside the database system.

To save the information about the assembly code, the user must be able to execute the CREATE ASSEMBLY instruction. The assembly owner is the user (or role) that performs this instruction. The assembly owner can make another user using the Authorization Offer Instructions Create Schema.

The Database Engine component also supports the ALTER ASSEMBLY and DROP ASSEMBLY instructions. Instruction Alter Assembly Used to update the assembly to latest version. This instruction also adds or deletes the files associated with the corresponding assembly. Drop Instructions Assembly Removes the specified assembly and all related files from the current database.

In the example below, it is shown to create a stored procedure based on a managed code, implemented earlier:

Use sampledb; Go Create Procedure Countemployees AS External Name ClrStoredProcedures.StoredProcedures.countemployees

The Create Procedure instruction in the example is different from the same instructions in the examples before it contains eXTERNAL NAME PAME. This parameter indicates that the code is created by the CLR environment. The name in this sentence consists of three parts:

assembly_name.class_name.method_name.

assembly_name - indicates the assembly name;

class_name - indicates the name of the total class;

method_Name is an optional part, indicates the name of the method that is specified within the class.

The COUNTEMPLOYESEES procedure is shown in the example below:

Use sampledb; Declare @Count int executees @count \u003d countemployees print @count - will return 7

PRINT instruction returns the current number of rows in the Employee table.

Include in your procedures a line - 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.

Transact-sql.

Use the name of the scheme named object:

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.

Transact-sql.

SELECT * from DBO.MYTABLE - that's how to do it well - instead of select * from mytable - and so do bad - exec DBO.myProc procedure is good.

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.

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.