A situation where stored procedures can degrade query performance is considered.
When compiling stored procedures in MS SQL Server 2000, the stored procedures are placed in the procedural cache, which can improve performance during their execution by eliminating the need for parsing, optimizing and compiling stored procedure code.
On the other hand, there are pitfalls in storing the compiled code of a stored procedure that can have the opposite effect.
The fact is that when compiling a stored procedure, the execution plan of those operators that make up the procedure code is compiled, respectively, if the compiled stored procedure is cached, then its execution plan is cached, and therefore, the stored procedure will not be optimized for a specific situation and query parameters.
Will do a little experiment to demonstrate this.
STEP 1... Database creation.
Let's create a separate database for the experiment.
CREATE DATABASE test_sp_perf
ON (NAME = "test_data", FILENAME = "c: \ temp \ test_data", SIZE = 1, MAXSIZE = 10, FILEGROWTH = 1Mb)
LOG ON (NAME = "test_log", FILENAME = "c: \ temp \ test_log", SIZE = 1, MAXSIZE = 10, FILEGROWTH = 1Mb)
STEP 2. Creating a table.
CREATE TABLE sp_perf_test (column1 int, column2 char (5000))
STEP 3. Filling the table with test lines. Duplicate rows are intentionally added to the table. 10,000 lines numbered from 1 to 10,000, and 10,000 lines numbered 50,000.
DECLARE @i int
SET @ i = 1
WHILE (@i<10000)
BEGIN
INSERT INTO sp_perf_test (column1, column2) VALUES (@i, "Test string #" + CAST (@i as char (8)))
INSERT INTO sp_perf_test (column1, column2) VALUES (50000, "Test string #" + CAST (@i as char (8)))
SET @ i = @ i + 1
END
SELECT COUNT (*) FROM sp_perf_test
Go
STEP 4. Create a non-clustered index. Since the execution plan is cached with the procedure, the index will be used the same for all calls.
CREATE NONCLUSTERED INDEX CL_perf_test ON sp_perf_test (column1)
Go
STEP 5. Creating a stored procedure. The procedure simply executes a SELECT statement with a condition.
CREATE PROC proc1 (@param int)
AS
SELECT column1, column2 FROM sp_perf_test WHERE [email protected]
Go
STEP 6. Running a stored procedure. When starting a vulnerable procedure, a selective parameter is specially used. As a result of the procedure, we get 1 line. The execution plan indicates the use of a non-clustered index because the query is selective and is the best way to retrieve a row. A procedure optimized for fetching one row is stored in the procedural cache.
EXEC proc1 1234
Go
STEP 7. Running a stored procedure with a non-selective parameter. The value of 50,000 is used as a parameter. Lines with such a value of the first column of about 10,000, respectively, using a nonclustered index and the bookmark lookup operation is ineffective, but since the compiled code with the execution plan is stored in the procedural cache, it will be used. The execution plan shows this, as well as the fact that the bookmark lookup operation was performed for 9999 lines.
EXEC proc1 50,000
Go
STEP 8. Fetching rows with the first field equal to 50,000. Executing a separate query will optimize and compile the query with the specific value of the first column. As a result, the query optimizer detects that the field is duplicated many times and decides to use the table scan operation, which in this case is much more efficient than using a nonclustered index.
SELECT column1, column2 FROM sp_perf_test WHERE column1 = 50000
Go
Thus, we can conclude that using stored procedures may not always improve query performance. You should be very careful about stored procedures that operate on results with a variable number of lines and use different execution plans.
You can use the script to repeat the experiment on a fresh MS SQL server.
Stored procedure types
There are several types in SQL Server stored procedures.
- Systemic stored procedures are designed to perform various administrative actions. Almost all server administration actions are performed with their help. We can say that the system stored procedures are an interface providing work with system tables, which ultimately boils down to changing, adding, deleting and retrieving data from system tables of both user and system databases. Systemic stored procedures are prefixed with sp_, are stored in the system database, and can be called in the context of any other database.
- Custom stored procedures carry out certain actions. Stored procedures- a full-fledged database object. As a result, each stored procedure is located in a specific database, where it is executed.
- Temporary stored procedures exist only for some time, after which they are automatically destroyed by the server. They are divided into local and global. Local temporary stored procedures can only be called from the connection in which they were created. When you create such a procedure, you need to give it a name that starts with a single # character. Like all temporary objects, stored procedures of this type are automatically deleted when the user disconnects, restarts or stops the server. Global temporary stored procedures available for any server connections that have the same procedure. To define it, you just need to give it a name starting with ## symbols. These procedures are deleted when the server is restarted or stopped, or when the connection in the context of which they were created is closed.
Creating, Modifying, and Deleting Stored Procedures
Creation stored procedure involves the solution of the following tasks:
- defining the type of created stored procedure: temporary or custom. In addition, you can create your own system stored procedure by giving it a name prefixed with sp_ and placing it in the system database. This procedure will be available in the context of any database on the local server;
- planning of access rights. While creating stored procedure it should be borne in mind that it will have the same access rights to database objects as the user who created it;
- definition stored procedure parameters... Like the procedures found in most programming languages, stored procedures can have input and output parameters;
- code development stored procedure... Procedure code can contain a sequence of any SQL commands, including calls to others. stored procedures.
Creating a new one and modifying an existing one stored procedure is done with the following command:
<определение_процедуры>:: = (CREATE | ALTER) procedure_name [; number] [(@ parameter_name datatype) [= default]] [, ... n] AS sql_operator [... n]
Let's consider the parameters of this command.
Using the prefixes sp_, #, ##, the created procedure can be defined as system or temporary. As you can see from the command syntax, it is not allowed to specify the name of the owner who will own the created procedure, as well as the name of the database where it should be located. Thus, in order to place the created stored procedure on a specific database, you must run the CREATE PROCEDURE command in the context of that database. When handling out of body stored procedure you can use shortened names for objects of the same database, that is, without specifying the database name. When you need to refer to objects located in other databases, specifying the name of the database is required.
The number in the name is the identification number stored procedure, which uniquely identifies it in a group of procedures. For the convenience of managing procedures, logically of the same type stored procedures can be grouped by giving them the same name, but different identification numbers.
To transfer input and output data in the created stored procedure parameters can be used whose names, like the names of local variables, must begin with the @ symbol. One stored procedure many parameters can be specified, separated by commas. The body of a procedure should not use local variables whose names are the same as the names of the parameters of this procedure.
To determine the type of data that will have the corresponding stored procedure parameter, any SQL data types, including user-defined ones, will work. However, the CURSOR data type can only be used as output parameter stored procedure, i.e. specifying the keyword OUTPUT.
The presence of the OUTPUT keyword means that the corresponding parameter is intended to return data from stored procedure... However, this does not mean at all that the parameter is not suitable for passing values to stored procedure... Specifying the OUTPUT keyword instructs the server to exit stored procedure assign the current value of the parameter to the local variable that was specified as the parameter value when calling the procedure. Note that when the OUTPUT keyword is specified, the value of the corresponding parameter when calling a procedure can only be set using a local variable. You are not allowed to use any expressions or constants that are valid for normal parameters.
The VARYING keyword is used in conjunction with
Stored procedure (eng. stored procedure) is a named database program object. There are several types of stored procedures in SQL Server.
System stored procedures (eng. system stored procedure) are supplied by the DBMS developers and are used to perform actions with the system catalog or obtain system information. Their names usually start with the "sp_" prefix. Stored procedures of all types are invoked with the EXECUTE command, which can be abbreviated as EXEC. For example, the sp_helplogins stored procedure, run without parameters, generates two reports on account names (eng. logins) and their corresponding users in each database (eng. users).
EXEC sp_helplogins;
To give an idea of the actions performed by using system stored procedures, in table. 10.6 provides some examples. There are over a thousand system stored procedures in SQL Server.
Table 10.6
SQL Server System Stored Procedure Examples
The user can create stored procedures in user databases and in databases for temporary objects. In the latter case, the stored procedure will be temporal. As with temporary tables, the name of a temporary stored procedure must begin with a "#" prefix if it is a local temporary stored procedure, or with "##" if it is a global one. The local temporary procedure can only be used within the connection in which it was created, the global one - and within other connections.
SQL Server programmable objects can be created using either Transact-SQL tools or assemblies (eng. assembly) in the Common Language Runtime (CRL) of the Microsoft .Net Framework. In this tutorial, only the first method will be considered.
Stored procedures are created using the CREATE PROCEDURE statement (can be abbreviated to PROC), the format of which is shown below:
CREATE (PROC I PROCEDURE) proc_name [; number]
[(gparameter data_type)
[“Default] |
[WITH
[FOR REPLICATION]
AS ([BEGIN] sql_statement [;] [... n] [END])
If a stored procedure (or trigger, function, view) is created with the ENCRYPTION option, its code is converted so that the text becomes unreadable. At the same time, as noted in, the algorithm used has been carried over from earlier versions of SQL Server and cannot be considered as a reliable protection algorithm - there are utilities that allow you to quickly perform the reverse conversion.
The RECOMPILE option instructs the system to recompile the text each time the procedure is called. Normally, the procedure compiled at the first start is saved in the cache, which can improve performance.
EXECUTE AS defines the security context in which the procedure should be executed. Then one of the values f CALLER | SELF | OWNER | "user_name"). CALLER is the default and means that the code will be executed in the security context of the user calling this module. Accordingly, the user must have permissions not only for the programmed object itself, but also for other database objects affected by it. EXECUTE AS SELF means using user context creating or modifying a programmable object. OWNER indicates that the code will be executed in the context of the current owner of the procedure. If no owner is defined for it, then the owner of the schema to which it belongs is meant. EXECUTE AS "user_name" allows you to explicitly specify the username (in single quotes).
Parameters can be specified for the procedure. These are local variables used to pass values to the procedure. If a parameter is declared with the OUTPUT keyword (or OUT for short), it is an output parameter: the value specified in the procedure after its termination can be used by the program that called the procedure. The READONLY keyword means that the parameter value cannot be changed inside the stored procedure.
Parameters can be assigned values but default, which will be used if the parameter value is not specified explicitly when the procedure is called. Let's consider an example:
CREATE PROC surma (@ a int, @b int = 0,
© result int OUTPUT) AS
SET @ result = 0a + 0b
We have created a procedure with three parameters, and the @b parameter has a default value of 0, and the @result parameter is an output parameter: through it, the value is returned to the calling program. The actions performed are quite simple - the output parameter receives the value of the sum of the two inputs.
When working in SQL Server Management Studio, the created stored procedure can be found in the Programmable DB Objects section (eng. Programmability) in the subsection for stored procedures (Figure 10.2).
When calling a procedure, you can use both variables and constants as input parameters. Let's look at two examples. In the first, the input parameters of the procedure are explicitly set by constants; the keyword OUTPUT is specified for the output parameter in the call. In the second option, the value of the variable is used as the first input parameter, and for the second parameter, using the DEFAULT keyword, it is indicated that the default value should be used:
Rice. 10.2.
DECLARE @ with int;
EXEC summa 10.5, @ c OUTPUT;
PRINT 0c; - 15 will be displayed
DECLARE Gi int = 5;
- when calling, use the default value
EXEC summa Gi, DEFAULT, 0с OUTPUT;
PRINT 0c; - 5 will be displayed
Let us now consider an example with the analysis of the return code with which the procedure ends. Let it be necessary to calculate how many books in the Bookl table have been published in a given range of years. Moreover, if the initial year turned out to be more than the final year, the procedure returns "1" and does not count, otherwise, we count the number of books and return 0:
CREATE PROC dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) AS
IF 0FirsYear> 0LastYear RETURN 1
SET @ result = (SELECT COUNT (*) FROM dbo.Bookl
WHERE BETWEEN 0FirsYear AND 0LastYear);
Consider a variant of calling this procedure, in which the return code is stored in the integer variable 0ret, after which its value is analyzed (in this case, it will be 1). The CAST function used in the PRINT operator is used to convert the value of the integer variable Gres to a string type:
DECLARE 0ret int, Gres int
EXEC Gret = rownum 2004, 2002, Gres OUT;
IF 0ret = l PRINT "Starting year is greater than ending year"
PRINT "Number of books" + CAST (Gres as varchar (20))
Stored procedures can not only read data from a table, but also modify data and even create tables and a number of other database objects.
However, you cannot create schemas, functions, triggers, procedures, and views from a stored procedure.
The following example illustrates both these capabilities and the scope for temporary objects. The stored procedure below checks for the existence of the # thab2 temporary table; if this table does not exist, then creates it. After that, the values of two columns are entered into the # Tab2 table, and the contents of the table are displayed by the SELECT statement:
CREATE PROC My_Procl (@id int, @name varchar (30))
IF OBJECT_ID ("tempdb.dbo. # Tab21) IS NULL
INSERT INTO dbo. # Tab2 (id, name) VALUES (0id, 0name)
SELECT * FROM dbo. # Tab2 –№1
Before the first call of the stored procedure, we will create the temporary table # Thab2 used in it. Pay attention to the EXEC operator. In the previous examples, parameters were passed to the procedure "by position", and in this case, another format for passing parameters is used - "by name", the parameter name and its value are explicitly indicated:
CREATE TABLE dbo. # Tab2 (id int, name varchar (30));
EXEC My_Procl 0name = "lvan", 0id = 2;
SELECT * FROM dbo. # Tab2; –№2
In the above example, the SELECT statement will be executed twice: the first time - inside the procedure, the second time - from the calling code fragment (marked with the comment "# 2").
Before the second call of the procedure, we will delete the temporary table # Tab2. Then the temporary table of the same name will be created from the stored procedure:
DROP TABLE dbo. # Tab2;
EXEC My_Procl 0name = "Ivan", 0id = 2;
SELECT * FROM dbo. # Tab2; –№2
In this case, only the SELECT statement inside the procedure (with the comment "Xa 1") will display the data. SELECT "# 2" will result in an error because the temporary table created in the stored procedure has already been deleted from tempdb at the time the procedure returns.
You can drop a stored procedure using the DROP PROCEDURE statement. Its format is shown below. One operator can delete several stored procedures by listing them separated by commas:
DROP (PROC I PROCEDURE) (procedure) [
For example, let's remove the previously created procedure summa:
DROP PROC summa;
You can make changes to an existing procedure (and in fact, override it) using the ALTER PROCEDURE statement (admissible
the abbreviation PROC). With the exception of the ALTER keyword, the format of the statement is similar to that of CREATE PROCEDURE. For example, let's change the dbo procedure. rownum by setting it an execute option in the security context of the owner:
ALTER PROC dbo.rownum (SFirsYear int,
SLastYear int, Sresult int OUTPUT)
WITH EXECUTE AS Owner is an installable option
IF 0FirsYear> 0LastYear RETURN 1 ELSE BEGIN
SET 0result = (SELECT COUNT (*) FROM dbo.Bookl
WHERE BETWEEN SFirsYear AND SLastYear);
In some cases, it may be necessary to dynamically form a command and execute it on the database server. This task can also be solved using the EXEC operator. The example below fetches records from the Bookl table by the condition that the Year attribute is equal to the value set using a variable:
DECLARE 0y int = 2000;
EXEC ("SELECT * FROM dbo.Bookl WHERE =" [email protected]) ;
Execution of dynamically generated instructions creates prerequisites for the implementation of computer attacks such as "SQL injection" (eng. SQL injection). The essence of the attack is that the attacker injects its own SQL code into a dynamically generated query. This usually happens when substituted parameters are taken from the results of user input.
Let's change the previous example a little:
DECLARE 0y varchar (100);
SET 0y = "2OOO"; - we got this from the user
If we assume that we received the string value assigned in the SET statement from the user (no matter how, for example, through a web application), then the example illustrates the "regular" behavior of our code.
DECLARE 0y varchar (100);
SET 0y = "2000; DELETE FROM dbo.Book2"; - injection
EXEC ("SELECT * FROM dbo.Book2 WHERE =" + 0y);
In such cases, it is recommended to use the sp_executcsql system stored procedure whenever possible, which allows you to control the type of parameters, which is one of the barriers to SQL injection. Without considering its format in detail, let's analyze an example similar to the one presented earlier:
EXECUTE sp_executesql
N "SELECT * FROM dbo.Bookl WHERE = 0y",
This explicitly specifies the type of parameter used in the query and will be monitored by SQL Server during execution. The letter "N" in front of the quotes indicates that this is a Unicode literal constant as required by the procedure. A parameter can be assigned not only a constant value, but also the value of another variable.
Stored procedure is a special type of batch of Transact-SQL statements created using the SQL language and procedural extensions. The main difference between a package and a stored procedure is that the latter is stored as a database object. In other words, stored procedures are persisted on the server side to improve performance and repeatability.
The Database Engine supports stored procedures and system procedures. Stored procedures are created in the same way as all other database objects, i.e. using the DDL language. System procedures are provided by the Database Engine and can be used to access and modify information in the system catalog.
When you create a stored procedure, you can define an optional parameter list. Thus, the procedure will accept the appropriate arguments each time it is called. Stored procedures can return a value containing user-defined information or, in the event of an error, an associated error message.
The stored procedure is precompiled before being stored as an object in the database. The precompiled form of the procedure is stored in the database and used each time it is called. This property of stored procedures provides an important benefit in that it eliminates (in almost all cases) re-compilation of the procedure and provides corresponding performance improvements. This property of stored procedures also has a positive effect on the amount of data exchanged between the database system and applications. In particular, a call to a stored procedure that is several thousand bytes in size may require less than 50 bytes. When multiple users perform repetitive tasks using stored procedures, the cumulative effect of these savings can be significant.
Stored procedures can also be used for the following purposes:
to create a log of actions with database tables.
The use of stored procedures provides a level of security control that significantly exceeds the level of security provided by the use of GRANT and REVOKE statements, which grant different access privileges to users. This is possible because authorization to execute a stored procedure is independent of authorization to modify objects contained in a given stored procedure, as described in the next section.
Stored procedures that generate logs for writes and / or reads on tables provide additional security for the database. Using these procedures, the database administrator can track modifications made to the database by users or applications.
Creating and Executing Stored Procedures
Stored procedures are created using the statement CREATE PROCEDURE which has the following syntax:
CREATE PROC proc_name [((@ param1) type1 [VARYING] [= default1])] (,…) AS batch | EXTERNAL NAME method_name Syntax Conventions
The schema_name parameter specifies the name of the schema that is assigned as the owner of the generated stored procedure. The proc_name parameter specifies the name of the stored procedure. The @ param1 parameter is a procedure parameter (formal argument) whose data type is specified by the type1 parameter. Procedure parameters are local within a procedure, just as local variables are local within a package. Procedure parameters are values that are passed by the caller to the procedure for use in it. The default1 parameter defines the default value for the corresponding procedure parameter. (The default can also be NULL.)
OUTPUT option indicates that a procedure parameter is a returnable parameter that can be used to return a value from a stored procedure to the calling procedure or system.
As mentioned earlier, the precompiled form of the procedure is stored in the database and used each time it is called. If, for some reason, the stored procedure needs to be compiled every time it is called, when declaring the procedure, use option WITH RECOMPILE... Using the WITH RECOMPILE option negates one of the most important advantages of stored procedures: the performance improvements from a single compilation. Therefore, the WITH RECOMPILE option should only be used when making frequent changes to the database objects used by the stored procedure.
EXECUTE AS clause defines the security context in which the stored procedure should execute after it is called. By setting this context, you can use the Database Engine to control the selection of user accounts for checking access permissions on objects referenced by this stored procedure.
By default, only members of the sysadmin fixed server role and the db_owner or db_ddladmin fixed database role can use the CREATE PROCEDURE statement. But members of these roles can assign this right to other users using the instruction GRANT CREATE PROCEDURE.
The example below shows how to create a simple stored procedure to work with the Project table:
USE SampleDb; GO CREATE PROCEDURE IncreaseBudget (@percent INT = 5) AS UPDATE Project SET Budget = Budget + Budget * @ percent / 100;
As mentioned earlier, to separate two packets, use GO instruction... The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in the same package. The IncreaseBudget stored procedure increases budgets for all projects by a specific number of percent, specified by the @percent parameter. The procedure also defines a default percentage value (5), which is used if this argument is not present during the procedure.
Stored procedures can access tables that do not exist. This property allows you to debug procedure code without first creating the appropriate tables or even connecting to the destination server.
Unlike basic stored procedures, which are always stored in the current database, it is possible to create temporary stored procedures that are always placed in the temporary system database tempdb. One reason for creating temporary stored procedures may be to avoid the repetitive execution of a specific group of statements when connecting to a database. You can create local or global temporary procedures. For this, the name of the local procedure is specified with a single # symbol (#proc_name), and the name of the global procedure is specified with a double (## proc_name).
A local temporary stored procedure can only be executed by the user who created it, and only during the connection to the database in which it was created. The global temporary procedure can be executed by all users, but only until the last connection in which it is executed ends (usually the connection of the procedure creator).
The life cycle of a stored procedure consists of two stages: its creation and its execution. Each procedure is created once, and executed repeatedly. The stored procedure is executed by EXECUTE statements a user who owns a procedure or has EXECUTE permission to access that procedure. The EXECUTE statement has the following syntax:
[] [@return_status =] (proc_name | @proc_name_var) ([[@ parameter1 =] value | [@ parameter1 =] @variable] | DEFAULT) .. Syntax conventions
Except for the return_status parameter, all parameters of the EXECUTE statement have the same logical meaning as the parameters of the same name for the CREATE PROCEDURE statement. The return_status parameter defines an integer variable that stores the return state of the procedure. A value can be assigned to a parameter using either a constant (value) or a local variable (@variable). The order of the values of the named parameters is not important, but the values of the unnamed parameters must be supplied in the order in which they are defined in the CREATE PROCEDURE statement.
DEFAULT clause provides default values for a procedure parameter that was specified in the procedure definition. An error occurs when a procedure expects a value for a parameter for which no default value has been defined and no parameter is specified, or the keyword DEFAULT is specified.
When the EXECUTE statement is the first statement in a batch, the EXECUTE keyword can be omitted. However, it is safer to include this word in every packet. The use of the EXECUTE statement is shown in the example below:
USE SampleDb; EXECUTE IncreaseBudget 10;
The EXECUTE statement in this example executes the IncreaseBudget stored procedure, which increases the budget for all projects by 10%.
The example below shows how to create a stored procedure to process data in the Employee and Works_on tables:
The ModifyEmpId procedure in the example illustrates the use of stored procedures as part of the referential integrity process (in this case, between the Employee and Works_on tables). A similar stored procedure can be used within a trigger definition that actually enforces referential integrity.
The example below shows the use of the OUTPUT clause in a stored procedure:
This stored procedure can be executed using the following instructions:
DECLARE @quantityDeleteEmployee INT; EXECUTE DeleteEmployee @ empId = 18316, @ [email protected] OUTPUT; PRINT N "Deleted employees:" + convert (nvarchar (30), @quantityDeleteEmployee);
This procedure counts the number of projects on which the employee with personnel number @empId is busy, and assigns the resulting value to the parameter © counter. After deleting all rows for a given personnel number from the Employee and Works_on tables, the calculated value is assigned to the @quantityDeleteEmployee variable.
The parameter value is returned to the calling procedure only if the OUTPUT option is specified. In the above example, the DeleteEmployee procedure passes the @counter parameter to the calling procedure, so the stored procedure returns a value to the system. Therefore, the @counter parameter must be specified both in the OUTPUT option when declaring a procedure, and in the EXECUTE statement when calling it.
WITH RESULTS SETS clause of EXECUTE statement
In SQL Server 2012, the EXECUTE statement is entered WITH RESULTS SETS clause, through which, when certain conditions are met, you can change the form of the result set of the stored procedure.
The following two examples will help explain this sentence. The first example is an introductory example that shows what the result might look like when the WITH RESULTS SETS clause is omitted:
The EmployeesInDept procedure is a simple procedure that displays the personnel numbers and last names of all employees working in a specific department. The department number is a parameter of the procedure and must be specified when calling it. Performing this procedure displays a table with two columns, the headers of which coincide with the names of the corresponding columns of the database table, i.e. Id and LastName. SQL Server 2012 uses the new WITH RESULTS SETS clause to change the result column headings (as well as their data type). The application of this proposal is shown in the example below:
USE SampleDb; EXEC EmployeesInDept "d1" WITH RESULT SETS ((INT NOT NULL, [Last Name] CHAR (20) NOT NULL));
The result of executing a stored procedure called in this way will be as follows:
As you can see, running the stored procedure using the WITH RESULT SETS clause in the EXECUTE statement allows you to change the names and data type of the columns in the result set returned by the procedure. Thus, this new functionality provides more flexibility in executing stored procedures and placing their results in a new table.
Changing the structure of stored procedures
The Database Engine also supports the statement ALTER PROCEDURE to modify the structure of stored procedures. ALTER PROCEDURE is typically used to modify Transact-SQL statements within a procedure. All parameters of the ALTER PROCEDURE statement have the same meaning as the parameters of the same name for the CREATE PROCEDURE statement. The main purpose of using this statement is to avoid overriding existing stored procedure rights.
The Database Engine supports data type CURSOR... This data type is used to declare cursors in stored procedures. Cursor is a programming construct used to store the results of a query (usually a set of rows) and to enable users to display that result line by line.
To delete one or a group of stored procedures, use DROP PROCEDURE statement... Only the owner or members of the db_owner and sysadmin fixed roles can delete a stored procedure.
Stored Procedures and the CLR
SQL Server supports the Common Language Runtime (CLR), which allows you to develop various database objects (stored procedures, UDFs, triggers, UDFs, and UDDs) using C # and Visual Basic. The CLR also allows you to execute these objects using the common runtime system.
The CLR is allowed and denied by the option clr_enabled system procedure sp_configure, which is started for execution by the instruction RECONFIGURE... The example below shows how the CLR can be enabled using the sp_configure system procedure:
USE SampleDb; EXEC sp_configure "clr_enabled", 1 RECONFIGURE
To create, compile, and save a procedure using the CLR, you must complete the following sequence of steps, in order:
Create a stored procedure in C # or Visual Basic and then compile it using the appropriate compiler.
Using the instruction CREATE ASSEMBLY, create the corresponding executable file.
Execute the procedure using the EXECUTE statement.
The figure below shows a flowchart of the previously outlined steps. The following is a more detailed description of this process.
First, create the required 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 saved in a dynamic link library (.dll) file, which is the source for the CREATE ASSEMBLY statement, which generates executable intermediate code. Next, run the CREATE PROCEDURE statement to save the executable code as a database object. Finally, run the procedure using the familiar EXECUTE statement.
The example below shows the source code for a stored procedure in C #:
Using System.Data.SqlClient; using Microsoft.SqlServer.Server; public partial class StoredProcedures (public static int CountEmployees () (int rows; SqlConnection connection = new SqlConnection ("Context Connection = true"); connection.Open (); SqlCommand cmd = connection.CreateCommand (); cmd.CommandText = "select count (*) as "Number of Employees" "+" from Employee "; rows = (int) cmd.ExecuteScalar (); connection.Close (); return rows;))
This procedure implements a query to count the number of rows in the Employee table. The using directives at the beginning of the program specify the namespaces required to execute the program. Using 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. The CountEmployees () method is defined inside the class code. The connection to the database system is established through an instance of the class SqlConnection... To open a connection, the Open () method of that instance is used. A CreateCommand () method allows you to access an instance of the class SqlCommnd to which the required SQL command is passed.
In the following code snippet:
Cmd.CommandText = "select count (*) as" Number of Employees "" + "from Employee";
uses a SELECT statement to count the number of rows in the Employee table and display the result. The command text is specified by setting the CommandText property of the cmd variable to the instance returned by the CreateCommand () method. Next is called ExecuteScalar () method a SqlCommand instance. This method returns a scalar value that is converted to the int data type and assigned to rows.
You can now compile this code using Visual Studio. I added this class to the project named CLRStoredProcedures, so Visual Studio will compile the assembly of the same name with the * .dll extension. The example below shows the next step in creating a stored procedure: creating executable code. Before running the code in this example, you need to know the location of the compiled dll file (usually located in the project's Debug folder).
USE SampleDb; GO CREATE ASSEMBLY CLRStoredProcedures FROM "D: \ Projects \ CLRStoredProcedures \ bin \ Debug \ CLRStoredProcedures.dll" WITH PERMISSION_SET = SAFE
The CREATE ASSEMBLY statement takes managed code as input and creates an appropriate object for which you can create CLR stored procedures, UDFs, and triggers. This statement has the following syntax:
CREATE ASSEMBLY assembly_name [AUTHORIZATION owner_name] FROM (dll_file) Syntax Conventions
Assembly_name specifies the name of the assembly. The optional AUTHORIZATION clause specifies the role name as the owner of this assembly. The FROM clause specifies the path where the assembly to load is located.
WITH PERMISSION_SET clause is a very important clause of the CREATE ASSEMBLY statement and must always be included. It defines the set of access rights granted to the assembly code. The SAFE set of rights is the most restrictive. Assembly code that has these rights cannot access external system resources such as files. The EXTERNAL_ACCESS privilege set allows assembly code to access specific external system resources, while the UNSAFE privilege set grants unrestricted access to resources, both inside and outside the database system.
The user must be able to execute the CREATE ASSEMBLY statement to preserve information about the assembly code. An assembly is owned by the user (or role) who is executing this statement. You can make the assembly owner another user by using the AUTHORIZATION clause of the CREATE SCHEMA statement.
The Database Engine also supports ALTER ASSEMBLY and DROP ASSEMBLY statements. ALTER ASSEMBLY Statement used to update an assembly to the latest version. This statement also adds or removes files associated with the corresponding assembly. DROP ASSEMBLY Statement removes the specified assembly and all associated files from the current database.
The example below shows how to create a stored procedure based on the managed code that you implemented earlier:
USE SampleDb; GO CREATE PROCEDURE CountEmployees AS EXTERNAL NAME CLRStoredProcedures.StoredProcedures.CountEmployees
The CREATE PROCEDURE statement in the example differs from the same statement in the examples earlier in that it contains EXTERNAL NAME parameter... This parameter indicates that the code is generated by the CLR. The name in this sentence has three parts:
assembly_name.class_name.method_name
assembly_name Specifies the name of the assembly.
class_name - indicates the name of the general class;
method_name - optional, specifies the name of the method that is specified inside the class.
The execution of the CountEmployees procedure is shown in the example below:
USE SampleDb; DECLARE @count INT EXECUTE @count = CountEmployees PRINT @count - Returns 7
The PRINT statement returns the current number of rows in the Employee table.
In Microsoft SQL Server, to implement and automate its own algorithms ( calculations), you can use stored procedures, so today we will 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 beginner 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 T-SQL Programmer's Way. Tutorial for the Transact-SQL language.
What are stored procedures in T-SQL?
Stored procedures Are database objects that contain an algorithm in the form of a set of SQL statements. In other words, we can say that stored procedures are programs within a database. Stored procedures are used to save reusable code on the server, for example, you have written a certain algorithm, sequential calculation or multi-step SQL statement, and in order not to execute all the instructions included in this algorithm every time, you can design it as a stored procedure. At the same time, when you create a SQL procedure, the server compiles the code, and then, each time you run this SQL procedure, the server will no longer re-compile it.
In order to run a stored procedure in SQL Server, it is necessary to write the EXECUTE command before its name, or the abbreviated EXEC command is also possible. Calling a stored procedure in a SELECT statement, for example, as a function will no longer work, i.e. procedures run separately.
In stored procedures, unlike functions, you can already perform data modification operations such as: UNSERT, UPDATE, DELETE. Also, in procedures, you can use SQL statements of almost any type, 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 modifying 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 can have input parameters and output parameters, it can return tabular data, it can return nothing, 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. the PIVOT operator. To make it easier to form queries with this operator ( as you know, the syntax of PIVOT 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 how this feature can be implemented 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 several records to it, let's say that it is a table containing a list of products with their prices.
Statement to create a table CREATE TABLE TestTable (INT IDENTITY (1,1) NOT NULL, INT NOT NULL, VARCHAR (100) NOT NULL, MONEY NULL) GO - Instruction to add data INSERT INTO TestTable (CategoryId, ProductName, Price) VALUES (1 , "Mouse", 100), (1, "Keyboard", 200), (2, "Phone", 400) GO --Select request SELECT * FROM TestTable
The data is there, now let's move on to creating stored procedures.
Creating a T-SQL Stored Procedure - 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 AS keyword and open the block of instructions with the BEGIN keyword, close this block with the END word. 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 product to our test chart. To do this, we will define three incoming parameters: @CategoryId - product category identifier, @ProductName - product name and @Price - product price, this parameter will be optional, i.e. it will not be possible to pass it to the procedure ( for example, we do not know the price yet), for this in its definition we will set the default value. These parameters are in the body of the procedure, i.e. in the BEGIN ... END block you can use the same way as ordinary variables ( as you know, variables are denoted by @). If you need to specify the output parameters, then after the name of the parameter, specify the keyword OUTPUT ( or abbreviated OUT).
In the BEGIN… END block, we will write a statement for adding data, as well as 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 input parameter, namely, the removal of extra spaces at the beginning and at the end of the text line in order to avoid situations when several spaces were accidentally inserted.
Here is the code for this procedure ( I also commented on it).
Create a procedure CREATE PROCEDURE TestProcedure (--Input parameters @CategoryId INT, @ProductName VARCHAR (100), @Price MONEY = 0) AS BEGIN --Instructions that implement your algorithm --Handle incoming parameters --Delete extra spaces at the beginning and in end of the text line SET @ProductName = LTRIM (RTRIM (@ProductName)); --Add a new INSERT INTO TestTable (CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) record - Return the data SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO
Running a T-SQL Stored Procedure - EXECUTE Command
You can start a stored procedure, as I have already noted, using the EXECUTE or EXEC command. Incoming parameters are passed to procedures by simply listing them and specifying the appropriate values after the procedure name ( for output parameters you also need to specify the OUTPUT command). However, the name of the parameters can be omitted, but in this case it is necessary to follow the sequence of specifying the values, i.e. specify values in the order in which the input parameters are defined ( this also applies to the output parameters).
Parameters that have default values do not need to be specified, these are so-called optional parameters.
Here are some different but equivalent ways to run stored procedures, in particular 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 indication EXEC TestProcedure @CategoryId = 1, @ProductName = "Test product 2", @Price = 300 --3. We call the procedure without specifying the name of the parameters EXEC TestProcedure 1, "Test Product 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 existing procedure, you just need to write ALTER PROCEDURE instead of CREATE PROCEDURE, and change everything else as needed.
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 get the resulting dataset, for this we will simply remove the SELECT statement from the stored procedure.
We change the ALTER PROCEDURE TestProcedure (--Incoming parameters @CategoryId INT, @ProductName VARCHAR (100), @Price MONEY) AS BEGIN --Instructions that implement your algorithm --Handling the input parameters - Removing extra spaces at the beginning and at the end of the text lines SET @ProductName = LTRIM (RTRIM (@ProductName)); --Add a new INSERT INTO TestTable (CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO record
Dropping a T-SQL Stored Procedure - DROP PROCEDURE Statement
If necessary, you can delete the stored procedure, this is done using the instruction DROP PROCEDURE.
For example, let's delete the test procedure we created.
DROP PROCEDURE TestProcedure
When deleting stored procedures, keep in mind that if the procedure is referenced by other procedures or SQL statements, after deleting it, they will fail because the procedure they are referring to no longer exists.
I have everything, I hope the material was interesting and useful to you, so far!
