C H A P T E R 9
Building a View
A view is a virtual table that, in itself, doesn’t contain any data or information. All it contains is the
query that the user defines when creating the view. You can think of a view as a query against one or
more tables that is stored within the database. Views are used as a security measure by restricting
users to certain columns or rows; as a method of joining data from multiple tables and presenting it
as if it resides in one table; and by returning summary data instead of detailed data. Another use for
a view is to provide a method of accessing the underlying data in a manner that provides the end
user with a business layout. For example, you will see within this chapter the building of a view that
shows customer details along with enriched transaction details, thus making it easier for anyone interrogating
your data who has no knowledge of the underlying data model to access useful information.
Building a simple view is a straightforward process and can be completed in SQL Server
Management Studio or a Query Editor pane using T-SQL within SQL Server. Each of these tools has
two options to build a view, and this chapter will cover all four options so that you become conversant
with building a view no matter which tool is currently at hand.
To give things a bit more bite in this chapter, a query within a query, known as a subquery, will
also be demonstrated, along with how to build a subquery to create a column.
Finally, placing an index on a view can speed up data retrieval, but it also can give performance
problems as well. An index on a view is not quite as straightforward as building an index on a table.
The aim of this chapter is to
• Make you aware of what a view is.
• Inform you as to how views can improve a database’s security.
• Show how to encrypt your view so that the source tables accessed cannot be seen.
• Demonstrate building a view using
• Management Studio View Designer
• Management Studio Create a View Wizard
• A Query Editor pane and T-SQL
• Show how to join two tables within a view.
• Demonstrate subqueries within a view.
• Build an index on a view and give the reasons as to why you would or would not do this.
Why a View?
There will be times when you’ll want to group together data from more than one table, or perhaps
only allow users to see specific information from a particular table, where some of the columns may
308 CHAPTER 9 ■ B U ILDING A V IEW
contain sensitive or even irrelevant data. A view can take one or more columns from one or more
tables and present this information to a user, without the user accessing the actual underlying
tables. A view protects the data layer while allowing access to the data. All of these scenarios can be
seen as the basis and reason for building a view rather than another method of data extraction. If you
are familiar with Microsoft Access, views are similar to Access queries. Because a view represents
data as if it were another table—a virtual table in fact—it is also possible to create a view of a view.
Let’s take a look at how a view works. As you know, we have a customer table that holds information
about our customers such as their first name, last name, account number, and balances.
There will be times when you’ll want your users to have access to only the first and last names, but
not to the other sensitive data. This is where a view comes into play. You would create a view that
returns only a customer’s first and last name but no other information.
Creating a view can give a user enough information to satisfy a query he or she may have about
data within a database without that user having to know any T-SQL commands. A view actually
stores the query that creates it, and when you execute the view, the underlying query is the code that
is being executed. The underlying code can be as complex as required, therefore leaving the end user
with a simple SELECT * command to run with perhaps a small amount of filtering via a simple WHERE
statement.
From a view, in addition to retrieving data, you can also modify the data that is being displayed,
delete data, and in some situations, insert new data. There are several rules and limitations for deleting,
modifying, and inserting data from multitable views, some of which will be covered in the “Indexing
a View” section later in the chapter.
However, a view is not a tool for processing data using T-SQL commands, like a stored procedure
is. A view is only able to hold one query at a time. Therefore, a view is more like a query than a
stored procedure. Just as with a stored procedure or a query within a Query Editor pane, you can
include tables from databases that are running on different servers. Providing the user ID has the
necessary security credentials, it is possible to include tables from several databases.
So to summarize, a view is a virtual table created by a stored SQL statement that can span
multiple tables. Views can be used as a method of security within your database, and they provide a
simpler front end to a user querying the data.
Later in the chapter, you will see how to build a view and how all of these ideas are put into practice.
Before we get to that, let’s look in more depth at how a view can be used as a security vehicle.
Using Views for Security
Security is always an issue when building your database. So far, the book has covered the different
database-provided roles, when to use them, how to set up different types of roles, and how useful
they are. You also saw in Chapter 8 how to assign a user only SELECT rights and not any other rights
such as INSERT. By restricting all users from accessing or modifying the data in the tables, you will
then force everyone to use views and stored procedures to complete any data task. (There will be
more on stored procedures in the next chapter.)
However, by taking a view on the data and assigning which role can have select access, update
access, and so on, you are protecting not only the underlying tables, but also particular columns of
data. This is all covered in the discussions involving security in this chapter.
Security encompasses not only the protection of data, but also the protection of your system. At
some point as a developer, you will build a view and then someone else will come along and remove
or alter a column from an underlying table that was used in the view. This causes problems; however,
this chapter will show you how to get around this problem and secure the build of a view so that this
sort of thing doesn’t happen.
Imagine that you have a table holding specific security-sensitive information alongside general
information—an example would be where you perhaps work for the licensing agency for driver’s
licenses and alongside the name and address, there is a column to define the number of fines that
CHAPTER 9 ■ B U ILDING A V IEW 309
have had to be paid. As you can see, this is information that should not be viewed by all employees
within the organization. So, what do you do?
The simplest answer is to create a view on the data where you exclude the columns holding the
sensitive data. In this way, you can restrict access on the table to the bare minimum of roles or logins,
and leave either a view or a stored procedure as the only method of data retrieval allowed. This way,
the information returned is restricted to only those columns that a general user is allowed to see.
It is also possible to place a WHERE statement within a view to restrict the rows returned. This
could be useful when you don’t wish all employee salaries to be listed: perhaps excluding the salaries
of the top executives would be advised!
All these methods give you, as a developer, a method for protecting the physical data lying in the
base tables behind the views. Combine this with what you learned about roles and restricting table
access, and you can really tighten the security surrounding your data. With more and more companies
embracing initiatives like Sarbanes-Oxley, where security should be so tight a company can be
defined as having secure data, views are a great method of getting toward this goal.
Another method of securing views is to encrypt the view definition, which we explore next.
Encrypting View Definitions
As well as restricting access to certain tables or columns within a database, views also give the option
of encrypting the SQL query that is used to retrieve the data. Once a view is built and you are happy
that it is functioning correctly, you would release that view to production; it is at this point that you
would add the final area of security—you would encrypt the view.
The most common situation where you will find views encrypted is when the information
returned by the view is of a privileged nature. To expand further, not only are you using a view to
return specific information, you also don’t wish anyone to see how that information was returned,
for whatever reason. You would therefore encrypt the SQL code that makes up the view, which would
mean that how the information was being returned would not be visible.
There is a downside to encrypting a view: once the process of encryption is completed, it is difficult
to get back the details of the view. There are tools on the Internet that can decrypt an encrypted
view. When you encrypt a view, the view definition is not processed via encryption algorithms, but
is merely obfuscated—in other words, changed so that prying eyes cannot see the code. These tools
can return the obfuscation back to the original code. Therefore, if you need to modify the view, you
will find that it is awkward. Not only will you have to use a tool, but you will also have to delete the
view and re-create it, as it will not be editable. So, if you build a view and encrypt it, you should make
sure that you keep a copy of the source somewhere. This is why it is recommended that encrypted
views should be used with care and really should only be placed in production, or at worst, in user testing.
Always keep a copy of the original view, before encryption, in the company’s source-control
system—for example, Visual SourceSafe—and make sure that regular backups are available.
Now that we have touched upon the security issues behind views, it is time to start creating
views for the database solution that we are building together.
Creating a View: SQL Server Management Studio
The first task for us is to create a view using SQL Server Management Studio. This is perhaps the
simplest solution, as it allows us to use drag-and-drop to build the view. This may be the slowest
method for creating a new view, but it does give us the greatest visual flexibility for building the view,
and this may also be the best method for dealing with views that already exist and require only minor
modifications.
310 CHAPTER 9 ■ B U ILDING A V IEW
The View Designer can aid you in the design of a view or the modification of any view already
built. For example, it can assist if you are trying to build a complex view from a simple view, or it can
even be used as a trial-and-error tool while you are gaining your T-SQL knowledge.
However, enough of the background—let’s take a look at how the View Designer works. In this
example, we will be making a view of ShareDetails.Shares.
Try It Out: Creating a View in SQL Server Management Studio
1. Ensure that SQL Server Management Studio is running and that the ApressFinancial database is expanded.
2. Find the Views node, and right-click it—this brings up the pop-up menu shown in Figure 9-1; from there, select
New View.
Figure 9-1. Creating a new view
3. The next screen you will see is the View Designer, with a modal dialog box on top presenting a list of tables that
you can add to make the view. The background is pretty empty at the moment (move the dialog box around if you
need to). It is within the View Designer that you will see all of the information required to build a view. There are
no tables in the view at this time, so there is nothing for the View Designer to show. For those of you who are
familiar with Access, you will see that the View Designer is similar to the Access Query Designer, only a bit more
sophisticated! We want to add our table, so moving back to the modal dialog box, shown in Figure 9-2, select
Shares (ShareDetails), click Add, and then click Close to remove the dialog box.
Figure 9-2. Selecting the tables for your view
CHAPTER 9 ■ B U ILDING A V IEW 311
4. Take a moment to see how the View Designer has changed, as illustrated in Figure 9-3. Notice that the background
Query Designer area has been altered, the ShareDetails.Shares table has been added, and the
beginnings of a SELECT statement now appear about two thirds of the way down the screen. By adding a table,
the Query Designer is making a start to the view you wish to build.
Figure 9-3. The basic view
5. There are four separate parts to the View Designer, each of which can be switched on or off for viewing via the
toolbar buttons on top. Take a look at these toolbar buttons, as shown close up in Figure 9-4. The first button
brings up the top pane—the diagram pane—where you can see the tables involved in the view and can access
them via the leftmost toolbar button. The next button accesses the criteria pane, where you can filter the information
you want to display. The third button accesses the SQL pane, and the fourth button accesses the results
pane. As with Query Editor, here you also have the ability to execute a query through the execute button (the one
with the red exclamation point). The final button relates to verifying the T-SQL. When building the view, although
the T-SQL is created as you build up the view, you can alter the T-SQL code, and this button will verify any changes.
Figure 9-4. View toolbar buttons
6. We will see the ShareDetails.Shares table listed in the top part of the Query Designer (the diagram pane)
with no check marks against any of the column names, indicating that there are not yet any columns within the
view. What we want is a view that will display the share description, the stock market ticker ID, and the current
price. If we wanted all the columns displayed, we could click the check box next to * (All Columns), but for
our example, just place checks against the last three columns, as shown in Figure 9-5. Notice as you check the
boxes how the two areas below the table pane alter. The middle grid pane lists all the columns selected and gives
you options for sorting and giving the column an alias name. The bottom part is the underlying query of the
columns selected. The finished designer will look as shown in Figure 9-5.
312 CHAPTER 9 ■ B U ILDING A V IEW
Figure 9-5. Our view with the columns selected
7. We are going to change the details in the column grid now to enforce sorting criteria and to give the column
aliases. This means that if a user just does SELECT * from the view, then he or she will receive the data in the
order defined by the view’s query by default. It also means that some of the column names will have been altered
from those of the underlying table. We want to ensure that the shares come out from the view in ascending name
order. Move to the Sort Type column and click in the row that corresponds to ShareDesc. Select Ascending, as
shown in Figure 9-6.
Figure 9-6. Placing an order on the data
8. In the next column, Sort Order, if we were defining more than one column to sort, we would define the order to
sort the columns in. Select the value 1 in this value. However, we still need to add the aliases, which are found
in the second column of the grid. Notice the third column, CurrentPrice. To make this column more user
friendly, we make the name Latest Price, with a space. When we type this and tab out of the column, it
becomes [Latest Price], as you see in Figure 9-7; SQL Server places the square brackets around the name
for us because of the space.
Figure 9-7. Alias with identifier
CHAPTER 9 ■ B U ILDING A V IEW 313
9. Scrolling to the right of the screen would allow us to define a filter for the view as well. This is ideal if we want
to restrict what a user can see. Although sort orders can be changed by the T-SQL that calls the view, filters
placed within the view cannot return more data than the view allows. So going back to our salary example mentioned
earlier, this would be where we would restrict users to not seeing the MD’s salary. In our example, we will
only list those shares that have a current price—in other words, where CurrentPrice is greater than 0, as
shown in Figure 9-8.
Figure 9-8. Filtering the data
10. Notice the Query Editor pane, which now has the filter within it as well as the sorting order. Also take a look at
the diagram pane and how the table display has been altered, as you see in Figure 9-9.
Figure 9-9. The table with the view options applied
11. Moving back to the T-SQL in the SQL pane, what about the TOP (100) PERCENT clause? Where did that come
from? First of all, if you specify an order in a view, then by default SQL Server will place the TOP (100) PERCENT
clause within the SQL, just as you saw in Chapter 8. It can be used if the table is very large and you don’t want
to allow users to return all the data on a production system, as it would tie up resources. You can also remove
that clause from the Query Editor pane if you want; this will unlink your query from the designer and the Properties
window, but you would also need to remove the ORDER BY. The ORDER BY is only here for the TOP clause,
and the data can be returned after the TOP number has been chosen by SQL Server in random order. If the user
of the view required a specific order, then an ORDER BY would be required when using the view. A final point to
notice is how the column aliases are defined. The physical column is named followed by AS and then the alias.
■Note The AS when defining aliases is optional.
SELECT TOP (100) PERCENT
ShareDesc AS Description,
ShareTickerId AS Ticker,
CurrentPrice AS [Latest Price]
FROM ShareDetails.Shares
WHERE (CurrentPrice > 0)
ORDER BY ShareDesc
314 CHAPTER 9 ■ B U ILDING A V IEW
12. If you wish to remove the TOP clause, it would be better to do this within the Properties window, shown in Figure 9-10,
usually found on the bottom right of SQL Server Management Studio; however, you would also need to remove
the sorting. If it’s not there, it can be found by selecting View ➤ Toolbox from the menu or by pressing F4. Within
the properties, we can give the view a description—very useful—but we can also remove the TOP clause by
setting Top Specification to No. We can also define whether this view is read-only by setting Update Specification
to No.
Figure 9-10. The properties of a view
13. We do need to change some of the properties in the view definition, as shown in Figure 9-11. First of all, it is
better to give the view a description. Also, like a table, a view should belong to a schema. This can be from an
existing schema, or if you have a view traversing more than one table, you may have a schema to cater to that
scenario. In our case, it fits into the ShareDetails schema.
14. We think the view is complete, but we need to test it out. By executing the query with the execute button (the one
sporting the red exclamation point), we will see the results in the results pane.
15. Now that the view is complete, it is time to save it to the database. Clicking the close button will bring up a dialog
box asking whether you want to save the view. Click Yes to bring up a dialog box in which you give the view a
name. You may find while starting out that there is a benefit to prefixing the name of the view with something like
vw_ so that you know when looking at the object that it’s a view. Many organizations do use this naming standard;
however, it is not compulsory, and SQL Server Management Studio makes it clear what each object is. The
naming standard comes from a time when tools did not make it clear what object belonged to which group of
object types. Once you have the name you wish, as shown in Figure 9-12, click OK.
CHAPTER 9 ■ B U ILDING A V IEW 315
Figure 9-11. Populated properties of a view
Figure 9-12. Naming the view
16. This will bring us back to SQL Server Management Studio, where we will see the view saved (see Figure 9-13).
Figure 9-13. Finding a view in Object Explorer
We have now created our first view on the database. However, this method of building a view could be seen as a bit slow
and cumbersome for something so simple. What if we wanted to combine two tables, or a view and another table?
Creating a View Using a View
Creating a view that uses another view is as straightforward as building a view with a table. The
downside of building a view with a view is that it cannot be indexed for faster execution. Therefore,
depending on what the T-SQL of the final view is, data retrieval may not be as fast as it could be with
316 CHAPTER 9 ■ B U ILDING A V IEW
an index. Also, by having a view within a view, you are adding increased complexity when debugging
or profiling performance. Therefore, consider including the T-SQL from the selected view in this
new view.
In this example, we will build a view of share prices using the vw_CurrentShares view created
previously. In reality, we would use the ShareDetails.Shares table along with ShareDetails.
SharesPrices for the reasons just discussed.
Try It Out: Creating a View with a View
1. From SQL Server Management Studio Object Explorer, find Views, right-click, and select New View. The Add Table
dialog box comes up as before (see Figure 9-14). From the Tables tab, select SharePrices(ShareDetails).
Figure 9-14. Add a table.
2. Move to the Views tab; there should only be one view, shown in Figure 9-15, as that is all we have created. Select
the view, click Add, and then click Close.
Figure 9-15. Adding a view
CHAPTER 9 ■ B U ILDING A V IEW 317
3. The View Designer will now look similar to Figure 9-16, with two tables and the SQL showing a new type of join,
a CROSS JOIN.
■Note A CROSS JOIN will take every row in one table and join it with every row in the second table. We look at
these in Chapter 12.
Figure 9-16. With more than one object, how the basic view looks
4. We want to place an INNER JOIN between the table and the view where for each share we get all the share
prices only. At this moment in time, we cannot do this, as vw_CurrentShares does not have a share ID column.
We therefore have to modify the vw_CurrentShares view. Keep what you have built in the View Designer, and
move back to the Object Explorer. Find vw_CurrentShares, right-click, and this time select Design, as shown
in Figure 9-17.
Figure 9-17. Modifying a view for a join
5. From the View Designer, click the ShareId column, as shown in Figure 9-18. This will then include the ShareId
column in the view as the last column. You can use the criteria pane to move this column if you wish.
Figure 9-18. Selecting the column
318 CHAPTER 9 ■ B U ILDING A V IEW
6. Close this dialog box, which will bring up the Save Changes dialog box, as shown in Figure 9-19. Click Yes to save
the changes.
Figure 9-19. Saving the modifications
7. We can now move back to our original View Designer, and you can now see the new column in the view, as
shown in Figure 9-20; there should be no need to refresh the screen.
Figure 9-20. The view with the “new” column
8. It is very easy to link the two tables together by dragging a column from one table to a column in another table.
This is very similar to how the relationships are built in the Database Designer, as we saw earlier in the book.
First of all, click the ShareId column in the vw_CurrentShares view. Keeping the mouse button down, drag
the mouse pointer from the vw_CurrentShares view over to the ShareId column in the ShareDetails.
SharePrices table and then release it. The View Designer should now look like Figure 9-21. We have not really
created a relationship in the truest sense of the word—this is simply the relationship between the columns for
the purpose of this query. We can see one gray line, which shows which fields are used for the join.
Figure 9-21. The view with the JOIN completed
CHAPTER 9 ■ B U ILDING A V IEW 319
9. Select Price and PriceDate from the ShareDetails.SharePrices table and ShareDesc from the
vw_Shares view, as shown in Figure 9-22. ShareId would already be selected in the SharePrices table
from the drag-and-drop join from the previous step.
Figure 9-22. How the view with a JOIN looks
10. The final part to this view creation is to build the sort orders. We want the result to be in the order of ascending
description, but we want the most recent price first and the first price last. Figure 9-23 shows the criteria pane
with these options.
Figure 9-23. Sorting with ascending and descending items
11. Moving to the SQL pane, note the code shows the columns, as well as the INNER JOIN of the two ShareId
columns, and finally the ordering of the data for the TOP clause.
SELECT TOP (100) PERCENT
ShareDetails.SharePrices.Price, ShareDetails.SharePrices.PriceDate,
ShareDetails.vw_Shares.Description
FROM ShareDetails.SharePrices INNER JOIN ShareDetails.vw_Shares ON
ShareDetails.SharePrices.ShareId = ShareDetails.vw_Shares.ShareId
ORDER BY ShareDetails.vw_Shares.Description,
ShareDetails.SharePrices.PriceDate DESC
320 CHAPTER 9 ■ B U ILDING A V IEW
12. Before we can execute to test the view, we need to add some data to the ShareDetails.SharePrices
details. Just because we are completing one action doesn’t preclude us from performing another action within
another Query Editor window. Click the New Query button on the toolbar if it is not visible, and then from the
menu select File ➤ New ➤ Query with Current Connection. In the code window that comes up, enter the following
code and then execute to insert the data:
USE ApressFinancial
GO
INSERT INTO ShareDetails.SharePrices (ShareId, Price, PriceDate)
VALUES (1,2.155,'1 Aug 2008 10:10AM')
INSERT INTO ShareDetails.SharePrices (ShareId, Price, PriceDate)
VALUES (1,2.2125,'1 Aug 2008 10:12AM')
INSERT INTO ShareDetails.SharePrices (ShareId, Price, PriceDate)
VALUES (1,2.4175,'1 Aug 2008 10:16AM')
INSERT INTO ShareDetails.SharePrices (ShareId, Price, PriceDate)
VALUES (1,2.21,'1 Aug 2008 11:22AM')
INSERT INTO ShareDetails.SharePrices (ShareId, Price, PriceDate)
VALUES (1,2.17,'1 Aug 2008 14:54')
INSERT INTO ShareDetails.SharePrices (ShareId, Price, PriceDate)
VALUES (1,2.34125,'1 Aug 2008 16:10')
INSERT INTO ShareDetails.SharePrices (ShareId, Price, PriceDate)
VALUES (2,41.10,'1 Aug 2008 10:10AM')
INSERT INTO ShareDetails.SharePrices (ShareId, Price, PriceDate)
VALUES (2,43.22,'2 Aug 2008 10:10AM')
INSERT INTO ShareDetails.SharePrices (ShareId, Price, PriceDate)
VALUES (2,45.20,'3 Aug 2008 10:10AM')
13. We can now navigate back to the View Designer window. Execute the code within the view’s code window by
pressing the execute button, and you should see the results displayed in Figure 9-24.
Figure 9-24. View test results.
14. Assign the view to the ShareDetails schema in the view’s Properties window, as shown in Figure 9-25.
15. The final action is to save the view. As before, click the close button and save the view as vw_SharePrices.
Now that we have built views using the designer, it’s time to build one with T-SQL.
CHAPTER 9 ■ B U ILDING A V IEW 321
Figure 9-25. Setting the view schema
CREATE VIEW Syntax
Very quickly, you will find that creating a view using T-SQL is the better way forward. It is just as fast
as building a view using the designer.
CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ]
[ WITH
AS select_statement [ ; ]
[ WITH CHECK OPTION ]
The basic CREATE VIEW syntax is very simple and straightforward. The following syntax is the
most basic syntax of the CREATE VIEW statement and is the one used most often:
CREATE VIEW [database_name.][schema_name.]view_name
WITH {ENCRYPTION | SCHEMABINDING}
AS
SELECT_statement
Taking a look at the first section of the syntax, notice that the name of the view can be prefixed
with the name of the schema and the name of the database to which it belongs; however, the database
name and the schema are optional. Providing that we are in the correct database and are logged
in with the ID we wish to create the view for, the database_name and schema_name options are not
required, especially if the logon has the desired schema as their default, as the options will be assumed
from the connection details. For production views, rather than views used purely by a single SQL
Server user, it is recommended that they be built by the database owner. If the view is built by a
nondatabase owner, then when someone tries to execute the view, that user will need to prefix the
name of the view with the login of the person who created it.
Following on from these options, we build the query, typically formed with a SELECT statement
that makes up the view itself. As you saw in the previous example, the SELECT statement can cover
one or many tables or views, many columns, and as many filtering options using the WHERE statement
as you wish. We cannot reference any temporary variable or temporary table within a view, or create
322 CHAPTER 9 ■ B U ILDING A V IEW
a new table from a view by using the INTO clause. To clarify, it is not possible to have a SELECT column
INTO newtable.
The ENCRYPTION option will take the view created and encrypt the schema contained so that the
view is secure and no one can see the underlying code or modify the contents of the SELECT statement
within. However (I know I keep repeating this, but it is so important), do keep a backup of the contents
of the view in a safe place in development in case any modifications are required.
The SCHEMABINDING option ensures that any column referenced within the view cannot be dropped
from the underlying table without dropping the view built with SCHEMABINDING first. This, therefore,
keeps the view secure with the knowledge that there will be no run-time errors when columns have
been altered or dropped from the underlying table, and the view is not altered in line with those
changes. If you try to remove a column from the table that is contained within a schema bound view,
for example, then you will receive an error. There is one knock-on effect when using SCHEMABINDING:
all tables or other views named within the SELECT statement must be prefixed with the name of the
schema of the table or view, even if the owner of these objects is the same as the schema of the view.
Let’s go back to the two options that will be used less often, the first being WITH CHECK OPTION.
If the view is being used as the basis of completing updates to the underlying table, then any modification
call, such as UPDATE/DELETE/INSERT, will still make the data visible through the view.
■Note Even with WITH CHECK OPTION defined, if the data is modified directly in the table, it won’t be verified
against any views defined with the underlying tables. Also, if the view uses TOP, then WITH CHECK OPTION cannot
be defined.
The final possible option, VIEW_METADATA, exposes the view’s metadata if you are calling the view
via ODBC, OLE DB, and so on—in other words, from a program that is external to SQL Server.
Now that you are aware of the basic syntax for creating a view, the next example will take this
knowledge and build a new view for the database.
Creating a View: a Query Editor Pane
Another method for creating views is by using T-SQL code in a Query Editor pane—in my experience,
the fastest and best option. This can be a faster method for building views than using SQL Server
Management Studio, especially as you become more experienced with T-SQL commands. This
section will demonstrate the T-SQL syntax required to create a view, which you will soon see is very
straightforward.
The SELECT statement forms the basis for most views, so this is where most of the emphasis is placed
when developing a view. By getting the SELECT statement correct and retrieving the required data, it can
then be easily transformed into a view. This is how the view in the following example is created, so let’s
look at building a view using T-SQL and a Query Editor pane. In the following example, we will create a
view that returns a list of transactions for each customer with some customer information.
Try It Out: Creating a View in a Query Editor pane
1. Ensure that a SQL Server Query Editor pane is running and that there is an empty Query Editor pane. First of all,
let’s get the T-SQL correct. We need to link in three tables: the CustomerDetails.Customers table to get the
name and address, the TransactionDetails.Transactions table so we can get a list of transactions for
the customer, and finally the TransactionDetails.TransactionTypes table so that each transaction type
has its full description. The code is as follows:
CHAPTER 9 ■ B U ILDING A V IEW 323
SELECT c.AccountNumber,c.CustomerFirstName,c.CustomerOtherInitials,
tt.TransactionDescription,t.DateEntered,t.Amount,t.ReferenceDetails
FROM CustomerDetails.Customers c
JOIN TransactionDetails.Transactions t ON t.CustomerId = c.CustomerId
JOIN TransactionDetails.TransactionTypes tt ON
tt.TransactionTypeId = t.TransactionType
ORDER BY c.AccountNumber ASC, t.DateEntered DESC
2. Once done, execute the code by pressing F5 or Ctrl+E or clicking the execute button.
3. We can now wrap the CREATE VIEW statement around our code. Execute this code to store the view in the
ApressFinancial database. As there is an ORDER BY clause, we need to add to the query a TOP statement,
so we have TOP 100 Percent.
CREATE VIEW CustomerDetails.vw_CustTrans
AS
SELECT TOP 100 PERCENT
c.AccountNumber,c.CustomerFirstName,c.CustomerOtherInitials,
tt.TransactionDescription,t.DateEntered,t.Amount,t.ReferenceDetails
FROM CustomerDetails.Customers c
JOIN TransactionDetails.Transactions t ON t.CustomerId = c.CustomerId
JOIN TransactionDetails.TransactionTypes tt ON
tt.TransactionTypeId = t.TransactionType
ORDER BY c.AccountNumber ASC, t.DateEntered DESC
This view is a straightforward view with no ENCRYPTION or SCHEMABINDING options. The remainder of the SELECT
statement syntax is very straightforward.
Creating a View: SCHEMABINDING
The following example will bind the columns used in the view to the actual tables that lie behind the
view, so that if any column contained within the view is modified, an error message will be displayed
and the changes will be cancelled. The error received will be shown so that we can see for ourselves
what happens.
First of all, let’s build the view before going on to discuss the background. This view is going to
list products for customers, therefore linking the Customers.CustomerProducts and CustomerDetails.
FinancialProducts tables.
Try It Out: Creating a View with SCHEMABINDING
1. Create a new Query Editor pane and connect it to the ApressFinancial database. We can then create the
T-SQL that will form the basis of our view.
SELECT c.CustomerFirstName + ' ' + c.CustomerLastName AS CustomerName,
c.AccountNumber, fp.ProductName, cp.AmountToCollect, cp.Frequency,
cp.LastCollected
FROM CustomerDetails.Customers c
JOIN CustomerDetails.CustomerProducts cp ON cp.CustomerId = c.CustomerId
JOIN CustomerDetails.FinancialProducts fp ON
fp.ProductId = cp.FinancialProductId
324 CHAPTER 9 ■ B U ILDING A V IEW
2. We need some test data within the system to test this out. This is detailed in the following code. Enter this code
and execute it:
INSERT INTO CustomerDetails.FinancialProducts (ProductId,ProductName)
VALUES (1,'Regular Savings'),
(2,'Bonds Account'),
(3,'Share Account'),
(4,'Life Insurance')
INSERT INTO CustomerDetails.CustomerProducts
(CustomerId,FinancialProductId,
AmountToCollect,Frequency,LastCollected,LastCollection,Renewable)
VALUES (1,1,200,1,'31 October 2008','31 October 2025',0),
(1,2,50,1,'24 October 2008','24 March 2009',0),
(2,4,150,3,'20 October 2008','20 October 2008',1),
(3,3,500,0,'24 October 2008','24 October 2008',0)
3. Test out that the SELECT T-SQL works as required by executing it. The results you get returned should look
similar to Figure 9-26.
Figure 9-26. Testing schema binding in T-SQL
4. We now need to create the CREATE VIEW. First of all, we are completing a test to see whether the view already exists
within the system catalogs. If it does, then we DROP it. Then we define the view using the WITH SCHEMABINDING
clause. The other change to the T-SQL is to prefix the tables we are using with the schema that the tables come
from. This is to ensure that the schema binding is successful and can regulate when a column is dropped.
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = N'vw_CustFinProducts'
AND TABLE_SCHEMA = N'CustomerDetails')
DROP VIEW CustomerDetails.vw_CustFinProducts
GO
CREATE VIEW CustomerDetails.vw_CustFinProducts WITH SCHEMABINDING
AS
SELECT c.CustomerFirstName + ' ' + c.CustomerLastName AS CustomerName,
c.AccountNumber, fp.ProductName, cp.AmountToCollect, cp.Frequency,
cp.LastCollected
FROM CustomerDetails.Customers c
JOIN CustomerDetails.CustomerProducts cp ON cp.CustomerId = c.CustomerId
JOIN CustomerDetails.FinancialProducts fp ON
fp.ProductId = cp.FinancialProductId
5. Once done, execute the code by pressing F5 or Ctrl+E or clicking the execute button. You should then see the
following message:
The command(s) completed successfully.
CHAPTER 9 ■ B U ILDING A V IEW 325
6. Now that our vw_CustFinProducts view is created, which we can check by looking in the SQL Server
Management Studio Object Explorer, it is possible to demonstrate what happens if we try to alter a column
used in the view so as to affect one of the underlying tables. Enter the following code, and then execute it:
ALTER TABLE CustomerDetails.Customers
ALTER COLUMN CustomerFirstName nvarchar(100)
7. You will then see in the Results pane two error messages: the first shows that an alteration has been attempted
on the CustomerDetails.Customers table and has been disallowed and names the view stopping this, and
the second shows that the alteration failed.
Msg 5074, Level 16, State 1, Line 1
The object 'vw_CustFinProducts' is dependent on column 'CustomerFirstName'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN CustomerFirstName failed because one or more
objects access this column.
Indexing a View
Views can be indexed just as tables can be indexed. Rules in choosing columns to make indexes on a
view are similar to those for a table. There are also some major requirements you need to meet
before you can index a view. I will show you these first so that you are aware of which views can be
indexed and what you have to do with your view.
When building indexes on views, the first index to be created must be a unique clustered index.
Once such an index has been built, additional nonclustered indexes on this view can then be created.
This can also be taken further, in that if we have a view with subsequent indexes on it, and we drop
the unique clustered index, then all of the other indexes will automatically be dropped. Also, if we
drop the view, as we would expect, the indexes are also dropped.
The view that the index is to build on must only contain tables and cannot contain views. The
tables must all come from one database, and the view must also reside in that database and have
been built with the SCHEMABINDING option.
As you saw when creating our database, certain options can be switched on or off. The following
options must be set to ON while creating an index. These options need only be set to ON for that session
and therefore would precede the CREATE INDEX statement.
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON
On top of this, the NUMERIC_ROUNDABORT option must be set to OFF.
SET NUMERIC_ROUNDABORT OFF
Finally, the view itself cannot have text, ntext, or image columns defined in it. In Chapter 11,
we’ll look at how to group data through a clause called GROUP BY. If you have grouping within your
view, then the columns used to group data are the only columns that can be in the first index.
Although these seem like they could be quite restrictive requirements, the upside is that indexing
views also comes with major speed implications. If a view remains without an index, every time that
326 CHAPTER 9 ■ B U ILDING A V IEW
the view is executed, the data behind the view, including any joins, is rebuilt and executed. However,
as the first index is a clustered index, this is similar to a clustered table index, and the data will be
retrieved at index-creation time and stored in that order. Also, like table indexes, when the data is
modified, then the index will receive the updates as well. Therefore, if SQL Server can use the clustered
index, there will be no need to run the query again.
SQL Server will use any indexes that you have on the tables when building the views. Indexing a
view is most beneficial when the data in the underlying tables is not changing frequently and when
the view is executed often. Keep in mind that a view is taking information from other tables and is
not a table itself, and therefore any updates to the underlying tables will not be reflected in the view
until the view is rerun.
By placing an index on a view, the columns named within the index are stored within the database,
as are all of the columns defined for the view, along with the data rows. Therefore, any changes
to the raw data within the native tables will also be reflected in the data stored for the view. Keep in
mind the performance issues with this. Every data change in the tables used in the views requires
SQL Server to evaluate the effect the change has on the view. This requires more processing by SQL
Server, causing a slowdown in performance. Temper this perceived gain of using an index with the
downside of the extra processing required to keep the data up to date in two places for the table and
two places for the index for those columns involved in the view.
Now that you are aware of the pros and cons of building indexes on views, and how they differ
from indexes for tables, it is time to build an index on our view.
The aim of this index is to locate a record in the view quickly. We want to be able to find all the
products for a customer based on his or her account number. Notice that we are not using CustomerId
here. First of all, that column is not within the view, so it is unavailable for selection anyway, but we
have to cater to when a customer phones up and supplies the account number. This customer will
be unaware of his or her ApressFinancial internal CustomerId. Building the index is very quick and
very simple, especially since you already know the basics from building indexes earlier in the book.
Try It Out: Indexing a View
1. The view we want to index is vw_CustFinProducts, as we know that was created with SCHEMABINDING. The
unique clustered index will be on the AccountNumber, as we know that this will be unique. In a Query Editor
query pane, enter the following code:
CREATE UNIQUE CLUSTERED INDEX ix_CustFinProds
ON CustomerDetails.vw_CustFinProducts (AccountNumber,ProductName)
2. Execute this code. When you do, you might get an error. The error I received was as follows:
Msg 1935, Level 16, State 1, Line 1
Cannot create index. Object 'vw_CustFinProducts' was created with the
following SET options off: 'ANSI_NULLS, QUOTED_IDENTIFIER'.
3. As was mentioned when discussing the options required to index a view, we didn’t have these two options set to
ON. We therefore have to re-create the view. From Object Explorer, right-click and select Script View As ➤
CREATE To ➤ New Query Editor Window, as you see in Figure 9-27.
Figure 9-27. Scripting the view
CHAPTER 9 ■ B U ILDING A V IEW 327
4. This brings up the code in a new Query Editor pane. Modify the two SET options and add in a DROP VIEW
statement so that we can re-create the view. Executing the code should be successful.
USE [ApressFinancial]
GO
/****** Object: View [CustomerDetails].[vw_CustFinProducts]
Script Date: 08/07/2008 12:31:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DROP VIEW CustomerDetails.vw_CustFinProducts
GO
CREATE VIEW [CustomerDetails].[vw_CustFinProducts] WITH SCHEMABINDING
AS
SELECT c.CustomerFirstName + ' ' + c.CustomerLastName AS CustomerName,
c.AccountNumber, fp.ProductName, cp.AmountToCollect,
cp.Frequency, cp.LastCollected
FROM CustomerDetails.Customers c
JOIN CustomerDetails.CustomerProducts cp ON cp.CustomerId = c.CustomerId
JOIN CustomerDetails.FinancialProducts fp ON
fp.ProductId = cp.FinancialProductId
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
5. We can then move back to our pane with the CREATE INDEX statement. Executing that code should be successful
now as well.
The index on a view has now been successfully created. As you can see, there are a number of restrictions, but not to the
point that no index can exist. You just have to think about what you are doing, and if you have a query in your view that
contains an item from the preceding list and you wish to create an index, you’ll just have to find a way around it.
Summary
This chapter has given you the confidence, when building your own view, of knowing which options
and features of views you wish to use. We have covered what a view is, how views can improve a
database’s security, how to encrypt your view, building a view using SQL Server Management Studio
and a Query Editor pane, how to join two tables within a view, and indexing a view.
Creating a view when there is more than one table to retrieve data from on a regular basis is
quite often a sensible solution, even more so when you wish to use views as a method of simplifying
the database schema and abstracting the database data into a presentation layer for users.
Encrypting views may seem like a good idea to hide the schema of your database even further
from potential users; however, do use encrypted views with caution, and always keep a backup of the
source in a safe and secure environment. People have been known to keep a printout of the view just
in case the source becomes corrupt. Use encrypted views sparsely, and only when really required.
Having seen three different methods to build a view, you should have found a method that suits
you and your style of working. You may find that as time moves on, the tool used alters, as do the
methods within that tool. Never discount any tool or option within SQL Server and banish it to the
328 CHAPTER 9 ■ B U ILDING A V IEW
annals of history: always keep each option and tool in mind, for one day that area may be your savior.
When starting out, switch between each method for building a view so that you are fully conversant
with each method.
You will find that in most cases when building views, the SCHEMABINDING option will be a good
option to have on a view, ensuring that a view that works today will always work. It would only be
when someone deliberately removes your view from the system to complete table changes, and then
doesn’t correctly put it back, that you would find that a view has stopped working. Herein lies yet
another scenario for keeping the code of encrypted views at hand: if you have encrypted views, along
with SCHEMABINDING, and someone wishes to alter an underlying table, then you had better have the
code available!
Finally, being aware of the differences between indexes on tables and indexes in views is crucial
to a successful and well-performing view. If you are unsure, then try out the view with and then
without an index within your development environment.
329
■ ■ ■
C H A P T E R 1 0
Stored Procedures and Functions
Now that you know how to build queries of single executable lines of T-SQL code, it is time to look
at how to place these into a stored procedure or a function within SQL Server, allowing them to be
run as often as they are required.
Stored procedures and functions are two different types of objects that provide different, yet
similar, functionality. You will see these differences within the examples, but the main point is that
a stored procedure is a set of code that runs as its own unit of work, while a function, which also runs
as its own unit of work, is contained within another unit of work. When building tables, you saw the
system function GETDATE(). When I discuss functions later in this chapter, you will learn more about
both the similarities and differences between these two types of objects.
While you may save queries on a disk drive somewhere, you have not stored them within SQL
Server itself up to this point, nor have you saved them as multiple units of work. Often, however, you
need to execute multiple queries in series from SQL Server. To do this, you employ stored procedures
or functions. SQL Server assumes that a stored procedure or a function will be run more than once.
Therefore, when it is executed for the first time, a query plan is created for it, detailing how best to
execute the query. It is also possible, just like any other database object, to assign security to a stored
procedure or a function, so that only specific users can run it, lending added security compared to a
one-time-only query saved to a hard drive.
The aim of this chapter is to build a simple stored procedure that will insert a single record and
then look at error handling and controlling the flow of execution within our procedure. You will then
move on to building a user-defined function and invoking it. We’ll look at some system functions in
Chapter 11.
Therefore, this chapter will
• Describe what a stored procedure is.
• Explain the advantages of a stored procedure over a view.
• Cover the basic syntax for creating a stored procedure.
• Show how to set values within variables.
• Control the flow through a stored procedure.
• Look at the differences between a function and a stored procedure.
• Cover the basic syntax for creating a T-SQL user-defined function.
Finally, as this chapter completes your overview of objects within SQL Server, you will learn how
you can create an alias, or a synonym, to give objects an alternative name. An alias provides its worth
for client-based applications by protecting the client from any changes to the structure of the database,
such as a change of the schema owner of an object.
330 CHAPTER 10 ■ STORED P ROCEDURES AND F UNCTIONS
What Is a Stored Procedure?
In the simplest terms, a stored procedure is a collection of compiled T-SQL commands that are
directly accessible by SQL Server. The commands placed within a stored procedure are executed as
one single unit, or batch, of work—the benefit of this is that network traffic is greatly reduced, as
single SQL statements are not forced to travel over the network; hence, this reduces network congestion.
In addition to SELECT, UPDATE, or DELETE statements, stored procedures are able to call other stored
procedures, use statements that control the flow of execution, and perform aggregate functions or
other calculations.
Any developer with access rights to create objects within SQL Server can build a stored procedure.
There are also hundreds of system stored procedures, all of which start with a prefix of sp_,
within SQL Server. Under no circumstances should you attempt to modify any system stored procedure
that belongs to SQL Server, as this could corrupt not only your database, but also other databases,
requiring you to perform a full restore.
There is little point in building a stored procedure just to run a set of T-SQL statements only
once; conversely, a stored procedure is ideal for when you wish to run a set of T-SQL statements
many times. The reasons for choosing a stored procedure are similar to those that would persuade
you to choose a view rather than letting users access table data directly. Stored procedures also
supply benefits; for example, SQL Server will always cache a stored procedure plan in memory, and
it is likely to remain in cache and be reused, whereas ad hoc SQL plans created when running ad hoc
T-SQL may or may not be stored in the procedure cache. The latter may lead to bloating of the procedure
cache with lots of very similar plans for similar batches, as SQL Server won’t match plans that
use the same basic code but with different parameter values.
Stored procedures give your application a single proven interface for accessing or manipulating
your data. This means that you keep data integrity, make the correct modifications or selections to
the data, and ensure that users of the database do not need to know structures, layouts, relationships,
or connected processes required to perform a specific function. We can also validate any data
input and ensure that the data brought into the stored procedure is correct.
Just like a view and tables, we can grant very specific execute permission for users of stored
procedures (the only permission available on a stored procedure is EXECUTE).
To prevent access to the source code, you can encrypt stored procedures, although this really
ought to be used in only the most required cases. The code itself isn’t actually encrypted; it is only
obfuscated, which means it is possible to decrypt the code if required. Therefore, it isn’t a total
prevention of viewing the code, but it does stop stray eyes. It also limits what can be seen in a tool
called SQL Server Profiler, which is used to profile performance of stored procedures, code, and so
on, thus causing difficulty in checking what is happening if there is a problem. Therefore, to reiterate,
you need to carefully justify any “encryption” you wish to do.
CREATE PROCEDURE Syntax
Begin a stored procedure with a CREATE PROCEDURE statement. The CREATE PROCEDURE syntax offers a
great many flexible options and extends T-SQL with some additional commands. The syntax generally
appears as follows:
CREATE PROCEDURE procedure_name
[ { @parameter_name} datatype [= default_value] [OUTPUT]]
[ { WITH [RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ]
AS
[BEGIN]
statements
[END]
CHAPTER 1 0 ■ STORED P ROCEDURES AND FUNCT IONS 331
First of all, it is necessary to inform SQL Server which action you wish to perform. Obviously, we
wish to create a stored procedure, so we need to supply a CREATE PROCEDURE statement.
The next part of the syntax is to give the procedure a name. It would be advisable, just as it is with
any SQL Server object, to adhere to a naming standard. Everyone has their own standard within their
installation, but if you prefix the name with sp_, a very common naming convention, then you will
know what that object is. However, this is not something I recommend for two reasons. The first is
that stored procedures prefixed by sp_ are seen as system stored procedures. The second is that you
can hit some unnecessary compile locks due to system stored procedure lookups. Therefore, do avoid
this naming convention.
Many people adopt a different naming convention whereby the prefix defines what the stored
procedure will do; for example, an update would have a prefix of up, a deletion dt, and a selection sl.
There are many different prefixes you could use, but once you have decided on your standard, you
should stick with it.
Some procedures may require information to be provided in order for them to do their work;
this is achieved by passing in a parameter. For example, passing in a customer number to a stored
procedure would provide the necessary information to allow creation of a list of transactions for a
statement. More than one parameter can be passed in: all you do is separate them with a comma.
Any parameter defined must be prefixed with an @ sign. Not all procedures will require parameters,
so this is optional; however, if you do wish to pass in parameters to a stored procedure, name
the parameters and follow them with the data type and, where required, the length of the data to pass
in. For example, the following specifies a parameter of name L_Name, with varchar data type of length 50:
@L_Name varchar(50)
You can also specify a default value in the event that a user does not provide one at execution
time. The value specified must be a constant value, like 'DEFAULT' or 24031964, or it can be NULL. It is
not possible to define a variable as a default value, since the procedure cannot resolve this when the
procedure is built. For example, if your application is commonly, but not exclusively, used by the
marketing department, you could make the department variable optional by setting a default of
'marketing':
@department varchar(50) = 'marketing'
Thus, in this example, if you were from marketing, you would not need to provide the department
input. If you were from information services, however, you could simply provide an input for department
that would override the default.
It is also possible to return a value, a number of values, or even a table of data from a stored
procedure using a parameter to pass the information out. The parameter would still be defined as if
it was for input, with one exception and one extra option. First of all, the exception: it is not possible
to define a default value for this parameter. If you try to do so, no errors will be generated, but the
definition will be ignored. The extra syntax option that is required is to suffix the parameter with the
keyword OUTPUT. This must follow the data type definition:
@calc_result varchar(50) OUTPUT
You are not required to place OUTPUT parameters after the input parameters; they can be intermixed.
Conventionally, however, try to keep the OUTPUT parameters until last, as it will make the stored
procedure easier to understand.
■Tip Output parameters can also be input parameters, and therefore can be used to pass a value in as well as
retrieve a value out.
332 CHAPTER 10 ■ STORED P ROCEDURES AND F UNCTIONS
Before continuing, one last thing about parameters needs to be discussed, and it has to do with
executing the procedure and working with the defined parameters. When it comes to executing a
stored procedure that has input parameters, you have two ways to run it.
The first method is to name the stored procedure and then pass the input values for the parameters
in the same order that they are defined. SQL Server will then take each comma-delimited value
set and assign it to the defined variable. However, this does make an assumption that the order of the
parameters does not change, and that any default value-defined parameters are also set with a value.
The second, and preferred, method of executing a stored procedure is to name the parameter,
and follow this with the value to pass in. We are then ensuring that, at execution time, it doesn’t
matter what order the stored procedure has named the parameters, because SQL Server will be able
to match the parameter defined with the parameter defined within the stored procedure. We then
don’t need to define a value for parameters that already have default values. Also, if the stored procedure
needs to be expanded, for backward compatibility, any new parameters can be defined with
default values, therefore removing the need to change every calling code. There will be examples of
each of the two different methods of passing in values to parameters within this chapter.
Next come two options that define how the stored procedure is built. First of all, just as a reminder,
a stored procedure, when first run without an existing plan in the procedure cache, is compiled into
an execution plan, which is an internal data structure in SQL Server that describes how it should go
about performing the operations requested within the stored procedures. SQL Server stores the
compiled code for subsequent executions, which saves time and resources.
However, the RECOMPILE option on a stored procedure dictates to SQL Server that every time the
stored procedure is run, the whole procedure is recompiled. Typically, when a parameter can greatly
affect the number of rows returned, you may want to add the RECOMPILE option to a stored procedure
to force the optimizer to produce the best plan every time (i.e., you want to avoid reuse of a plan that
may not be very good for certain parameter values).
The second of the two options is the ENCRYPTION keyword. It is possible to encrypt—well, obfuscate
at least—a stored procedure so that the contents of the stored procedure cannot be viewed easily.
Keep in mind that ENCRYPTION does not secure the data, but rather protects the source code from
inspection and modification. Both ENCRYPTION and RECOMPILE are preceded by the WITH keyword and
can be employed together when separated by a comma:
CREATE PROCEDURE sp_do_nothing
@nothing int
WITH ENCRYPTION, RECOMPILE
AS
SELECT something FROM nothing
The keyword AS defines the start of the T-SQL code, which will be the basis of the stored procedure.
AS has no other function, but is mandatory within the CREATE PROCEDURE command defining the
end of all variable definitions and procedure creation options. Once the keyword AS is defined, you
can then start creating your T-SQL code.
It is then possible to surround your code with a BEGIN...END block. I tend to do this as a matter
of course so that there is no doubt where the start and end of the procedure lie.
Returning a Set of Records
One method of achieving output from a stored procedure is to return a set of records, also known as
a recordset. This recordset may contain zero, one, or many records as a single batch of output. This
is achieved through the use of the SELECT statement within a stored procedure—what is selected is
returned as the output of the stored procedure. Don’t be fooled into thinking, though, that we can
only return one recordset within a stored procedure, as this is not true: we can return as many recordsets
as we wish.
CHAPTER 1 0 ■ STORED P ROCEDURES AND FUNCT IONS 333
In this chapter, you will see single recordsets of data returned and how these look within Query
Editor. Returning single, or even multiple, recordsets should not really concern you at this stage. It’s
of more concern to developers in languages such as C#, VB .NET, and so on. Multiple recordsets will
only concern you when we move on to more advanced stored procedures with multiple queries.
Creating a Stored Procedure: Management Studio
Now that you have seen some of the merits of a stored procedure over other methods of working
with data, it is time to create the first stored procedure in this chapter. This stored procedure will be
built within SQL Server Management Studio to insert a customer into the CustomerDetails.
Customers table from the information passed to it. This is also the first part in our overall security
solution. By using a stored procedure to enter the data into the underlying table, we will be in control
of what data is entered, as the data can be validated and verified. You can also remove all access from the
table and leave the stored procedure to serve as the only method of inserting data (you would also have
stored procedures that update, delete, and retrieve data). We will look at this toward the end of the
chapter.
Try It Out: Creating a Stored Procedure Using SQL Server Management Studio
1. Navigate to the ApressFinancial database, find the Programmability node, and right-click Stored Procedures.
From the pop-up menu, select New Stored Procedure.
2. This opens a Query Editor pane with code from a basic stored procedure template—the template called Create
Stored Procedure (New Menu) to be exact. You can either alter the procedure by changing the template options
by clicking Ctrl+Shift+M, or just write the code from scratch. As we have chosen to create a stored procedure via
the Object Explorer, we will use the template this time. Figure 10-1 shows the template options that can be
changed.
Figure 10-1. A stored procedure’s blank template
334 CHAPTER 10 ■ STORED P ROCEDURES AND F UNCTIONS
3. The first three options, shown in Figure 10-2, are not part of the stored procedure syntax; they are extra options
used within the comments of the stored procedure. The first option is very useful because it will probably be a
dbo account that adds the stored procedure to the database, and therefore it will be hard to track who the actual
creator of the stored procedure was. It may be that only one account “releases” all the code to production for
deployment. The second option, Create Date, is not quite as relevant, as this can be found by interrogating system
views. The Description option is excellent and should form part of every stored procedure, as it will allow a short
description of what the stored procedure is trying to achieve. Never go into too much detail in a description,
because not everyone has good discipline in updating the comments when the stored procedure changes. However, a
short “we are trying to achieve” set of text is perfect.
Figure 10-2. First set of template options filled
4. We can now move to the template options that form part of the CREATE PROCEDURE syntax. The first option is
the name. I have called this apf_insCustomer to define that it’s a stored procedure in the ApressFinancial
database and that we are inserting a row in the CustomerDetails.Customers table. Then we can insert
two parameters, as this is what the template is set up for. The first two parameters will be used to populate
CustomerFirstName and CustomerLastName. We will look at the rest in a moment. The parameter values do
not have to be the same name as the columns they will be working with, but it is best to have similar names. The
data type and data length should be defined as the same type and length as the columns they will be used for.
Failure to do this could lead to problems with data truncation if you make the parameter columns too long, for
example. We also remove the values in the default options. Your template options should now look similar to
what you see in Figure 10-3.
Figure 10-3. The remaining parameters
5. Click OK. The code will now look like the following:
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
CHAPTER 1 0 ■ STORED P ROCEDURES AND FUNCT IONS 335
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Robin Dewson
-- Create date: 24 Mar 2008
-- Description: This is to insert a customer
-- =============================================
CREATE PROCEDURE apf_InsertCustomer
-- Add the parameters for the stored procedure here
@FirstName varchar(50) = ,
@LastName varchar(50) =
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT @FirstName, @LastName
END
GO
6. We can now define the remaining parameters. There are one or two points to make before we progress. First of
all, the parameters can be in any order, although it is best to try and group parameters together. The second point
is that parameters like @CustTitle, @AddressId, @AccountNumber, and @AccountTypeId in this example
are showing the numerical reference values that would come from values defined in a graphical front end. You
may be wondering why the stored procedure is not generating these values from other information passed. For
example, why is the stored procedure not producing the title ID from Mr., Miss, etc.? It is likely that the operator
using the front end had a combo box with a list of possible values to choose from, with IDs corresponding to
titles. In the case of the address, the ID would link back to an external address database, so rather than holding
the whole address, we could receive just the ID selected when the operator used the address lookup. The code
with the remaining parameters is shown here:
CREATE PROCEDURE CustomerDetails.apf_InsertCustomer
-- Add the parameters for the function here
@FirstName varchar(50) ,
@LastName varchar(50),
@CustTitle int,
@CustInitials nvarchar(10),
@AddressId int,
@AccountNumber nvarchar(15),
@AccountTypeId int
336 CHAPTER 10 ■ STORED P ROCEDURES AND F UNCTIONS
7. Moving on to the remaining section of the stored procedure, we will take the values of our parameters and use
these as input to the relevant columns. The remaining code for the stored procedure is as follows:
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
INSERT INTO CustomerDetails.Customers
(CustomerTitleId,CustomerFirstName,CustomerOtherInitials,
CustomerLastName,AddressId,AccountNumber,AccountType,
ClearedBalance,UnclearedBalance)
VALUES (@CustTitle,@FirstName,@CustInitials,@LastName,
@AddressId,@AccountNumber,@AccountTypeId,0,0)
END
GO
8. When you execute the preceding code, providing you have made no typing mistakes, you should see the following
output:
Command(s) completed successfully.
9. This will have added the stored procedure to the database. We can check this. Move back to Object Explorer,
right-click Stored Procedures, and select Refresh. After the refresh, you should see the stored procedure in the
Object Explorer, as shown in Figure 10-4.
Figure 10-4. Object Explorer with the stored procedure listed
10. We have completed our first developer-built stored procedure within the system. Inserting data using the stored
procedure will now be demonstrated so we can see the procedure in action. To execute this stored procedure, we
need to specify its name and pass the data in with parameters. There are two ways we can progress. The first method
is to pass the data across in the same order as the parameters defined within the stored procedure as follows:
CustomerDetails.apf_InsertCustomer 'Henry','Williams',
1,NULL,431,'22067531',1
11. If you execute this, you should see the following output:
(1 row(s) affected)
12. However, there is a downside to this method: if someone alters the stored procedure and places a new parameter
in the middle of the existing list or changes the order of the parameters, or perhaps you don’t know the order of
the parameters, then you are at risk for errors. The preferred method is to name the parameters and the values
as shown in the next example. Notice as well that the order has changed.
CHAPTER 1 0 ■ STORED P ROCEDURES AND FUNCT IONS 337
CustomerDetails.apf_InsertCustomer @CustTitle=1,@FirstName='Julie',
@CustInitials='A',@LastName='Dewson',@AddressId=6643,
@AccountNumber='SS865',@AccountTypeId=6
13. Again, if you execute this, you should see the same results:
Command(s) completed successfully.)
You can check that the two customers have been entered if you wish. Let’s take a look at two different methods for
executing procedures next.
Different Methods of Executing
There are two different methods of executing a stored procedure. The first is to just call the stored
procedure, as you saw in the preceding example. The second method is to use the EXEC(UTE) command.
Both have the end result of invoking the stored procedure, but which is better for you to use depends
on the particular situation.
No EXEC
It is possible to call a stored procedure without prefixing the stored procedure name with the EXEC(UTE)
statement. However, the stored procedure call must be the first statement within a batch of statements
if you wish to exclude this statement.
With EXEC
As we have just indicated, if the stored procedure call is the second or subsequent statement within
a batch, then you must prefix the stored procedure with the EXEC(UTE) statement. On top of this, if
you are calling a stored procedure within another stored procedure, then you will need to prefix the
call with the EXEC(UTE) statement.
Using RETURN
One method of returning a value from a stored procedure to signify an error is to use the RETURN
statement. This statement immediately stops a stored procedure and passes control back out of it.
Therefore, any statements after the RETURN statement will not be executed.
It is not compulsory to have a RETURN statement within your code; it is only really necessary
when you either wish to return an error code or exit from a stored procedure without running any
further code from that point. A logical RETURN is performed at the end of a stored procedure, returning a
value of 0.
By default, 0 is returned if no value is specified after the RETURN statement, which means that the
stored procedure was successful. Any other integer value could mean that an unexpected result occurred
and that you should check the return code, although it is possible to return the number of rows
affected by the stored procedure, for example. Notice that the word “error” wasn’t mentioned, as it
may be valid for a nonzero return code to come out of a stored procedure.
338 CHAPTER 10 ■ STORED P ROCEDURES AND F UNCTIONS
In this example, we will create a stored procedure that will return two output parameters back
to the calling procedure or code, indicating the cleared and uncleared balances of a specific
customer.
We will also use the RETURN option to indicate whether the customer ID passed to the stored
procedure finds no rows. Note that this is not an error, as the stored procedure code will be working
as expected.
So you are probably wondering when to use output parameters and when to use RETURN. Output
parameters are used to return information back to a calling set of code and can handle any data type.
On the other hand, a RETURN can only return an integer numeric value and is used more often for indicating
success or failure.
Try It Out: Using RETURN and Output Parameters
1. The Template Explorer contains a template set up for output parameters. Navigate to this template, shown in
Figure 10-5, and double-click it.
Figure 10-5. Template Explorer with the OUTPUT stored procedure
2. This will open up a new Query Editor pane with the basics of the relevant stored procedure, which is shown,
reformatted, in the following code block. Take a moment to peruse this code. First of all, the first batch within the
template sets up checks to see whether the stored procedure already exists, and if it does, deletes the procedure
through the DROP PROCEDURE command. After running DROP PROCEDURE, just like after dropping any object,
all of the permissions associated with that object are lost when we re-create it as we discussed earlier.
-- ===============================================
-- Create stored procedure with OUTPUT parameters
-- ===============================================
-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'
AND SPECIFIC_NAME = N'
)
DROP PROCEDURE
GO
CREATE PROCEDURE
<@param1, sysname, @p1>
<@param2, sysname, @p2>
AS
SELECT @p2 = @p2 + @p1
CHAPTER 1 0 ■ STORED P ROCEDURES AND FUNCT IONS 339
GO
-- =============================================
-- Example to execute the stored procedure
-- =============================================
DECLARE <@variable_for_output_parameter, sysname, @p2_output>
EXECUTE
<@variable_for_output_parameter, sysname, @p2_output> OUTPUT
SELECT <@variable_for_output_parameter, sysname, @p2_output>
GO
3. Now that we have seen the code, it is time to update the template parameters. Again, we find that the template
is not ideal for our final solution, as we only have one input parameter and two output parameters. However, we
have populated the template parameters we need. This stored procedure will belong to the CustomerDetails
schema. We have one integer input parameter for the customer ID, followed by the first of our output parameters
for cleared balances. Once you have entered these settings, as shown in Figure 10-6, click OK.
Figure 10-6. Template values for the OUTPUT stored procedure
4. Let’s look at the code that was generated. The first section of code checks whether the stored procedure exists.
If it does, then we delete it using the DROP PROCEDURE statement.
-- ===============================================
-- Create stored procedure with OUTPUT parameters
-- ===============================================
-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'CustomerDetails'
AND SPECIFIC_NAME = N'apf_CustBalances'
)
DROP PROCEDURE CustomerDetails.apf_CustBalances
GO
340 CHAPTER 10 ■ STORED P ROCEDURES AND F UNCTIONS
5. Move on to the second section, which creates the contents of the stored procedure; we’ll go through each part
of it in turn. This stored procedure takes three parameters: an input parameter of @CustId, and two output
parameters that will be passed back to either another stored procedure or a program, perhaps written in C#, etc.
Don’t worry, it is possible to use Query Editor to see the value of the output parameter. When defining parameters
in a stored procedure, there is no need to specify that a parameter is set for input, as this is the default; however,
if we do need to define a parameter as an output parameter, we have to insert OUTPUT as a suffix to each parameter.
■Tip If we define an OUTPUT parameter but do not define a value within the stored procedure, it will have a value
of NULL.
CREATE PROCEDURE CustomerDetails.apf_CustBalances
@CustId int,
@ClearedBalance money OUTPUT, @UnclearedBalance money OUTPUT
AS
6. Take a look at the next section of code, which is very similar to what we have covered several times earlier in the
book where we are assigning values to variables:
SELECT @ClearedBalance = ClearedBalance, @UnclearedBalance = UnclearedBalance
FROM Customers
WHERE CustomerId = @CustId
7. The final section of the stored procedure returns a value from a system global variable, @@ERROR. We’ll look at
this variable in the next chapter, but in essence, this variable returns a number if an error occurred. From this, the
calling code can tell whether there have been problems and can then decide whether to ignore any values in the
OUTPUT parameter.
RETURN @@Error
GO
8. This completes the stored procedure definition. The template continues defining how to execute the stored procedure.
The first part of this section defines the variables that hold the output values and the return value. We do
not need to define a variable for the input value, although you could if it was required. Then we move to the
EXECUTE section of code. When a value is returned from a stored procedure, it is set on the left-hand side of the
stored procedure call and is not a parameter value. Then the stored procedure is defined with the three parameters.
Note that each output parameter has to have the OUTPUT keyword after it. The final section of the code is
a SELECT statement displaying the values returned and the output parameter.
-- =============================================
-- Example to execute the stored procedure
-- =============================================
DECLARE @ClearedBalance Money, @UnclearedBalance Money
DECLARE @RetVal int
EXECUTE @RetVal=CustomerDetails.apf_CustBalances 1,
@ClearedBalance OUTPUT,
@UnclearedBalance OUTPUT
SELECT @RetVal AS ReturnValue, @ClearedBalance AS ClearedBalance,
@UnclearedBalance AS UnclearedBalance
GO
CHAPTER 1 0 ■ STORED P ROCEDURES AND FUNCT IONS 341
9. Now that the template has been altered with the changes we need, execute the template by pressing Ctrl+E or
F5 or clicking the execute button on the toolbar. This will create the stored procedure and run the examples at the
end to demonstrate the procedure. Of course, we can run this section of code as many times as we want because the
whole scenario, from dropping and losing the stored procedure through to re-creating the stored procedure, is all
there, ready for us. The stored procedure will pass back its output parameter value to the @ClearedBalance
and @UnclearedBalance variables defined within the execution batch and the return value to the @RetVal
variable. From there, once the variables are set, the values can be printed out using a SELECT statement. This
will produce the output shown in Figure 10-7 in the results pane.
Figure 10-7. Results after running the OUTPUT stored procedure
We have now built two very basic stored procedures in which we are performing an INSERT and a SELECT. Next we look
at control of flow.
Controlling the Flow
When working on a stored procedure, there will be times when it is necessary to control the flow of
information through it. The main control of flow is handled with an IF...ELSE statement. You can
also control the flow with a WHILE...BREAK statement.
■Note The GOTO statement can also control the flow of a stored procedure. You can use this statement to jump
to a label within a stored procedure, but this can be a dangerous practice and really is something that should be
avoided. For example, it might be better to nest the stored procedure calls.
Controlling the flow through a stored procedure will probably be required when a procedure
does anything more than working with one T-SQL statement. The flow will depend on your procedure
taking an expression and making a true or false decision, and then taking two separate actions
depending on the answer from the decision.
IF . . . ELSE
At times, a logical expression will need to be evaluated that results in either a true or false answer.
This is where an IF...ELSE statement is needed. There are many ways of making a true or false condition,
and most of the possibilities involve relational operators such as <, >, =, and NOT; however, these
can be combined with string functions, other mathematical equations, comparisons between values
in local variables, or even system-wide variables. It is also possible to place a SELECT statement within an
IF...ELSE block, as long as a single value is returned.
A basic IF...ELSE would perhaps look like the following:
IF A=B
Statement when True
ELSE
Statement when False
342 CHAPTER 10 ■ STORED P ROCEDURES AND F UNCTIONS
IF...ELSE statements can also be nested and would look like the following; this example also
shows you how to include a SELECT statement within an IF decision:
IF A=B
IF (SELECT ClearedBalance FROM Customers WHERE CustomerId = 1) > $20000
Statement2 when True
ELSE
Statement2 when False
ELSE
Statement when False
As you can see, there is only one statement within each of the IF...ELSE blocks. If you wish to
have more than one line of executable code after the IF or the ELSE, you must include another control-offlow
statement, the BEGIN...END block. Before we can try this out, let’s take a look at how to code for
multiple statements within an IF...ELSE block.
BEGIN . . . END
If you wish to execute more than one statement in the IF or ELSE code block, you need to batch the
statements up. To batch statements together within an IF...ELSE, you must surround them with a
BEGIN...END block. If you try to have more than one statement after the IF, the second and subsequent
statements will run no matter what the setting of the IF statement is.
So if you have
DECLARE @VarTest
SET @VarTest = 2
IF @VarTest=1
SELECT 1
SELECT 2
then the SELECT 2 statement would run no matter what value you have for @VarTest. If you only want
SELECT 2 to run when @VarTest is 1, then you would code the example, thus placing the code you
want to run within the BEGIN...END block.
DECLARE @VarTest
SET @VarTest = 2
IF @VarTest=1
BEGIN
SELECT 1
SELECT 2
END
If you use an ELSE statement after a second or subsequent statement after an IF that has
no BEGIN...END block, you would get an error message. Therefore, the only way around this is
to use BEGIN...END.
WHILE . . . BREAK Statement
The WHILE...BREAK statement is a method of looping around the same section of code from zero to
multiple times based on the answer from a Boolean test condition, or until explicitly informed to exit
via the keyword BREAK.
CHAPTER 1 0 ■ STORED P ROCEDURES AND FUNCT IONS 343
The syntax for this command is as follows:
WHILE Boolean_expression
{ sql_statement | statement_block }
[ BREAK ]
{ sql_statement | statement_block }
[ CONTINUE ]
{ sql_statement | statement_block }
The code defined for the WHILE statement will execute while the Boolean expression returns a
value of True. You can have other control-of-flow statements such as an IF...ELSE block within your
WHILE block. This is where BREAK and CONTINUE could be used if required. You may wish to test a condition
and, if it returns a particular result, BREAK the loop and exit the WHILE block. The other option that
can be used is the CONTINUE statement. This moves processing straight to the WHILE statement again
and will stop any execution of code that is defined after it. The best way to illustrate these concepts
is to show a simple example of these three options in action.
Try It Out: WHILE...BREAK
1. The first option demonstrates how to build a WHILE loop and then test the value of a variable. If the test returns
True, we will break out of the loop; if it returns False, we will continue processing. Within the example, there
are two SELECT statements before and after an IF...ELSE statement. In this example, the first SELECT will
show the values of the variables, but the IF test will either stop the loop via BREAK or will move the code back
to the WHILE statement via the CONTINUE statement. Either of these actions will mean that the second SELECT
will not execute.
DECLARE @LoopCount int, @TestCount int
SET @LoopCount = 0
SET @TestCount = 0
WHILE @LoopCount <>
BEGIN
SET @LoopCount = @LoopCount + 1
SET @TestCount = @TestCount + 1
SELECT @LoopCount, @TestCount
IF @TestCount > 10
BREAK
ELSE
CONTINUE
SELECT @LoopCount, @TestCount
END
2. When the code is executed, we don’t actually make it around the 20 loops due to the value of @TestCount
causing the break. The output is shown in Figure 10-8.
344 CHAPTER 10 ■ STORED P ROCEDURES AND F UNCTIONS
Figure 10-8. WHILE with BREAK and CONTINUE
3. If we change the code to remove the ELSE CONTINUE statement, the second SELECT statement will be executed.
The two rows changed have been highlighted. We are not going to execute the two lines because they have been
commented out by prefixing the code with two hyphens, --.
DECLARE @LoopCount int, @TestCount int
SET @LoopCount = 0
SET @TestCount = 0
WHILE @LoopCount <>
BEGIN
SET @LoopCount = @LoopCount + 1
SET @TestCount = @TestCount + 1
SELECT @LoopCount, @TestCount
IF @TestCount > 10
BREAK
--- ELSE
--- CONTINUE
SELECT @LoopCount, @TestCount
END
A snapshot of some of the output from this is shown in Figure 10-9.
The third statement we’ll look at in this section is the CASE statement. While not a control-of-flow statement for your
stored procedure, it can control the output displayed based on decisions.
CHAPTER 1 0 ■ STORED P ROCEDURES AND FUNCT IONS 345
Figure 10-9. WHILE with BREAK only
CASE Statement
When a query has more than a plain true or false answer—in other words, when there are several
potential answers—you should use the CASE statement.
A CASE statement forms a decision-making process within a SELECT or UPDATE statement. It is
possible to set a value for a column within a recordset based on a CASE statement and the resultant
value. Obviously, with this knowledge, a CASE statement cannot form part of a DELETE statement.
Several parts of a CASE statement can be placed within a stored procedure to control the statement
executed depending on each scenario. Two different syntax structures exist for the CASE statement
depending on how you want to test a condition or what you want to test. Let’s take a look at all the
parts to the first CASE statement syntax:
CASE expression
WHEN value_matched THEN
statement
[[WHEN value_matched2 THEN]
[Statement2]]
...
...
...
[[ELSE]
[catch_all_code]
END
First of all, you need to define the expression that is to be tested. This could be the value of a variable,
a column value from within the T-SQL statement, or any valid expression within SQL Server. This
expression is then used to determine the values to be matched in each WHEN statement.
You can have as many WHEN statements as you wish within the CASE condition, and you do not
need to cover every condition or possible value that could be placed within the condition. Once a
condition is matched, then only the statements within the appropriate WHEN block will be executed.
Of course, only the WHEN conditions that are defined will be tested. However, you can cover yourself
for any value within the expression that has not been defined within a WHEN statement by using an
346 CHAPTER 10 ■ STORED P ROCEDURES AND F UNCTIONS
ELSE condition. This is used as a catchall statement. Any value not matched would drop into the ELSE
condition, and from there you could deal with any scenario that you desire.
The second syntax is where you don’t define the expression prior to testing it and each WHEN
statement performs any test expression you desire.
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
As just indicated, CASE statements form part of a SELECT, UPDATE, or INSERT statement, therefore
possibly working on multiple rows of data. As each row is retrieved from the table, the CASE statement
kicks in, and instead of the column value being returned, it is the value from the decision-making
process that is inserted instead. This happens after the data has been retrieved and just before the
rows returned are displayed in the results pane. The actual value is returned initially from the table
and is then validated through the CASE statement; once this is done, the value is discarded if no longer
required.
Now that you are familiar with CASE statements, we can look at them in action.
Try It Out: Using the CASE Statement
1. Our first example will demonstrate the first CASE syntax, where we will take a column and test for a specific
value. The results of this test will determine which action will be performed. We will prepopulate the
TransactionDetails.TransactionTypes table first so that you can see how populating this table and the
CASE statement work.
INSERT INTO TransactionDetails.TransactionTypes
(TransactionDescription,CreditType,AffectCashBalance)
VALUES ('Deposit',1,1)
INSERT INTO TransactionDetails.TransactionTypes
(TransactionDescription,CreditType,AffectCashBalance)
VALUES ('Withdrawal',0,1)
INSERT INTO TransactionDetails.TransactionTypes
(TransactionDescription,CreditType,AffectCashBalance)
VALUES ('BoughtShares',1,0)
SELECT TransactionDescription,
CASE CreditType
WHEN 0 THEN 'Debiting the account'
WHEN 1 THEN 'Crediting the account'
END
FROM TransactionDetails.TransactionTypes
2. Execute this code, and you should see the output shown in Figure 10-10.
CHAPTER 1 0 ■ STORED P ROCEDURES AND FUNCT IONS 347
Figure 10-10. Simple CASE statement output
3. A customer can have a positive or negative ClearedBalance. The CASE statement that follows will demonstrate
this by showing either In Credit or Overdrawn. In this case, we want to use the second CASE syntax.
We cannot use the first syntax, as we have an operator included within the test and we are not looking for a specific
value. The code is defined as follows:
SELECT CustomerId,
CASE
WHEN ClearedBalance <>
WHEN ClearedBalance > 0 THEN ' In Credit'
ELSE 'Flat'
END, ClearedBalance
FROM CustomerDetails.Customers
4. Execute the code. This produces output similar to what you see in Figure 10-11.
Figure 10-11. Searched CASE statement output
Bringing It All Together
Now that you have seen the control-of-flow statements, we can bring all of this together in our most
complex set of code so far. The aim of this stored procedure is to take a “from” and “to” date, which
can be over any period, and return the movement of a particular customer’s transactions that have
affected the cash balance. This mimics your bank statement when it says whether you have spent
more than you have deposited.
This example includes one topic that is not covered until the next chapter: joining data from
more than one table together. For the moment, just accept that when you see the statement JOIN, all
it is doing is taking data from another table and allowing you to work with it.
So let’s build that example.
348 CHAPTER 10 ■ STORED P ROCEDURES AND F UNCTIONS
Try It Out: Bringing It All Together
■Note In this example, we are performing a loop around rows of data within a table. This example demonstrates
some of the functionality just covered with decisions and control of flow. SQL Server works best with sets of data,
rather than a row at a time. However, there will be times that row-by-row processing like this happens. In SQL Server
2008, you have the option to write .NET-based stored procedures, and this example would certainly be considered a
candidate for this treatment. Our example works with one row at a time, where you would have a running total of a
customer’s balance so that you can calculate interest to charge or to pay.
1. First of all, let’s create our stored procedure. We have our CREATE PROCEDURE statement that we enter in an
empty Query Editor pane, and then we name the procedure with our three input parameters.
CREATE PROCEDURE CustomerDetails.apf_CustMovement @CustId bigint,
@FromDate datetime, @ToDate datetime
AS
BEGIN
2. We then need three internal variables. This stored procedure will return one row of transactions at a time while
we are still in the date range. As we move through each row, we need to keep a running balance of the amounts
for each transaction. We know that the data in the TransactionDetails.Transactions table has an ascending
TransactionId as each transaction is entered, so the next transaction from the one returned must have a
higher value. Therefore, we can store the transaction ID in a variable called @LastTran and use that in our filtering.
Once the variables are declared, we then set them to an initial value. We use @StillCalc as a test for
the WHILE loop. This could be any variable as we are using the CONTINUE and BREAK statements to determine
when we should exit the loop.
DECLARE @RunningBal money, @StillCalc Bit, @LastTran bigint
SELECT @StillCalc = 1, @LastTran = 0, @RunningBal = 0
3. We tell the loop to continue until we get no rows back from our SELECT statement. Once we get no rows, we
know that there are no more transactions in the date range.
WHILE @StillCalc = 1
BEGIN
4. Our more complex SELECT statement will return one row where the TransactionId is greater than the previous
TransactionId returned; the transaction would affect the customer’s cash balance; and the transaction is
between the two dates passed in. If there is a transaction, then we add or subtract the value from the @RunningBal
variable. We use a CASE statement to decide whether we need to make the value a negative value for adding to
the variable.
SELECT TOP 1 @RunningBal = @RunningBal + CASE
WHEN tt.CreditType = 1 THEN t.Amount
ELSE t.Amount * -1 END,
@LastTran = t.TransactionId
FROM CustomerDetails.Customers c
JOIN TransactionDetails.Transactions t ON t.CustomerId = c.CustomerId
JOIN TransactionDetails.TransactionTypes tt ON
tt.TransactionTypeId = t.TransactionType
CHAPTER 1 0 ■ STORED P ROCEDURES AND FUNCT IONS 349
WHERE t.TransactionId > @LastTran
AND tt.AffectCashBalance = 1
AND DateEntered BETWEEN @FromDate AND @ToDate
ORDER BY DateEntered
5. If we get a row returned, then we continue the loop. Once we get no rows returned, we know that there are no
further transactions in the date range.
IF @@ROWCOUNT > 0
-- Perform some interest calculation here...
CONTINUE
ELSE
BREAK
END
SELECT @RunningBal AS 'End Balance'
END
GO
6. We can now create the stored procedure and test our results. The example is going to check whether Vic McGlynn,
customer ID 1, has had a positive or negative movement on her cash balance in the month of August 2008. The
code to find this out follows. First of all, we insert some TransactionDetails.Transactions records to test
it out. We also prefix the stored procedure with an EXEC(UTE) statement, as this is part of a batch of statements.
INSERT INTO TransactionDetails.Transactions
(CustomerId,TransactionType,DateEntered,Amount,RelatedProductId)
VALUES (1,1,'1 Aug 2008',100.00,1),
(1,1,'3 Aug 2008',75.67,1),
(1,2,'5 Aug 2008',35.20,1),
(1,2,'6 Aug 2008',20.00,1)
EXEC CustomerDetails.apf_CustMovement 1,'1 Aug 2008','31 Aug 2008'
7. Execute the preceding code, which should return a value that we expect, as shown in Figure 10-12.
Figure 10-12. Complex stored procedure output
User-Defined Functions
As you have just seen, a stored procedure takes a set of data, completes the work as required, and
then finishes. It is not possible to take a stored procedure and execute it within, for example, a SELECT
statement. This is where user-defined functions (UDFs) come about. There are two methods of
creating UDFs: through T-SQL or .NET. Both provide the same functionality, which takes a set of
information and produces output that the query invoking the function can further use. UDFs are
very similar to stored procedures, but it is their ability to be used within another query that provides
their power. You have already seen a few system-defined functions within this book, including
GETDATE(), which gets today’s date and time and returns it within a query such as SELECT GETDATE().
350 CHAPTER 10 ■ STORED P ROCEDURES AND F UNCTIONS
■Tip If you want to learn more about .NET-based functions, take a look at Pro SQL Server 2005 Assemblies by
Julian Skinner and Robin Dewson (Apress, 2005).
Scalar Functions
Functions come in two types: scalar and table-valued. The following shows the basic syntax to define
a scalar function:
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name _data_type [ = default ] [ READONLY ] } [ ,...n ] ] )
RETURNS return_data_type
[ WITH
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
Note that zero, one, or more parameters can be passed in to the function. Prefix each parameter
in the definition with the local variable definition @ sign, and define the data type. Every parameter
can be modified within the function as part of the function’s execution, unless you place the keyword
READONLY after the data type. Also, as with stored procedures, it is possible to call a function without
specifying one or more of that function’s parameters. However, you can only do that if the parameters
that you omit have been defined to have default values. In that case, you can call the function with
the keyword DEFAULT in the location that the parameter is expected. The use of default values is
demonstrated within the example that follows.
A scalar function can only return a single value, and the RETURNS clause in the definition defines
the type of data that will be returned. All data types, with the exception of the timestamp data type,
can be returned.
The contents of a function are similar to a stored procedure, with the exceptions already discussed.
You must place a RETURN statement when you want the function to complete and return control to the
calling code.
Table-Valued Functions
The basic syntax for a table-valued function follows. Most of the syntax is similar; however, this time,
you’re returning a TABLE data type, and the data to return is defined in terms of a SELECT statement.
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name parameter_datatype [ = default ] [ READONLY ] } [ ,...n ] ]
RETURNS TABLE
[ WITH
[ AS ]
RETURN [ ( ] select_stmt [ ) ]
A table function is not built in this chapter, but will be completed in Chapter 12, as I want to
show more advanced T-SQL with this functionality, and you need to read Chapter 11 before I can do
that.
CHAPTER 1 0 ■ STORED P ROCEDURES AND FUNCT IONS 351
■Note It is also possible to define a stored procedure to receive a TABLE data type as an input-only parameter.
Considerations When Building Functions
Functions must be robust. If an error is generated within a function, whether it is from invalid data
being passed in or from errors in the logic, then the function will stop executing at that point, and the
T-SQL calling the function will cancel. A function must also not alter any external resource such as a
table, and it must not execute a system function that alters resources, such as a function that sends
an e-mail. Finally, you need to know whether a function can be used in computed columns.
Once a column is added to a table, there are five Boolean value-based properties that you can
inspect, listed shortly, that are assigned to a function by SQL. The values of the properties can be
checked by using the COLUMNPROPERTY function once the function has been added to a column. However,
once a function has been built, you can check its suitability using the OBJECTPROPERTY function to
check whether it is deterministic. You will see the OBJECTPROPERTY soon.
If you wish to use OBJECTPROPERTY or COLUMNPROPERTY, the function call is the same. The syntax is
as follows:
SELECT COLUMNPROPERTY (OBJECT_ID('schema.table'),
'columnname', 'property')
SELECT OBJECTPROPERTY(OBJECT_ID('schema.object'), 'property')
Here are the five properties you can check against a computed column:
• IsDeterministic: If you call the function and it returns the same value every time, then you
can define the function as being deterministic. GETDATE() is not deterministic, as it returns a
different value each time.
• IsPrecise: A function returns this value to determine if it is precise or imprecise. For example,
an exact number is precise, but a floating-point number is imprecise.
• IsSystemVerified: If SQL Server can determine the values of the first two properties, this will
be set to true; otherwise, it will be set to false.
• SystemDataAccess: This is true if any system information is accessed.
• UserDataAccess: This is true if any user data from the local instance of SQL Server is used.
SQL Server defines whether a column is deterministic and whether the result from the function
produces a precise value or an imprecise value. Also, unless you specify the PERSISTED keyword when
defining a column, the values will not be stored in the table but rather will be recalculated each time
the row is returned. There are, of course, valid scenarios for having the column computed each time,
but you have to be aware that there will be a small performance overhead with this. By defining the
column with the PERSISTED keyword, the value will be stored in the table and will only change when
a value in one of the columns used to perform the calculation alters. So there is a trade-off with space
and speed.
In the following exercise, you will build a scalar function to calculate an amount of interest either
gained or lost based on an amount, an interest rate, and two dates. Once the function is built, you will
then see a simple usage of the function and check its deterministic value. In Chapter 12, when your T-SQL
knowledge is advanced, you will then use this function against the TransactionDetails.Transactions
table to calculate interest for every transaction entered.
352 CHAPTER 10 ■ STORED P ROCEDURES AND F UNCTIONS
Try It Out: A Scalar Function to Calculate Interest
1. The first part of creating a function is to define its name, including the schema it will belong to, and then the paramater
values that will be coming into it. This function will calculate the amount of interest from a defined rate,
and will use two dates for the number of days the interest shall last. The first parameter, the interest rate, has a
default value of 10, defining 10%.
CREATE FUNCTION TransactionDetails.fn_IntCalc
(@InterestRate numeric(6,3)=10,@Amount numeric(18,5),
@FromDate Date, @ToDate Date)
2. Next, you need to define what data type is to be returned. In this instance, it is a numeric data type with up to five
decimal places. This granularity may not be required for you, but it is defined here so that in an audit situation,
accurate global summation of interest can be accrued (recall the example about interest earlier?).
RETURNS numeric(18,5)
3. In this example, EXECUTE AS specifies that the function will execute in the same security context as the calling
code. This security context is determined by the AS CALLER clause. It is possible to alter the security context of
EXECUTE AS to another account. Doing so is ideal when you want to ensure that no matter what the account is
that is connected, the function can be called. Conversely, you can set up a function so that only specific accounts
can execute the code.
WITH EXECUTE AS CALLER
4. Now that the preliminaries have been dealt with, we can move on to building the remainder of the function. A
local variable that will hold the interest is defined using the same data type and size as the RETURNS definition.
Then the variable is set using the calculation required to calculate the interest.
AS
BEGIN
DECLARE @IntCalculated numeric(18,5)
SELECT @IntCalculated = @Amount *
((@InterestRate/100.00) * (DATEDIFF(d,@FromDate, @ToDate) / 365.00))
5. Finally, the RETURN statement returns the calculated value, taking into account whether a NULL value is
being returned.
RETURN(ISNULL(@IntCalculated,0))
END
GO
6. You can now test the function by executing it against a set of values. The interest rate default value demonstrates
how to specify default parameter values when invoking a function. The results are showing in Figure 10-13.
SELECT TransactionDetails.fn_IntCalc(DEFAULT,2000,'Mar 1 2008','Mar 10 2008')
Figure 10-13. Inline function results for interest
CHAPTER 1 0 ■ STORED P ROCEDURES AND FUNCT IONS 353
7. It is now possible to check if the function is deterministic using the OBJECTPROPERTY function. This returns a
value of 0, or FALSE, because this function returns a different value each time. Therefore, this function could not
be used as a computed column.
SELECT OBJECTPROPERTY(OBJECT_ID('TransactionDetails.fn_IntCalc'),
'IsDeterministic');
GO
In Chapter 12, when you’ll see more advanced T-SQL, this function will be updated to calculate interest for customer transactions.
Also in Chapter 12, you will see how to build an inline table-valued function.
Summary
In this chapter, you have met stored procedures and functions, which are collections of T-SQL statements
compiled and ready to be executed by SQL Server. You have learned the advantages of a stored
procedure over an ad hoc query, encountered the basic CREATE PROCEDURE and CREATE FUNCTION
syntaxes, and created some simple stored procedures and functions.
The basics of building a stored procedure are very simple and straightforward. Therefore, building
a stored procedure within Query Editor may be as attractive as using a template. As stored procedures are
sets of T-SQL statements combined together, you will tend to find that you build up your query, and
then at the end surround it with a CREATE PROCEDURE statement.
You have seen both in-line and table T-SQL-based functions. To reiterate, it is possible to also
have functions that are written using .NET code, which provides you with more possibilities regarding
functionality and other processing abilities.
Probably the largest area of code creation outside of data manipulation and searching will be
through control-of-flow statements. We will look at other areas, such as error handling, in Chapter 11,
which aims to advance your T-SQL knowledge.
Nenhum comentário:
Postar um comentário