Stored procedures in ms sql server environment. Stored Procedures in T-SQL - Create, Modify, Delete

1. Include in your procedures the line - SET NOCOUNT ON: With each DML statement, SQL server carefully returns us a message containing the number of processed records. This information may be useful to us while debugging the code, but after that it will be completely useless. By writing SET NOCOUNT ON, we disable this feature. For stored procedures containing multiple expressions or\and loops, this action can give a significant performance boost, because the amount of traffic will be significantly reduced.

CREATE PROC dbo.ProcName
AS
SET NOCOUNT ON;
--Procedure code here
SELECT column1 FROM dbo.TblTable1
--Switch SET NOCOUNT to initial state
SET NOCOUNT OFF;
GO

2. Use schema name with object name: Well, I think it's clear. This operation tells the server where to look for objects and instead of randomly rummaging through its bins, it will immediately know where it 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 --This is a good way to do it
-- Instead of
SELECT * FROM MyTable --This is a bad thing to do
--Procedure call
EXEC dbo.MyProc -- Good again
--Instead of
EXEC MyProc --Bad!

3. Do not use the "sp_" prefix in the name of your stored procedures: If our procedure name starts with "sp_", SQL Server will look in its master database first. The fact is that this prefix is ​​used for personal internal stored procedures of the server. Therefore, its use may lead to additional costs and even incorrect results if a procedure with the same name as yours is found in its database.

4. Use IF EXISTS (SELECT 1) instead of IF EXISTS (SELECT *): To check if a record exists in another table, we use the IF EXISTS statement. This expression returns true if at least one value is returned from the inner expression, it doesn't matter "1", all columns or a table. The returned data, in principle, is not used in any way. Thus, to compress traffic during data transmission, it is more logical to use "1", as shown below:

IF EXISTS (SELECT 1 FROM sysobjects
WHERE name = "MyTable" AND type = "U")

5. Use TRY-Catch to catch errors: Prior to 2005 server, after each request, a huge number of error checks were written in the procedure. More code always consumes more resources and more time. Since SQL Server 2005, a more correct and convenient way to solve this problem has appeared:

BEGIN TRY
--code
END TRY
BEGIN CATCH
-- error catch code
END CATCH

Conclusion
Basically, that's all I have for today. I repeat once again that here are only those techniques that I personally used in my practice, and I can vouch for their effectiveness.

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

In Microsoft SQL Server to implement and automate their own algorithms ( calculations) you can use stored procedures, so today we'll talk about how they are created, modified and deleted.

But first, a little theory so that you understand what stored procedures are and what they are for in T-SQL.

Note! For novice programmers, I recommend the following useful materials on the topic of T-SQL:

• For a more detailed study of the T-SQL language, I also recommend reading the book - The Way of the T-SQL Programmer. Transact-SQL Tutorial.

What are stored procedures in T-SQL?

Stored procedures- These are database objects in which the algorithm is embedded in the form of a set of SQL instructions. In other words, we can say that stored procedures are programs within a database. Stored procedures are used to store reusable code on the server, for example, you wrote an algorithm, a sequential calculation or a multi-step SQL statement, and in order not to execute all the instructions included in this algorithm each time, you can arrange it as a stored procedure. In this case, when you create an SQL procedure, the server compiles the code, and then, each time you run this procedure, the SQL server will no longer compile it again.

In order to run a stored procedure in SQL Server, it is necessary to write the EXECUTE command before its name, it is also possible to write this EXEC command in shorthand. Call a stored procedure in a SELECT statement, for example, as a function will no longer work, i.e. procedures are run separately.

In stored procedures, unlike functions, it is already possible to perform data modification operations such as: UNSERT, UPDATE, DELETE. Also, in procedures, you can use almost any type of SQL statement, for example, CREATE TABLE to create tables or EXECUTE, i.e. calling other procedures. The exception is several types of instructions such as: creating or changing functions, views, triggers, creating schemas and a few other similar instructions, for example, it is also impossible to switch the database connection context (USE) in a stored procedure.

A stored procedure may have input parameters and output parameters, it may return tabular data, it may not return anything, but only execute the instructions contained in it.

Stored procedures are very useful, they help us automate or simplify many operations, for example, you constantly need to generate various complex analytical reports using pivot tables, i.e. PIVOT operator. To simplify the formation of queries with this operator ( as you know, PIVOT syntax is rather complicated), you can write a procedure that will dynamically generate summary reports for you, for example, in the material “Dynamic PIVOT in T-SQL”, an example of implementing this feature in the form of a stored procedure is presented.

Examples of working with stored procedures in Microsoft SQL Server

Initial data for examples

All examples below will be executed in Microsoft SQL Server 2016 Express. In order to demonstrate how stored procedures work with real data, we need this data, let's create it. For example, let's create a test table and add some records to it, let's say it will be a table containing a list of products with their price.

Table creation statement CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Add data statement INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1 , "Mouse", 100), (1, "Keyboard", 200), (2, "Phone", 400) GO -- SELECT * FROM TestTable query

There is data, now let's move on to creating stored procedures.

Creating a stored procedure in T-SQL - CREATE PROCEDURE statement

Stored procedures are created using the statement CREATE PROCEDURE, after this instruction you must write the name of your procedure, then, if necessary, define the input and output parameters in brackets. After that, you write the keyword AS and open a block of instructions with the keyword BEGIN, close this block with the word END. Inside this block, you write all the instructions that implement your algorithm or some kind of sequential calculation, in other words, you program in T-SQL.

For example, let's write a stored procedure that will add a new record, i.e. new item to our test chart. To do this, we will define three input parameters: @CategoryId - product category identifier, @ProductName - product name and @Price - product price, this parameter will be optional for us, i.e. it can not be passed to the procedure ( for example, we don't know the price yet), for this we will set the default value in its definition. These parameters are in the body of the procedure, i.e. in the BEGIN…END block can be used in the same way as ordinary variables ( as you know, variables are denoted by the @ sign). If you need to specify output parameters, then after the parameter name, specify the OUTPUT keyword ( or abbreviated OUT).

In the BEGIN…END block, we will write an instruction for adding data, and also at the end of the procedure, a SELECT statement so that the stored procedure returns tabular data about the products in the specified category, taking into account the new, just added product. Also in this stored procedure, I added processing of the incoming parameter, namely, removing extra spaces at the beginning and at the end of the text string in order to avoid situations when several spaces were accidentally entered.

Here is the code for this procedure I also commented on it).

Create procedure CREATE PROCEDURE TestProcedure (--Incoming parameters @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) AS BEGIN --Instructions that implement your algorithm --Processing incoming parameters --Removing extra spaces at the beginning and in end of text string SET @ProductName = LTRIM(RTRIM(@ProductName)); --Add new entry INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Return data SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO

Running a Stored Procedure in T-SQL - EXECUTE Command

You can run a stored procedure, as I already noted, using the EXECUTE or EXEC command. Incoming parameters are passed to procedures by simply enumerating them and providing the appropriate values ​​after the procedure name ( for output parameters, you must also specify the OUTPUT command). However, the name of the parameters may not be specified, but in this case it is necessary to follow the sequence of specifying the values, i.e. specify the values ​​in the order in which the input parameters are defined ( this also applies to the output parameters).

Parameters that have default values ​​may not be specified, these are the so-called optional parameters.

Here are a few different but equivalent ways to run stored procedures, specifically our test procedure.

1. Call the procedure without specifying the price EXECUTE TestProcedure @CategoryId = 1, @ProductName = "Test product 1" --2. We call the procedure with the price specified EXEC TestProcedure @CategoryId = 1, @ProductName = "Test product 2", @Price = 300 --3. We call the procedure without specifying the name of the EXEC parameters TestProcedure 1, "Test item 3", 400

Changing a Stored Procedure to T-SQL - ALTER PROCEDURE Statement

You can make changes to the algorithm of the procedure using the instructions ALTER PROCEDURE. In other words, in order to change an already existing procedure, you just need to write ALTER PROCEDURE instead of CREATE PROCEDURE, and change everything else as necessary.

Let's say we need to make changes to our test procedure, say the @Price parameter, i.e. price, we will make it mandatory, for this we will remove the default value, and also imagine that we no longer need to obtain the resulting data set, for this we will simply remove the SELECT statement from the stored procedure.

Change the procedure ALTER PROCEDURE TestProcedure (--Incoming parameters @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) AS BEGIN --Instructions that implement your algorithm --Processing incoming parameters --Remove extra spaces at the beginning and end of the text lines SET @ProductName = LTRIM(RTRIM(@ProductName)); --Add new record INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO

Deleting a stored procedure in T-SQL - DROP PROCEDURE statement

If necessary, you can delete the stored procedure, this is done using the statement DROP PROCEDURE.

For example, let's delete the test procedure we created.

DROP PROCEDURE TestProcedure

When deleting stored procedures, it is worth remembering that if the procedure is referenced by other procedures or SQL statements, after deleting it, they will fail with an error, since the procedure they refer to no longer exists.

I have everything, I hope the material was interesting and useful to you, bye!

The concept of stored procedures is defined. 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.

The concept of a stored procedure

Stored procedures are groups of interconnected SQL statements, the use of which makes the programmer's work easier and more flexible, because to execute stored procedure is often much simpler than a sequence of individual SQL statements. Stored procedures are a set of commands that consists of one or more SQL statements or functions and is stored in the database in a compiled form. Execution on the database stored procedures Instead of individual SQL statements, it gives the user the following benefits:

• the necessary operators are already in the database;
• they all passed the stage parsing and are in executable format; before executing a stored procedure SQL Server generates an execution plan for it, optimizes it, and compiles it;
• stored procedures support modular programming, as they allow you to break large tasks into independent, smaller and easy-to-manage parts;
• stored procedures may cause others stored procedures and functions;
• stored procedures can be called from other types of application programs;
• usually, stored procedures are executed faster than a sequence of individual statements;
• stored procedures easier to use: they can consist of tens and hundreds of commands, but to run them, it is enough to specify just 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 hence the load on the network.

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

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

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

Stored procedures in MS SQL Server environment

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

Types of Stored Procedures

There are several types in SQL Server stored procedures.

• Systemic stored procedures designed to perform various administrative actions. Almost all server administration actions are performed with their help. We can say that the system stored procedures are an interface that provides work with system tables, which, ultimately, comes down to changing, adding, deleting, and retrieving data from system tables of both user and system databases. Systemic stored procedures are prefixed with sp_ , are stored in the system database, and can be called in the context of any other database.
• Custom stored procedures implement certain actions. Stored procedures- a complete database object. As a result of this, each stored procedure is located in a specific database, where it is executed.
• Temporary stored procedures exist only for a short time, after which they are automatically destroyed by the server. They are divided into local and global. Local temporary stored procedures can only be called from the connection in which they are created. When creating such a procedure, it must be given a name that starts with a single # character. Like all temporary objects, stored procedures of this type are automatically deleted when the user disconnects, restarts, or stops the server. Global temporary stored procedures available for any server connections that have the same procedure. To define it, it is enough to give it a name that starts with the characters ## . These procedures are deleted when the server is restarted or stopped, or when the connection in whose context they were created is closed.

Creating, Modifying, and Deleting Stored Procedures

Creation stored procedure involves solving the following tasks:

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

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

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

Consider the parameters of this command.

Using the prefixes sp_ ​​, # , ## , the created procedure can be defined as a system or temporary procedure. As you can see from the syntax of the command, it is not allowed to specify the name of the owner to whom the created procedure will belong, as well as the name of the database where it should be placed. Thus, in order to accommodate the created stored procedure in a particular database, you must run the CREATE PROCEDURE command in the context of that database. When handled from the body stored procedure Shortened names can be used for objects in the same database, i.e. without specifying the database name. When you want to refer to objects located in other databases, specifying the database name is required.

The number in the name is the identification number stored procedure, which uniquely defines it in a group of procedures. For the convenience of managing procedures, logically the same type stored procedures can be grouped by giving them the same name but different identification numbers.

To pass input and output data in the created stored procedure parameters can be used, whose names, like the names of local variables, must begin with the @ symbol. One stored procedure You can specify multiple options separated by commas. The body of a procedure must not use local variables whose names are the same as the names of the procedure's parameters.

To determine the data type that the corresponding stored procedure parameter, any SQL data type is fine, including user-defined ones. However, the CURSOR data type can only be used as output parameter stored procedure, i.e. with the keyword OUTPUT .

The presence of the OUTPUT keyword means that the corresponding parameter is intended to return data from stored procedure. However, this does not mean at all that the parameter is not suitable for passing values ​​to stored procedure. Specifying the OUTPUT keyword instructs the server to exit from stored procedure assign the current value of the parameter to the local variable that was specified when the procedure was called as the value of the parameter. Note that when specifying the OUTPUT keyword, the value of the corresponding parameter when calling the procedure can only be set using a local variable. Any expressions or constants allowed for normal parameters are not allowed.

The VARYING keyword is used in conjunction with the OUTPUT parameter, which is of type CURSOR . It defines that output parameter will be the resulting set.

The DEFAULT keyword is the value that the corresponding default setting. Thus, when calling a procedure, you can not explicitly specify the value of the corresponding parameter.

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

The FOR REPLICATION parameter is required when replicating data and including the created stored procedure as an article in a publication.

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

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

Deleting a stored procedure carried out by the command:

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

Executing a Stored Procedure

For executing a stored procedure the command is used:

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

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

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

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

From the syntax of the EXECUTE command, you can see that parameter names can be omitted when calling a procedure. However, in this case, the user must specify the values ​​for the parameters in the same order in which they were listed when creating a procedure. Assign to parameter default value, simply skipping it when enumerating is not possible. If it is required to omit the parameters for which the default value, it is enough to explicitly specify the parameter names when calling stored procedure. Moreover, in this way, you can list the parameters and their values ​​in any order.

Note that when a procedure is called, either parameter names with values ​​are specified, or only values ​​without a parameter name. Their combination is not allowed.

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

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

For call to procedure commands can be used:

EXEC my_proc1 or my_proc1

The procedure returns a set of data.

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

For call to procedure commands can be used:

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 prices of items purchased by a given customer.

CREATE PROC my_proc3 @k VARCHAR(20) AS SELECT Item.Name, Item.Price*Trade.Quantity AS Cost, Customer.LastName FROM Customer INNER JOIN (Item INNER JOIN Trade ON Item.ItemID=Trade.ItemID) ON Client.CustomerID =Deal.CustomerCode WHERE Customer.LastName=@k Example 12.3. A procedure for getting the names and prices of items purchased by a given customer.

For call to procedure commands can be used:

EXEC my_proc3 "Ivanov" or my_proc3 @k="Ivanov"

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

For call to procedure commands can be used:

EXEC my_proc4 "Waffle",0.05 or EXEC my_proc4 @t="Waffle", @p=0.05

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

CREATE PROC my_proc5 @t VARCHAR(20)='Candy`, @p FLOAT=0.1 AS UPDATE Item SET Price=Price*(1-@p) WHERE Type=@t Example 12.5. Procedure with input parameters and default values. Create a procedure to reduce the price of a product of a given type in accordance with the specified %.

For call to procedure commands can be used:

EXEC my_proc5 "Waffle",0.05 or EXEC my_proc5 @t="Waffle", @p=0.05 or EXEC my_proc5 @p=0.05

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

In the latter case, both parameters (both type and percentage) are not specified when calling the procedure, their values ​​are taken by default.

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

CREATE PROC my_proc6 @m INT, @s FLOAT OUTPUT AS SELECT @s=Sum(Item.Price*Trade.Quantity) FROM Item INNER JOIN Trade ON Item.ItemID=Trade.ItemID GROUP BY Month(Trade.Date) HAVING Month( Deal.Date)=@m Example 12.6. Procedure with input and output parameters. Create a procedure to determine the total cost of goods sold in a particular month.

For call to procedure commands can be used:

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 the month is set to 1).

Create a procedure to determine the total quantity of goods purchased by the firm where a given employee works.

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

Example 12.7. Usage nested procedures. Create a procedure to determine the total quantity of goods purchased by the firm where a given employee works.

Then we will create a procedure that counts the total amount of goods purchased by the firm of interest to us.

CREATE PROC my_proc8 @fam VARCHAR(20), @kol INT OUTPUT AS DECLARE @firm VARCHAR(20) EXEC my_proc7 @fam,@firm OUTPUT SELECT @kol=Sum(Trade.Quantity) FROM Client INNER JOIN Trade ON Client.ClientCode= Deal.ClientCode GROUP BY Client.Company HAVING Client.Company=@firm Example 12.7. Create a procedure to determine the total quantity of goods purchased by the firm where a given employee works.

The procedure is called using the command:

DECLARE @k INT EXEC my_proc8 ‘Ivanov’,@k OUTPUT SELECT @k

Stored procedure is a special type of Transact-SQL statement batch created using the SQL language and procedural extensions. The main difference between a package and a stored procedure is that the latter is stored as a database object. In other words, stored procedures are stored on the server side to improve the performance and consistency of repetitive tasks.

The Database Engine supports stored procedures and system procedures. Stored procedures are created in the same way as all other database objects, i.e. using the DDL language. System Procedures are provided by the Database Engine and can be used to access and modify information in the system catalog.

When creating a stored procedure, you can define an optional list of parameters. Thus, the procedure will take the appropriate arguments each time it is called. Stored procedures can return a value containing user-defined information or, in the event of an error, an appropriate error message.

A stored procedure is precompiled before it is stored as an object in the database. The precompiled form of the procedure is stored in the database and used each time it is called. This property of stored procedures provides the important benefit of eliminating (in almost all cases) procedure recompilations and obtaining a corresponding performance improvement. This property of stored procedures also has a positive effect on the amount of data exchanged between the database system and applications. In particular, a call to a stored procedure of several thousand bytes may require less than 50 bytes. When multiple users perform repetitive tasks using stored procedures, the cumulative effect of these savings can be significant.

Stored procedures can also be used for the following purposes:

to create a log of logs about actions with database tables.

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

Stored procedures that log writes and/or reads to tables provide additional security for the database. Using such procedures, the database administrator can keep track of modifications made to the database by users or applications.

Creating and Executing Stored Procedures

Stored procedures are created using the statement CREATE PROCEDURE, which has the following syntax:

CREATE PROC proc_name [((@param1) type1 [ VARYING] [= default1] )] (, …) AS batch | EXTERNAL NAME method_name Syntax conventions

The schema_name parameter specifies the schema name that is assigned by the owner of the generated stored procedure. The proc_name parameter specifies the name of the stored procedure. The @param1 parameter is a procedure parameter (a formal argument) whose data type is specified by the type1 parameter. Procedure parameters are local within a procedure, just as local variables are local within a package. Procedure parameters are values ​​that are passed by the caller to the procedure for use in it. The default1 parameter specifies the default value for the corresponding procedure parameter. (The default value can also be NULL.)

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

As mentioned earlier, the precompiled form of the procedure is stored in the database and used each time it is called. If, for some reason, a stored procedure needs to be compiled every time it is called, the declaration of the procedure uses option WITH RECOMPILE. Using the WITH RECOMPILE option negates one of the most important benefits of stored procedures: the performance improvement due to a single compilation. Therefore, the WITH RECOMPILE option should only be used if there are frequent changes to the database objects used by the stored procedure.

EXECUTE AS offer defines the security context in which the stored procedure must be executed after it has been called. By setting this context, the Database Engine can control the selection of user accounts for checking access permissions on objects referenced by this stored procedure.

By default, only members of the sysadmin fixed server role and the db_owner or db_ddladmin fixed database role can use the CREATE PROCEDURE statement. However, members of these roles can assign this right to other users using the instruction GRANT CREATE PROCEDURE.

The example below shows how to create a simple stored procedure to work with the Project table:

USE SampleDb; GO CREATE PROCEDURE IncreaseBudget (@percent INT=5) AS UPDATE Project SET Budget = Budget + Budget * @percent/100;

As mentioned earlier, to separate two packages is used GO instruction. The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in the same batch. The IncreaseBudget stored procedure increases the budgets for all projects by a certain percentage, specified by the @percent parameter. The procedure also defines a default percentage value (5) that is applied if this argument is not present during the execution of the procedure.

Stored procedures can access tables that do not exist. This property allows you to debug the procedure code without first creating the appropriate tables and without even connecting to the destination server.

Unlike basic stored procedures, which are always stored in the current database, it is possible to create temporary stored procedures, which are always placed in the temporary system database tempdb. One of the reasons for creating temporary stored procedures may be to avoid repetitive execution of a certain group of statements when connecting to a database. You can create local or global temporary procedures. To do this, the name of the local procedure is specified with a single # character (#proc_name), and the name of the global procedure is specified with a double character (##proc_name).

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

The life cycle of a stored procedure consists of two phases: its creation and its execution. Each procedure is created once and executed many times. The stored procedure is executed by EXECUTE statements a user who owns the procedure or has the EXECUTE right to access the procedure. The EXECUTE statement has the following syntax:

[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] value | [@parameter1=] @variable ] | DEFAULT).. Syntax conventions

With the exception of the return_status parameter, all parameters of the EXECUTE statement have the same boolean value as the parameters of the CREATE PROCEDURE statement of the same name. The return_status parameter defines an integer variable that stores the return status of the procedure. A value can be assigned to a parameter using either a constant (value) or a local variable (@variable). The order of named parameter values ​​is not important, but unnamed parameter values ​​must be supplied in the order in which they are defined in the CREATE PROCEDURE statement.

DEFAULT clause provides default values ​​for a procedure parameter that was specified in the procedure definition. When a procedure expects a value for a parameter for which no default value has been defined and the parameter is missing, or the DEFAULT keyword is specified, an error occurs.

When the EXECUTE statement is the first statement in a batch, the EXECUTE keyword can be omitted. However, it is safer to include this word in every package. The use of the EXECUTE statement is shown in the example below:

USE SampleDb; EXECUTE IncreaseBudget 10;

The EXECUTE statement in this example executes the IncreaseBudget stored procedure, which increases the budget of all projects by 10%.

The following example shows how to create a stored procedure to process data in the Employee and Works_on tables:

The ModifyEmpId procedure in the example illustrates the use of stored procedures as part of the referential integrity process (in this case between the Employee and Works_on tables). Such a stored procedure can be used inside a trigger definition, which actually enforces referential integrity.

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

This stored procedure can be executed using the following statements:

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

This procedure counts the number of projects that an employee with personnel number @empId is working on and assigns the resulting value to the ©counter parameter. After deleting all rows for a given personnel number from the Employee and Works_on tables, the calculated value is assigned to the @quantityDeleteEmployee variable.

The parameter value is returned to the calling procedure only if the OUTPUT option is specified. In the example above, the DeleteEmployee procedure passes the @counter parameter to the calling procedure, so the stored procedure returns the value to the system. Therefore, the @counter parameter must be specified both in the OUTPUT option when declaring the procedure, and in the EXECUTE statement when calling it.

WITH RESULTS SETS clause of an EXECUTE statement

In SQL Server 2012, the EXECUTE statement is typed WITH RESULTS SETS clause A that, under certain conditions, can change the shape of the result set of the stored procedure.

The following two examples will help explain this sentence. The first example is an introductory example that shows what the result might look like when the WITH RESULTS SETS clause is omitted:

The EmployeesInDept procedure is a simple procedure that displays the personnel numbers and last names of all employees working in a particular department. The department number is a procedure parameter and must be specified when calling the procedure. Executing this procedure outputs a table with two columns whose headings match the names of the corresponding columns in the database table, i.e. id and lastname. To change the result column headers (as well as their data type), SQL Server 2012 uses the new WITH RESULTS SETS clause. The application of this clause is shown in the example below:

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

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

As you can see, running a stored procedure using the WITH RESULT SETS clause in an EXECUTE statement allows you to change the names and data type of the result set columns produced by the procedure. Thus, this new functionality provides more flexibility in executing stored procedures and placing their results in a new table.

Changing the Structure of Stored Procedures

The Database Engine also supports the statement ALTER PROCEDURE to modify the structure of stored procedures. The ALTER PROCEDURE statement is typically used to modify Transact-SQL statements within a procedure. All parameters of the ALTER PROCEDURE statement have the same meaning as the parameters of the CREATE PROCEDURE statement of the same name. The primary purpose of using this statement is to avoid overriding existing stored procedure permissions.

The Database Engine supports CURSOR data type. This data type is used to declare cursors in stored procedures. Cursor is a programming construct used to store the results of a query (usually a set of rows) and to allow users to display that result row by row.

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

Stored procedures and the common language runtime

SQL Server supports the Common Language Runtime (CLR), which allows you to develop various database objects (stored procedures, user-defined functions, triggers, user-defined aggregates, and user-defined data types) using C# and Visual Basic. The common language runtime also allows these objects to be executed using the common runtime system.

The common language runtime is enabled and disabled via the option clr_enabled system procedure sp_configure, which is launched for execution by the instruction RECONFIGURE. The following example shows how you can enable the common language runtime using the sp_configure system procedure:

USE SampleDb; EXEC sp_configure "clr_enabled",1 RECONFIGURE

Creating, compiling, and saving a procedure using the CLR requires the following sequence of steps, in the order listed:

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

Using instruction CREATE ASSEMBLY, create the appropriate executable.

Execute a procedure using the EXECUTE statement.

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

First, create the desired program in a development environment such as Visual Studio. Compile the finished program to object code using the C# or Visual Basic compiler. This code is stored in a dynamic link library (.dll) file that serves as the source for the CREATE ASSEMBLY statement, which creates intermediate executable code. Next, issue a CREATE PROCEDURE statement to save the code being executed as a database object. Finally, run the procedure using the familiar EXECUTE statement.

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

Using System.Data.SqlClient; using Microsoft.SqlServer.Server; public partial class StoredProcedures ( public static int CountEmployees() ( int rows; SqlConnection connection = new SqlConnection("Context Connection=true"); connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = "select count(*) as "Number of employees" " + "from Employee"; rows = (int)cmd.ExecuteScalar(); connection.Close(); return rows; ) )

This procedure implements a query to count the number of rows in the Employee table. Using directives at the beginning of a program specify the namespaces required for its execution. The use of these directives allows you to specify class names in the source code without explicitly specifying the corresponding namespaces. Next, the StoredProcedures class is defined, for which the SqlProcedure attribute, which informs the compiler that this class is a stored procedure. Inside the class code, the CountEmployees() method is defined. The connection to the database system is established through an instance of the class SqlConnection. To open a connection, the Open() method of this instance is used. A CreateCommand() method allows you to access an instance of a class SqlCommnd, to which the desired SQL command is passed.

In the following code snippet:

Cmd.CommandText = "select count(*) as "Number of employees" " + "from Employee";

uses a SELECT statement to count the number of rows in the Employee table and display the result. The command text is specified by setting the CommandText property of the cmd variable to the instance returned by the CreateCommand() method. Next is called ExecuteScalar() method instance of SqlCommand. This method returns a scalar value that is converted to the integer data type int and assigned to the rows variable.

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

USE SampleDb; GO CREATE ASSEMBLY CLRStoredProcedures FROM "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll" WITH PERMISSION_SET = SAFE

The CREATE ASSEMBLY statement takes managed code as input and creates an appropriate object for which you can create common language runtime (CLR) stored procedures, user-defined functions, and triggers. This instruction has the following syntax:

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

The assembly_name parameter specifies the name of the assembly. The optional AUTHORIZATION clause specifies the name of a role as the owner of this assembly. The FROM clause specifies the path where the assembly to load is located.

WITH PERMISSION_SET clause is a very important clause of the CREATE ASSEMBLY statement and should always be specified. It defines the set of access rights granted to the assembly code. The SAFE rights set is the most restrictive. Assembly code that has these rights cannot access external system resources such as files. The EXTERNAL_ACCESS rights set allows assembly code to access certain external system resources, while the UNSAFE rights set provides unrestricted access to resources, both inside and outside the database system.

To store assembly code information, the user must be able to issue a CREATE ASSEMBLY statement. The assembly is owned by the user (or role) that executes the statement. You can change the owner of an assembly by using the AUTHORIZATION clause of the CREATE SCHEMA statement.

The Database Engine also supports ALTER ASSEMBLY and DROP ASSEMBLY statements. ALTER ASSEMBLY Statement used to update an assembly to the latest version. This instruction also adds or removes files associated with the corresponding assembly. DROP ASSEMBLY Statement removes the specified assembly and all associated files from the current database.

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

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

The CREATE PROCEDURE statement in the example differs from the same statement in the earlier examples in that it contains EXTERNAL NAME parameter. This option specifies that the code is generated by the CLR. The name in this sentence consists of three parts:

assembly_name.class_name.method_name

assembly_name - specifies the name of the assembly;

class_name - specifies the name of the general class;

method_name - optional part, specifies the name of the method that is set inside the class.

The execution of the CountEmployees procedure is shown in the example below:

USE SampleDb; DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count -- Returns 7

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

Procedure declaration

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 a field (no parameters)
. DYNAMIC RESULT SET indicates that the procedure can open the specified number of cursors that will remain open after the procedure returns

Notes
It is not recommended to use many parameters in stored procedures (primarily large numbers and character strings) due to network and stack congestion. In practice, existing dialects of Transact-SQL, PL/SQL, and Informix show significant departures from the standard, both in parameter declaration and use, variable declaration, and subroutine invocation. Microsoft recommends using the following approximation to estimate the cache size of stored procedures:
=(maximum concurrent users)*(largest execution plan size)*1.25. Determining the size of the execution plan in pages can be done using the command: DBCC MEMUSAGE.

Procedure call

In many existing DBMSs, stored procedures are called using the statement:

EXECUTE PROCEDURE [(][)]

Note: A call to stored procedures can be made from within an application, another stored procedure, or interactively.

Procedure declaration example

CREATE PROCEDURE Proc1 AS //declaring a procedure
DECLARE Cur1 CURSOR FOR SELECT SName, City FROM SalesPeople WHERE Rating>200 //declare a cursor
OPEN Cur1 //open cursor
FETCH NEXT FROM Cur1 //read data from cursor
WHILE @@Fetch_Status=0
BEGIN
FETCH NEXT FROM Cur1
END
CLOSE Cur1 //close the cursor
DEALLOCATE Cur1
EXECUTE Proc1 //run the procedure

Polymorphism
Two subroutines with the same name can be created in the same schema if the parameters of the two subroutines are sufficiently different from each other that they can be distinguished. In order to distinguish between two routines with the same name in the same schema, each is given an alternative and unique name (specific name). Such a name may be explicitly specified when the subroutine is defined. When calling subroutines with several identical names, determining the desired subroutine is carried out in several steps:
. Initially, all procedures with the specified name are defined, and if there are none, then all functions with the specified name.
. For further analysis, only those routines are left for which the given user has the execute privilege (EXECUTE).
. For them, those are selected whose number of parameters corresponds to the number of call arguments. The specified data types of the parameters and their positions are checked.
. If there is more than one subroutine left, then the one with the shorter qualifying name is selected.
In practice, in Oracle, polymorphism is supported for functions declared only in a package, DB@ - in different schemas, and overloading is prohibited in Sybase and MS SQL Server.

Removing and changing procedures
The following statement is used to delete a procedure:

To modify a procedure, use the statement:

ALTER PROCEDURE [([{IN|OUT|INOUT}])]
BEGIN [ATOMIC]

END

Privileges to execute 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 you can use for your own purposes.