Lesson 1: Stored Procedures . . . .144
Creating Stored Procedures 144
Commenting Code 146
Variables, Parameters, and Return Codes 146
Control Flow Constructs 149
Error Messages 152
Error Handling 153
Executing Stored Procedures 157
Module Execution Context 159
Cursors 159
Compilation and Recompilation 162
Lesson Summary 166
Lesson 2: User-Defined Functions . . . .167
System Functions 167
User-Defined Functions 168
Retrieving Data from a Function 170
Lesson Summary 174 Lesson 3: Triggers . . . 175 DML Triggers 175 DDL Triggers 176 Logon Triggers 178 Lesson Summary 182 Lesson 4: Views . . . .183 Creating a View 183
Modifying Data Through a View 184
Partitioned Views 184
Creating an Indexed View 185
Determinism 187 Query Substitution 187 Lesson Summary 189 Chapter Review . . . .190 Chapter Summary 190 Key Terms . . . .190 Case Scenario 191 Suggested Practices . . . .192
Create a Stored Procedure 192
Create a Function 192
Create a Trigger 192
Exam objectives in this chapter:
n Create and alter stored procedures.
n Create and alter user-defined functions (UDFs). n Create and alter DML triggers.
n Create and alter DDL triggers. n Create and deploy CLR-based objects. n Implement error handling.
n Manage transactions. n Create and alter views.
Lessons in this chapter:
n Lesson 1: Stored Procedures 144 n Lesson 2: User-Defined Functions 167 n Lesson 3: Triggers 175
n Lesson 4: Views 183
before You begin
To complete the lessons in this chapter, you must have: n SQL Server 2008 installed
n The AdventureWorks database installed
More info cLR ObjectS
You can create triggers, functions, and stored procedures using any supported CLR language. The creation and management of CLR objects is covered in Chapter 7, “ Extending Microsoft SQL Server Functionality with XML, Filestream, and SQLCLR.”
real World
Michael Hotek
F
or decades, companies have been creating frameworks and code generation tools that are supposed to “shield” developers from needing to understand databases or Structured Query Language (SQL). While many of these frameworks and code generation tools have allowed developers to quickly create large volumes of database access code, unless the code generated undergoes significant modification, very little will perform well enough to meet the needs of business applications.At one company where I was working on a project, one of the developers couldn’t wait to use one of these new code generators that was designed to shield the developers from needing to understand the databases they were using. Within the application, the developer wrote about four lines of code that would retrieve a single row from a table based on an input value for one of the columns of a table. This could have been done with a simple SELECT statement, which could have been encapsulated inside a stored procedure. However, what the application submitted to SQL Server created a function that contained the SELECT statement, a SELECT statement to access the function, code to drop the function at the end of the process, and 30 to 40 lines of T-SQL to perform error handling validation and ensure that the function was properly created and then dropped.
Had the developer simply written the stored procedure and then called the stored procedure in the code, he would have needed to write a total of eight lines of code, and each user request would execute a total of three statements. Using the code generated by the code generator, however, required an object to be created and then destroyed along with the execution of more than 70 lines of code for each user request.
Although the code generator did all the work so that the developer “didn’t have to worry about it,” performance comparisons showed that the automatically generated code required six times as many resources and took 245 percent longer to execute than if the developer had just written a stored procedure.
T-SQL is a very simple language to learn and has been around, with much the same syntax, for more than 50 years. Every time I hear about the next code generation tool that someone created to free developers from needing to understand how to access a database, the thing that always comes to mind is reinventing the wheel. T-SQL is a much simpler language than any of the development platforms in use. Just like the wheel, which has been around for more than 5,000 years with very little in the way of innovation, SQL will long outlive any application development language you will ever use, while at the same time being the most efficient way to access a relational database.
Lesson 1: Stored Procedures
Stored procedures provide an abstraction layer that shields applications from the underlying database structure. As the backbone of any SQL Server application, stored procedures allow you to make changes to the database structure and manage performance without needing to rewrite applications or deploy application updates. In this lesson, you learn how to create stored procedures to provide the programmatic interface necessary for writing easily maintained and efficient database applications.
After this lesson, you will be able to:
n Create and alter stored procedures. n Implement error handling.
n Manage transactions.
Estimated lesson time: 20 minutes
Creating Stored Procedures
A stored procedure is one or more statements that has been given a name and stored within a database. Almost any command within the T-SQL language can be included in a stored procedure, making procedures suitable for applications and for performing myriad administrative actions. The only commands that cannot be used in a stored procedure are the following:
n USE <database name> n SET SHOWPLAN_TEXT n SET SHOWPLAN_ALL n SET PARSEONLY n SET SHOWPLAN_XML n CREATE AGGREGATE n CREATE RULE n CREATE DEFAULT n CREATE SCHEMA
n CREATE FUNCTION or ALTER FUNCTION n CREATE TRIGGER or ALTER TRIGGER n CREATE PROCEDURE or ALTER PROCEDURE n CREATE VIEW or ALTER VIEW
The first time that a stored procedure is accessed, SQL Server generates compile and execution plans that are stored in the query cache and reused for subsequent executions. Therefore, you can receive a slight performance benefit when using a stored procedure by avoiding the need to parse, compile, and generate a query plan on subsequent executions of a stored procedure. However, the main purpose of a stored procedure is to provide a security layer and an API to your databases that isolate applications from changes to the database structure.
The generic syntax to create a stored procedure is
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] [ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY] ] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ] [ FOR REPLICATION ]
AS { <sql_statement> [;][ ...n ] | <method_specifier> } [;] <procedure_option> ::=
[ ENCRYPTION ] [ RECOMPILE ] [ EXECUTE AS Clause ]
When you specify the ENCRYPTION option, available for triggers, functions, procedures, and views, SQL Server applies a bitwise OR to the code in the object. The ENCRYPTION option is a carryover from early versions of SQL Server and the option causes quite a bit of confusion. When you specify the ENCRYPTION option, you are not applying an encryption routine to hide your code. The algorithm that SQL Server uses is a simple bitwise OR that only obfuscates the code in the object. If you look at the definition of the object, it appears as unintelligible text. However, a very simple, publicly available routine reverses the obfuscation. SQL Server does not allow you to hide the code in triggers, functions, views, and stored procedures, and anyone with VIEW DEFINITION authority on the object can retrieve the code you have written.
Caution encRYPtinG MODULeS
SQL Server is not a digital rights management (DRM) system. The text of the module is not encrypted; rather, it is obfuscated. Any user with access to database metadata can reverse-engineer the obfuscated text easily. The ENCRYPTION option is not meant to prevent a user from reading the code within your modules.
If you want to modify the contents of a stored procedure or the procedure options, you can use the ALTER PROCEDURE statement.
What sets a stored procedure apart from a simple batch of T-SQL are all the code structures that can be employed, such as variables, parameterization, error handling, and control flow constructs.