SQL Server 2008 : Programming Objects - Implementing Stored Procedures

10/10/2010 6:24:15 PM
The favorite programming objects used by the database developers so far, stored procedures, are compiled SQL routines that are stored in a database and act like an independent program within SQL Server when called. Stored procedures are essential for the security and accessibility of data for applications because they allow you to use them as an interface from your database to your application.

An interface between a database and an application enables you to control the user’s access to your database and objects, as well as to protect and isolate your code, resulting in high security and easy maintenance. For example, you can create a stored procedure that inserts the client’s data into the table clients. The application will only call the stored procedure responsible for the insert: developers don’t need to know the T-SQL code behind this operation, protecting your database and code. Another advantage of this interface is that you will maintain only the code of the stored procedure, regardless of how many times an application refers to your stored procedure: no changes will be necessary in the application if you edit the procedure’s code. In addition, you can grant a user permission to execute a procedure, without having to give permission to realize the actions within the procedure, thereby increasing the security of your database. In other words, you can grant a user permission to execute a procedure that selects data from a table, without granting him the SELECT permission on that table: users can execute the procedure but cannot execute a SELECT statement in this table.

Creating Stored Procedures

You can use stored procedures to modify data, return a scalar value, or return a result set. Unlike functions, however, the return in stored procedures is not mandatory. Also, stored procedures support almost all commands of SQL Server inside their routines. To create a procedure, you will use the CREATE PROCEDURE statement. The general syntax for creating a stored procedure is the following:

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
] [,... n]
[ WITH <procedure_option> [,... n ] ]
AS { <sql_statement> [;][ ...n ] }
<procedure_option> ::=
[ EXECUTE AS Clause ]
<sql_statement> ::=
{ [ BEGIN ] statements [ END ] }

When executing procedure statements, you can use only the word PROC as an alias of PROCEDURE. Like functions, the structure of the CREATE PROCEDUREheader and body. The header starts with the procedure name, which must have a unique name that conforms to the rules for object in SQL Server. In the header you can define the input parameter names and data types, which are used within the procedure as local variables. The user must supply the value of each declared parameter when the procedure is called, unless a default value is defined or the value is set to equal another parameter. You can also indicate that a parameter cannot be updated or modified within the definition of your procedure, using the READONLY option. statement has two parts:

You can also define parameters as the output of a procedure, although you don’t have to define them. The OUTPUT option indicates that the parameter is an output parameter. You can create as many output parameters as your application needs. OUTPUT parameters can be any data type, except text, ntext, and image data types.

The WITH clause offers three options for your procedures: ENCRYPTION, RECOMPILE, and EXECUTE AS. You specify the ENCRYPTION option when you want to encrypt the procedure definition. The RECOMPILE option indicates that SQL Server does not cache a plan for this procedure, and it will be compiled each time the procedure is executed. The EXECUTE AS option specifies the security context under which the procedure is executed.

Configuring & Implementing...: When Do You Use the RECOMPILE Statement?

When a stored procedure is executed, SQL Server compiles the procedure and stores it in the query cache. The compilation process creates a query plan and an execution plan, allowing SQL Server to reuse them in the subsequent executions, decreasing the answer time of a procedure.

When you use the RECOMPILE option, SQL Server will discard the stored query plan and create a new one in each procedure execution. This option is useful when you make changes that bring benefits to the procedure performance, such as the addition of a new index in a table that the routine within the procedure will use. You should use the RECOMPILE option in the ALTER PROCEDURE statement; you should avoid using it at the creation process.

The body of the CREATE PROCEDURE statement is the main part of this statement. This is where you define the routine of actions that the procedure will execute when called. This part is delimited by a BEGIN...END statement, where you place all the code. You can execute any T-SQL command, except SET SHOWPLAN_TEXT, SET SHOWPLAN_ALL and USE statements.

Example: Creating and Consuming a Stored Procedure

The AdventureWorks2008 database has a table called ProductCategory, which shows the categories available for products. Figure 1 shows the table structure and its columns. All the columns, except the Name, are filled in automatically.

Figure 1. The ProductCategory Table Structure

As a database developer, you decide to create a stored procedure to insert new categories into the table ProductCategory for your application. With this object, you intend to ease the maintenance of your code and secure your code and data from developers and users. Figure 2 shows how you can create a stored procedure that executes an INSERT statement in the desired table. When you create a procedure, it is stored in the active database, inside the folder Stored Procedures, set under the Programmability folder.

Figure 2. The Creation Syntax of the Stored Procedure

As you can see, the new procedure has the name uspNewProductCategory, the variable @Name as an input parameter, the ENCRYPTION option, and the routine is an INSERT statement. Once created, you can execute this procedure using either EXEC or EXECUTE statements, or you can just write the name of the procedure. Also, you can pass the input parameters in different ways. Figure 3 shows the ways to pass parameter values and a SELECT that returns the inserted category.

Figure 3. Executing the Procedure and Viewing the Inserted Value

Managing Stored Procedures

As a database developer, you need to learn some basics about managing stored procedure objects, such as altering a procedure and viewing its definition. Basically, there are two ways to manage these procedures: using the SQL Server Management Studio (SSMS) or using Transact-SQL statements.

In SQL Server Management Studio, the procedures are stored under Programmability | Stored Procedures of the selected database. You can see the properties of a procedure by right-clicking it and choosing Properties to open a new window. In the Stored Procedure Properties window, you can see some information about the selected procedure, such as if its definition is encrypted (see Figure 4).

Figure 4. The Stored Procedure Properties Window

You can alter the code of the procedure by right-clicking it and choosing Modify to open a new query tab. This tab shows the actual definition of the stored procedure and allows you to make changes at its code. After you complete the changes, commit them by clicking on the Execute button. Also, to drop a procedure, right-click the desired object and choose Delete. You can see the objects that depend on a procedure and the objects that a procedure depends on by right-clicking it and choosing the View Dependencies option.

You can also manage procedures using Transact-SQL statements. You can alter and remove a procedure using the ALTER PROCEDURE and the DROP PROCEDURE statements. The ALTER PROCEDURE alters an existing stored procedure that you previously created. Its syntax and options are the same as the CREATE PROCEDURE statement. The DROP PROCEDURE removes one or more procedures from the current database. The syntax is as follow:

DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [,...n ]

You can also use system stored procedures and catalog views to provide information about stored procedures and their definitions. Some of the most common system procedures that you can use to retrieve procedure definition and properties are the sp_help and sp_helptext. The following example shows you how to retrieve the definition of and information on the stored procedure uspNewProductCategory, created in the example:

--View the definition
sp_helptext uspNewProductCategory
--View the information
sp_help uspNewProductCategory

Managing Stored Procedure Security

The stored procedure, as an object, allows you to grant users permission to use it. However, procedures have a security mechanism called delegation, which acts in a special way about the permissions of objects within the procedure and the stored procedure itself. When you grant a user the EXECUTE permission, SQL Server automatically delegates your permissions to the objects and statements referenced inside the routine, making the execution of the procedure possible to this user. The user receives these permissions only when executing the procedure, and these permissions don’t affect the permissions that you set directly in these objects.

Let’s take, for instance, the uspNewProductCategory that you created in the example. This procedure inserts rows into the ProductCategory table. You have the INSERT permission on the table and have also created the procedure. Let’s say that users of your application have no permission on the ProductCategory table but need to insert new categories inside the table. So, when you grant the EXECUTE permission to such users, they can execute this procedure normally because the insert permissions necessary to execute the routine are delegated to them. However, they cannot execute an INSERT statement directly, once they have no permission on ProductCategory.

The security delegation provided by stored procedures gives you a powerful and secure mechanism, enabling you to create an interface of stored procedures that applications will use. With this interface, users will not access data and tables directly, unless they use the stored procedures you have created and provided. Also, these delegated permissions are still dependent on the code within the procedure, allowing you to validate the input parameters, avoid attacks such as SQL Injection, and execute specific tasks according to the user who is calling the procedure.

Besides EXECUTE, stored procedures have other permissions, focused on their administration (see Table 1).

Table 1. Stored Procedure Permissions
EXECUTEExecute a stored procedure
VIEW DEFINITIONView the information and definition of a procedure
ALTERAlter the properties of a procedure
CONTROLAssign the permission of a procedure to other users
TAKE OWNERSHIPTake ownership of a procedure

Like functions, you can set the user’s permission using the GRANT, REVOKE or DENY T-SQL statements. The following two examples show how to grant a user permission on a stored procedure.

--Grant EXECUTE to a user
GRANT EXECUTE ON [dbo].[uspNewProductCategory] TO Priscila

Adjusting Transaction Isolation Levels

As you have seen, stored procedures have an essential role in the development of databases, creating a secure interface between your database and your application. Most of the procedures that compose this interface execute data manipulation commands: SELECT, INSERT, UPDATE, DELETE, and MERGE statements.

When you are manipulating data, SQL Server provides isolation levels for the transactions that are being executed. This isolation defines how these transactions will access the resources and how SQL Server will solve concurrency between transactions. The transaction isolation levels control how locks will be taken when data is read, how long the read locks will be held, and how a read operation will reference rows modified by another transaction.

The isolation property is one of the four properties—atomicity, consistency, isolation, and durability (ACID)—that a logical unit of work must display to qualify as a transaction. It is the ability to shield transactions from the effects of updates performed by other concurrent transactions. The level of isolation can actually be customized for each transaction.

Every transaction that you execute always gets an exclusive lock on any data it modifies, and it holds that lock until the transaction is completed. For read operations, transaction isolation levels primarily define the level of protection from the effects of modifications made by other transactions.

If you define a lower isolation level for a transaction, it will increase the ability of many users to access your data at the same time, but it will increase the number of concurrency effects users might encounter, such as dirty reads or lost updates. At the same time, a higher isolation level reduces the types of concurrency effects your users may encounter, but requires more system resources and increases the chances that one transaction will block another.

Concurrency causes three basic side effects: dirty read, nonrepeatable read, and phantom read. A dirty read occurs when a second transaction selects a row that is being updated by another transaction. The second transaction is reading data that has not been committed yet and may be changed by the transaction updating the row. A non repeatable read occurs when a second transaction accesses the same row several times and reads different data each time. A phantom read occurs when an insert or a delete action is performed against a row that belongs to a range of rows being read by a transaction. The transaction’s first read of the range of rows shows a row that no longer exists in the second or succeeding read as a result of a deletion by a different transaction.

An example of a dirty read is when you are making changes to a table. During the changes, a user creates a new table that is a copy of the table that includes all the changes made to that point. You then decide that the changes that have been made so far are wrong and you remove the changes. The table created by the user contains changes that no longer exist and should be treated as if they never existed. An example of a nonrepeatable read is when you read the same table twice, but between each reading a user changes the data. When you read the table for the second time, it has changed. The original read was not repeatable. An example of a phantom read is when you propose changes to a table, but when the changes are incorporated into the table, you discover that a user has made other changes to the table without your consent.

As a database developer, you must choose the appropriate isolation level of your transactions and routines within your stored procedures, considering the data integrity requirements of your application and the overhead of each isolation level. The highest isolation level, SERIALIZABLE, guarantees that your transaction will retrieve exactly the same data every time it repeats a read operation, but it does this by performing a level of locking that is likely to impact other users in multiuser systems. The lowest isolation level, READ UNCOMMITTED, may retrieve data that has been modified but has not been committed by other transactions. All of the concurrency side effects can occur in read uncommitted, but because there is no read locking or versioning, overhead is minimized in your environment.

SQL Server supports the transaction isolation levels defined in SQL-92. The transaction isolation levels are as follows:

  • READ UNCOMMITTED Specifies that statements can read rows that have been modified by other transactions but not yet committed.

  • READ COMMITTED Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

  • REPEATABLE READ Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

  • SNAPSHOT Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction got a snapshot of the committed data as it existed at the start of the transaction.

  • SERIALIZABLE Specifies that the statements cannot read data that has been modified but not yet committed by other transactions; no other transactions can modify data that has been read by the current transaction until the current transaction completes; and other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Table 2 shows the concurrency side effects enabled by the different isolation levels of a transaction:

Table 2. Isolation Levels and Their Side Effects
Isolation LevelDirty ReadNonrepeatable ReadPhantom

Test Day Tip

On the day of your exam, review the isolation levels and their side effects table so that you will know the relationship between levels and effects. You will see that the questions about isolation will be very easy.

You can define the isolation level of a transaction using the SET TRANSACTION ISOLATION LEVEL statement. You use this command before the transactions that you will execute. The syntax is as follows:

[ ; ]

1. Creating Stored Procedures

You will create a stored procedure that will update a product category, according to a given ID. You then will alter this procedure, creating an output for it. At the end, you will review the procedure definition.

  1. Launch SQL Server Management Studio (SSMS), connect to the instance, open a new query window, and change the context to the AdventureWorks2008 database.

  2. Execute the following statement to view the rows of the ProductCategory table.

    SELECT * FROM [Production].[ProductCategory]
  3. Create the stored procedure uspUpdateProductCategory by executing the following code:

    CREATE PROCEDURE [dbo].[uspUpdateProductCategory]
    @ProductCategoryID INT,
    @Name NVARCHAR(50)
    UPDATE [Production].[ProductCategory] SET Name = @Name
    WHERE ProductCategoryID = @ProductCategoryID
  4. Update the product category using the EXECUTE statement, as follows:

    EXECUTE uspUpdateProductCategory 4, 'Others'
  5. Now, let’s alter the created procedure, adding a new parameter as an output. This output will be a message to the user about the success or failure of the procedure. Execute the following code:

    ALTER PROCEDURE [dbo].[uspUpdateProductCategory]
    @ProductCategoryID INT,
    @Name NVARCHAR(50),
    @Message NVARCHAR(20) = 'None' OUTPUT
    UPDATE [Production].[ProductCategory] SET Name = @Name
    WHERE ProductCategoryID = @ProductCategoryID
    IF (@@ERROR = 1)
    SET @Message = 'An error occurred!'

    SET @Message = 'Category updated'

  6. Update the product category using the EXECUTE statement, as follows:

    EXECUTE uspUpdateProductCategory 4, 'Acessories', @Var OUT
    PRINT @Var
  7. Now create a new user and grant him or her the EXECUTE permission, as in the following code:

    GRANT EXECUTE ON [dbo].[uspUpdateProductCategory] TO Herleson
  8. To finish, view the definition of the uspUpdateProductCategory function using the following code:

    sp_helptext uspUpdateProductCategory
Top 10 Video Game
-   Minecraft Mods - MAD PACK #10 'NETHER DOOM!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #9 'KING SLIME!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #2 'LAVA LOBBERS!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #3 'OBSIDIAN LONGSWORD!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Total War: Warhammer [PC] Demigryph Trailer
-   Minecraft | MINIONS MOVIE MOD! (Despicable Me, Minions Movie)
-   Minecraft | Crazy Craft 3.0 - Ep 3! "TITANS ATTACK"
-   Minecraft | Crazy Craft 3.0 - Ep 2! "THIEVING FROM THE CRAZIES"
-   Minecraft | MORPH HIDE AND SEEK - Minions Despicable Me Mod
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 92 "IS JOE DEAD?!"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 93 "JEDI STRIKE BACK"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 94 "TATOOINE PLANET DESTRUCTION"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 95 "TATOOINE CAPTIVES"
-   Hitman [PS4/XOne/PC] Alpha Gameplay Trailer
-   Satellite Reign [PC] Release Date Trailer