IntroductionWelcome| 00:04 | Hi! I'm Martin Guidry and welcome to SQL Server:
Triggers, Stored Procedures, and Functions.
| | 00:10 | I'll start by showing you how to create
stored procedures using T-SQL or C# code.
| | 00:16 | Then show you how to use SQL
Server Management Studio to simplify
| | 00:19 | database development.
| | 00:21 | We'll see how to create triggers using
SQL Server Management Studio and we'll
| | 00:25 | take a look at the Common Language
Runtime and how it enables us to create more
| | 00:29 | powerful, faster performing
stored procedures, and functions.
| | 00:32 | We'll end with using Visual Studio to
create a web application that utilizes the
| | 00:37 | store procedures created earlier in the course.
| | 00:40 | We'll be covering all these features
plus best practices and techniques for
| | 00:44 | avoiding common problems.
| | 00:46 | Now let's get started with SQL
Server: Triggers, Stored Procedures, and Functions.
| | Collapse this transcript |
| What you should know| 00:00 | To get the most out of this
course you should have a little
| | 00:03 | background knowledge.
| | 00:04 | Specifically, I expect you to know the
basics of any version of Microsoft SQL Server.
| | 00:09 | You will need to have some familiarity
with SQL Server Management Studio and
| | 00:14 | some knowledge of the T-SQL language.
| | 00:17 | Things like UPDATE, INSERT,
and DELETE statements.
| | 00:19 | A great way to pick up this knowledge is
SQL Server 2008 Essential Training with
| | 00:25 | Simon Allardice here in the
lynda.com online training library.
| | 00:30 | Also, in the last two sections of the
course we will be running some C# code.
| | 00:35 | To get the most out of these sections
you should be familiar with the basics of
| | 00:39 | C# as well as the basics of Visual Studio.
| | 00:43 | These topics are covered in C#
Essential Training with Joe Marini also in the
| | 00:48 | lynda.com online training library.
| | Collapse this transcript |
| Using the exercise files| 00:00 | The exercise files for this course
are organized into folders by chapter.
| | 00:05 | Each chapter has an individual
exercise file for the individual section.
| | 00:10 | So for example Ch03 section 05
exercise file looks like this.
| | 00:15 | This is stored in a text file
even though it's actually SQL code.
| | 00:19 | We will be copying and pasting this code
into Management Studio and run into there.
| | 00:25 | Don't do it just yet.
| | 00:26 | I know you're anxious to get started,
but go ahead and wait until we get to the
| | 00:30 | individual section before
you run in any of this code.
| | Collapse this transcript |
|
|
1. Getting StartedComparing triggers, functions, and procedures| 00:00 | Before we get into creating triggers,
functions, and stored procedures let's
| | 00:05 | talk about the similarities and differences.
| | 00:07 | First, the similarities.
| | 00:09 | All of these are database objects containing
code that will be executed as a single unit.
| | 00:15 | These things are similar to what
other development platforms might call a
| | 00:18 | method, a subroutine, or a module.
| | 00:21 | Some other platforms also use the term
procedure and function, similar to the way
| | 00:25 | SQL Server uses those words.
| | 00:27 | In SQL Server, triggers, functions,
and stored procedures are typically
| | 00:31 | written in a T-SQL, but they can
also be implemented in any of the .NET
| | 00:35 | languages like C# or VB.NET.
| | 00:39 | Now let's talk a little
bit about the differences.
| | 00:41 | Triggers and stored procedures are
allowed to change data and commonly do change
| | 00:46 | data, whereas a function is
not allowed to change any data.
| | 00:49 | The point of a function
is always to return data.
| | 00:54 | Sometimes it returns a single scalar
value, sometimes it returns a table.
| | 00:58 | Triggers never return data.
| | 01:00 | A trigger has to accomplish all of
its work by changing data and tables and
| | 01:05 | there is no return value from a trigger.
| | 01:08 | Stored procedures are somewhere in-between.
| | 01:10 | Typically, the point of a stored
procedure is to change data in the underlying
| | 01:14 | tables, but it is allowed to
return a value if it wants to.
| | 01:17 | It commonly returns a 0 or 1
to indicate success or failure.
| | 01:22 | Probably, the biggest difference
between the three is how they are called.
| | 01:26 | The easiest to work with is a stored procedure.
| | 01:30 | For a stored procedure, we just simply
execute it directly from code using the
| | 01:35 | phrase EXEC or the full word EXECUTE
and then the name of the stored procedure.
| | 01:41 | This will cause a stored
procedure to run at that time.
| | 01:44 | We cannot use this
technique with the other ones.
| | 01:47 | So a function has to be
part of a SELECT statement.
| | 01:51 | So we see here we have a typical SELECT
statement, calling first name and last
| | 01:57 | name, but if I wrote a function that
combined that into full name, I could just
| | 02:02 | use that in line with the SELECT statement.
| | 02:05 | I can use a function in the
column list of a SELECT statement.
| | 02:10 | I can also use it in the WHERE clause.
| | 02:12 | I could also use it in the ORDER BY
clause of a single SELECT statement.
| | 02:20 | These are all functions
that return scalar values.
| | 02:23 | It's also possible for a function to
return an entire table and that will
| | 02:27 | replace the table name here.
| | 02:29 | We are not limited to just SELECT statements.
| | 02:34 | we can also do the same thing with
UPDATE, INSERT, and DELETE statements.
| | 02:41 | So something like this where we're using
the function to filter a DELETE statement.
| | 02:47 | Calling triggers is yet again a third technique.
| | 02:50 | There is no way for me to issue a command
that causes a trigger and just a trigger to run.
| | 02:56 | A trigger always runs as
a reaction to something.
| | 02:59 | So I do something else on my machine,
the machine performs that action, and then
| | 03:03 | afterwards runs the trigger.
| | 03:06 | Most commonly, triggers react to
UPDATE, INSERT, or DELETE statements.
| | 03:10 | So if I have a trigger set up on a
table and I run an insert statement on that
| | 03:15 | table, immediately after that insert the
trigger will take over and execute its code.
| | 03:21 | There is no way to just type in
and save run this trigger now.
| | Collapse this transcript |
| Why use a stored procedure?| 00:00 | Now I'd like to talk about some of the
advantages of using stored procedures.
| | 00:04 | One of the primary advantages is to
simplify the administration and maintenance
| | 00:09 | for common routines.
| | 00:11 | On a large database project we could
have dozens of developers writing code to
| | 00:15 | read and manipulate data in the same table.
| | 00:18 | If each developer independently writes
a simple INSERT statement for the table,
| | 00:22 | there is a large chance
someone will make an error.
| | 00:25 | Also, if that table ever changes,
every single developer has to go back
| | 00:30 | and change their code.
| | 00:31 | However, if we wrote one insert stored
procedure and allowed all the developers
| | 00:36 | to use that stored procedure,
we gain some advantages.
| | 00:39 | First, we reduce the chances of error.
| | 00:42 | We also reduce the level of
effort when a change is required.
| | 00:45 | We can simply change the stored procedure.
| | 00:48 | As long as the name of the stored
procedure and the input parameters of
| | 00:51 | the stored procedure stay the same,
the developers will likely have no
| | 00:54 | need to change their code.
| | 00:56 | Another advantage is, with stored
procedures, our code is stored with the data.
| | 01:02 | I typically have common task I
regularly perform on a database.
| | 01:05 | Rather than type in that code everytime
I want to run it, I prefer to type it in
| | 01:10 | once and save it for the next time.
| | 01:12 | Certainly, I could create a code folder on
my desktop and store all the scripts there.
| | 01:17 | But I prefer to create a stored procedure.
| | 01:20 | That way the code is stored in the database.
| | 01:22 | Which reduces the chances the
code will ever be misplaced, and it also
| | 01:26 | provides the advantage of every time the
database is backed up, all of my code is backed up.
| | 01:31 | Stored procedures can improve our security
and simplify the administration of security.
| | 01:37 | Using stored procedures we can
easily provide different levels of access
| | 01:41 | to different users.
| | 01:43 | I often prevent any user from
directly accessing any table.
| | 01:47 | The only way they can read or modify the
data is to utilize my stored procedure.
| | 01:52 | This technique makes it very
simple to prevent certain actions.
| | 01:55 | For example, if I don't want a certain
user to delete any data from a particular
| | 02:00 | table, I can just deny them access to
the delete stored procedure, or if I don't
| | 02:06 | want anyone deleting data, I'd just
never implement a delete stored procedure.
| | 02:11 | They won't have access to the underlying table.
| | 02:14 | They won't be able to go there directly.
| | 02:16 | And saving the best for last, the most
important advantage of using a stored
| | 02:20 | procedure is a performance improvement.
| | 02:23 | To understand this performance
improvement we'll have to talk a little bit about
| | 02:27 | what happens every time
you run code in SQL Server.
| | 02:31 | If I just type in a query, when I go
to run it, the machine performs several
| | 02:36 | steps before it runs the query.
| | 02:39 | First, the information is parsed.
| | 02:42 | Parsing means checking to
make sure my syntax is good.
| | 02:45 | I haven't misspelled a keyword
or used a keyword that's invalid.
| | 02:50 | Next, my code is algebrized and
that means it changes object names to
| | 02:56 | a consistent format.
| | 02:57 | So for example, the categories table
could be referenced just as categories
| | 03:03 | or it could be referenced as dbo.categories,
or we can even put the name of
| | 03:08 | the database first.
| | 03:10 | So with all of these different options
on how to name things, the machine has to
| | 03:14 | standardize all of the names in a
consistent format before it can run it.
| | 03:19 | The third step is optimizing and
optimizing the database decides which
| | 03:24 | data structures to use.
| | 03:26 | It will have to enumerate a
list of all the tables to be read.
| | 03:29 | It will decide which
order it reads the tables in.
| | 03:32 | It will look for indexes that could
help in other optimization strategies.
| | 03:37 | After the machine performs these three steps:
| | 03:40 | parse, algebrize, and optimize, it
results in an execution plan and it
| | 03:45 | immediately executes that execution plan.
| | 03:49 | If I were to run this exact query
again, it starts over from the top.
| | 03:54 | It will parse it, it will algebrized it,
it will optimize it, it will come up
| | 03:58 | with an execution plan, and it
will execute the execution plan.
| | 04:02 | That is somewhat displeasing to me
that it executed all those steps again.
| | 04:07 | Apparently, someone at Microsoft had
same feelings as me and stored procedures
| | 04:11 | bypass some of this.
| | 04:13 | So the very first time I run a stored
procedure, it does go through all those steps.
| | 04:17 | But when it creates the execution plan, it
also saves the execution plan at that time.
| | 04:23 | So the next time I run the stored
procedure it can skip the parsing, it can skip
| | 04:28 | algebrizing, it can skip the optimizing.
| | 04:31 | It goes directly to the saved execution
plan and just executes that immediately.
| | 04:36 | This can result in a significant
performance improvement, typically in the
| | 04:40 | optimizing stage is where
we see the most improvement.
| | 04:43 | If you have a large database with
lots of indexes rather than the machine
| | 04:47 | having to make a decision on which
index to use every time, all of that can be
| | 04:53 | saved in the execution plan, and we can
see a significant performance increase
| | 04:57 | from a stored procedure.
| | Collapse this transcript |
| Why use functions?| 00:00 | Now let's talk about some of
the advantages of using functions.
| | 00:04 | Functions have most of the same
benefits of stored procedures.
| | 00:07 | They can simplify administration
and maintenance of common routines.
| | 00:11 | The code is stored with the data.
| | 00:13 | They do have some of the same security
options as stored procedures, but people
| | 00:18 | don't use these quite as commonly
as they do with stored procedures.
| | 00:21 | And we also have all of the performance
improvements of the stored execution plan.
| | 00:27 | Additionally, functions
have some other benefits.
| | 00:31 | In line functions can be
very easy and intuitive.
| | 00:35 | A developer who is familiar with select
statements can quickly adapt to a select
| | 00:40 | statement written with functions
rather than with typical database objects.
| | 00:44 | It's just not that big of a jump to go
from a mindset of not using functions
| | 00:49 | to a mindset of using functions, because
the code is still structured so similarly.
| | 00:54 | Functions can also be a quick
way to abstract some complex ideas.
| | 00:58 | So something like formatting an address.
| | 01:01 | Well, if you have customers from
dozens of different countries that all need
| | 01:05 | their address formatted different ways,
| | 01:08 | we don't want our developers to spend
lots and lots of time writing that code
| | 01:12 | every time they need an address.
| | 01:14 | Creating a function that formats the
addresses properly for whatever country can
| | 01:20 | allow the developer to move past that
complexity and just focus on other tasks.
| | Collapse this transcript |
| Why use triggers?| 00:00 | Let's talk little bit about some
of the advantages of using triggers.
| | 00:04 | Triggers can be used to
audit changes to the database.
| | 00:08 | We can set up triggers on tables to record
every change to every record in the table.
| | 00:13 | Typically, this is accomplished by copying the
record before it's modified to a different table.
| | 00:18 | A table we might call historical data.
| | 00:21 | Triggers can also be used to
ensure the integrity of our data.
| | 00:25 | SQL Server has some other ways to do
this such as checks and constraints.
| | 00:30 | But those are designed to be pretty simple.
| | 00:32 | They check for a data type or they
might check to make sure some value is
| | 00:36 | greater than or less than some other value.
| | 00:40 | Triggers allow for more robust logic.
| | 00:42 | We can generate data integrity rules
that implement complex business logic,
| | 00:47 | that can reference multiple tables
based on the value in those tables, decide if
| | 00:52 | a certain piece of data is valid or invalid.
| | 00:56 | Triggers can also be used to block mistakes.
| | 00:58 | Triggers can undo an attempt to change data.
| | 01:01 | They can even undo an attempt to
change the structure of the database.
| | 01:06 | So something like dropping a
table can be blocked by a trigger.
| | Collapse this transcript |
|
|
2. Installing SQL ServerConfiguring your environment| 00:00 | Before I get into my
demonstrations, I'd like to help you set up a
| | 00:04 | development environment very similar
to what I'm using so you can perform
| | 00:08 | these steps at home.
| | 00:09 | We are going to be using two products:
| | 00:12 | Microsoft SQL Server 2012 Express
Edition and Microsoft Visual Web Developer
| | 00:18 | 2010 Express Edition.
| | 00:21 | First let's look at SQL
Server 2012 Express Edition.
| | 00:26 | You can find that add the URL
www.microsoft.com/express/database.
| | 00:35 | This takes me to the standard landing
page for SQL Server Express Edition.
| | 00:40 | I have a big red button in
the middle of the screen.
| | 00:43 | I'll click on that and it
shows me some different choices.
| | 00:47 | You definitely want to get one of the
versions that has the database engine and
| | 00:52 | the tools include Sequels Server
Management Studio which is an important tool
| | 00:56 | in many of our demos.
| | 00:58 | So I'm going to with the top one here.
| | 01:02 | That takes me to another page where I'll
select the 64-bit edition Express with Tools.
| | 01:13 | Now that we've downloaded SQL Server
2012 Express Edition let's go ahead and install it.
| | 01:19 | So in this interface I want the
top option New SQL Server stand-alone
| | 01:23 | installation, and I'd like to perform a
new installation of SQL Server 2012, so
| | 01:30 | the default there is
appropriate. I'll click Next.
| | 01:34 | I need to read and accept the
license and then click Next.
| | 01:40 | I need to choose what
features I'm going to install.
| | 01:44 | I definitely want the Management Tools.
| | 01:47 | The SQL Server Replication is not
necessary for anything we're going to do in
| | 01:51 | the course, but you could install
if you want though. Click Next.
| | 01:58 | At this point I'd like to name this instance.
| | 02:01 | You can use whatever name you want,
probably do something descriptive.
| | 02:05 | I can choose a folder to install to.
| | 02:07 | I'm going to accept the default and click Next.
| | 02:14 | Here it's asking me which
service accounts I would like to use.
| | 02:17 | In a tested environment I
typically accept the defaults here.
| | 02:21 | If you'd like to use something other
than the default accounts, you may do so.
| | 02:28 | This interface is asking me which
authentication mode I would prefer.
| | 02:32 | One of our demos will
require SQL Server authentication.
| | 02:35 | So I'm going to click Mixed Mode
to allow SQL Server authentication.
| | 02:40 | That will force me to import
a password for the SA account.
| | 02:45 | So I'm going to of when
to use a secure password.
| | 02:47 | It also has automatically added the
current user as an administrator of SQL Server.
| | 02:53 | That's typically something you'll want to do.
| | 02:55 | If you want to add additional users, you
can do so in the interface at the bottom.
| | 03:01 | Asking me whether or not I want to
send error reports to Microsoft, I'll just
| | 03:05 | click Next and now we are installing.
| | 03:09 | I should mention we are
installing SQL Server version 2012.
| | 03:14 | Many of the demos I'll be doing would
work exactly the same in 2008 or 2005.
| | 03:21 | So if we have that kind of environment
already set up, you could stick with that.
| | 03:25 | I also anticipate that the majority of
the stuff in the course will not change
| | 03:29 | in a future version.
| | 03:31 | So when Microsoft releases the next
version of SQL Server, I can't be 100% sure
| | 03:37 | but I imagine the basics of triggers,
functions, and stored procedures will
| | 03:41 | still work the same.
| | 03:43 | In this sense your SQL Server 2012
installation completed successfully
| | 03:46 | with product updates.
| | 03:48 | Sounds like good news.
| | 03:50 | I'll close this and then I can also
close this window and we have successfully
| | 03:55 | installed SQL Server.
| | 03:57 | Now it's time to install Microsoft
Visual Studio Web Developer 2010 Express.
| | 04:03 | You can get this from
www.microsoft.com/express.
| | 04:10 | This takes me to a landing page for
Visual Studio 2010 Express products.
| | 04:14 | There are a few different
versions. I'll scroll down.
| | 04:17 | The one I'm interested in is all
the way here to the right, Visual Web
| | 04:22 | Developer 2010 Express.
| | 04:30 | I'll click Install at the bottom
and I'll need to accept the licensing
| | 04:35 | agreement, and here we have a
message saying the following products were
| | 04:39 | successfully installed.
| | 04:40 | That sounds like good news.
| | 04:42 | I'll click on Finish and I'll click in
Exit and we have successfully installed
| | 04:48 | Microsoft Visual Web
Developer 2010 Express Edition.
| | Collapse this transcript |
| Downloading and installing a sample database| 00:00 | Now that we've installed SQL Server
let's go ahead and set up a test database
| | 00:04 | to run some demos on.
| | 00:06 | I've already staged some code for
you in your Exercise Files folder.
| | 00:11 | The one says create_database, just copy
and paste all of that into Management Studio.
| | 00:18 | You notice near the top it's going to
try to create some files in C:\program
| | 00:23 | files\Microsoft SQL Server,
et cetera, et cetera.
| | 00:27 | This is the default path
for database and log files.
| | 00:31 | You're welcome to use a
different path if you want to.
| | 00:33 | What I have on the screen
here just represents the default.
| | 00:36 | So we'll go ahead and run that.
| | 00:38 | Command completed successfully.
| | 00:41 | And that gives us a new
database called myDatabase.
| | 00:44 | We do need to add one table to that
database and populate it with just a
| | 00:49 | little bit of data.
| | 00:50 | We have script for that also.
| | 00:53 | So again I'll copy and paste that over,
and execute, and we see now we have an
| | 01:00 | Authors table and we should have of a few
records in that Authors table. It looks good.
| | 01:07 | I think we're ready to go.
| | Collapse this transcript |
|
|
3. Stored ProceduresCreating a stored procedure| 00:00 | I'd like to talk to you little bit
about creating stored procedures in
| | 00:04 | Microsoft SQL Server.
| | 00:05 | To do this we're going to start of with the
keyword CREATE and then the keyword PROCEDURE.
| | 00:11 | After that we need to give it a name.
| | 00:13 | Well, we have lots of options on naming.
| | 00:15 | Almost any combination of letters and
numbers and punctuation marks will work.
| | 00:19 | One of the few exceptions is you are
not allowed to start with a numeral.
| | 00:23 | So if I try to type in numerals at the
beginning, the machine will get upset
| | 00:27 | with that and put that
little red squiggly line in there.
| | 00:30 | That is because you're not
allowed to start with numerals.
| | 00:33 | You are allowed to end with numerals or
have numerals anywhere else in the name.
| | 00:38 | But just as a matter of preference I
typically avoid both punctuation marks and
| | 00:42 | numerals and go with just text.
| | 00:44 | Once we've decided on a name we will
need the keyword AS and the keyword BEGIN.
| | 00:50 | I typically put those on separate
lines, but that's not necessary.
| | 00:53 | You could put all of this on one line.
| | 00:55 | I just find it makes it a little
easier to read spaced out like this.
| | 00:59 | After BEGIN, we have to actually do the
work of the stored procedure and just
| | 01:03 | about any T-SQL statement is valid.
| | 01:06 | We're going to start off easy and
just do a very simple one line that
| | 01:10 | will return some text.
| | 01:12 | Then in single quotes the word hello.
| | 01:15 | At the end of the stored procedure we
need to type in the keyword END and
| | 01:20 | that signifies to the machine this
is the end of our stored procedure.
| | 01:23 | We will then click on the red
exclamation point for Execute and we get at the
| | 01:28 | bottom a green check mark that says
Query executed successfully, and the phrase
| | 01:34 | command completed successfully.
| | 01:36 | So it would certainly
seem as we were successful.
| | 01:38 | We've got two forms of positive feedback.
| | 01:40 | The stored procedure is
now stored in our database.
| | 01:43 | We can see that by expanding
the myDatabase menu to the left.
| | 01:47 | Then we expand Programmability.
| | 01:50 | Inside of there we will have Stored Procedures.
| | 01:52 | It's not coming up yet.
| | 01:53 | I bet if I click Refresh, there it is,
a stored procedure called myTest.
| | 02:00 | So there we can see the stored procedure exists.
| | 02:03 | The next logical thing to do would be
to execute the stored procedure, and in
| | 02:07 | order to execute you can just type in
the keyword EXECUTE and then the name of
| | 02:13 | the stored procedure, click the red
exclamation point again and we see our
| | 02:17 | data returned to us.
| | 02:18 | We ask the stored
procedure to return the word hello.
| | 02:22 | It did in fact return the word hello.
| | 02:24 | So it seems as if things are going well for us.
| | 02:27 | The keyword execute can be abbreviated.
| | 02:29 | You can type in just EXEC and it
will accomplish the exact same thing.
| | 02:35 | So that's an alternative way
to execute our stored procedure.
| | 02:38 | So these are the basics of
creating a stored procedure.
| | 02:41 | In the next section we will work
on modifying a stored procedure.
| | Collapse this transcript |
| Modifying a stored procedure| 00:00 | Now that we've created a stored procedure, it's
likely I would want to change it at some point.
| | 00:06 | The easiest way to modify a stored
procedure is to come back to our favorite menu,
| | 00:09 | the Programmability menu, open that up,
locate the stored procedure we would
| | 00:14 | like to change, right-click on it, and
one of the options in the right-click
| | 00:18 | menu will be Modify.
| | 00:20 | When I click on Modify, it
throws some code up on the screen.
| | 00:23 | Notice that lines 8 through 12 here
are extremely similar to what I wrote.
| | 00:30 | The only difference is rather than have
the keyword CREATE, it has changed it to
| | 00:35 | the keyword ALTER, which makes a lot of sense.
| | 00:37 | We are not trying to create a new
stored procedure anymore, we are trying to
| | 00:41 | modify a stored procedure, and in the
SQL world, we use the word alter for when
| | 00:47 | we want to change things.
| | 00:48 | So I could make a small change to my
stored procedure, and click on the red
| | 00:53 | exclamation point again.
| | 00:55 | And it will say that it completed my
command successfully, which means the
| | 00:59 | stored procedure has been modified.
| | 01:01 | In order to see this, I
will need to execute it again.
| | 01:06 | So we will again do EXEC and the name
of the stored procedure, and we see it is
| | 01:11 | now giving the new text; the text
from the modified stored procedure which
| | 01:16 | reassures me we have
successfully modified the stored procedure.
| | 01:20 | I could go back to the other
window and change it again,
| | 01:24 | Execute and come back to my window where
I'm executing the stored procedure and
| | 01:29 | we see change again.
| | 01:31 | When you're working with stored
procedures, it's likely that you will be
| | 01:35 | modifying them often, and it would be
preferable to not have to bounce back and
| | 01:39 | forth between one window and the other.
| | 01:42 | I don't want to have to go to one
window to change my stored procedure, and a
| | 01:46 | different window to
execute the stored procedure.
| | 01:49 | We can do that all from one window.
| | 01:51 | I'll take this code here and a
little copy and paste to my other window.
| | 01:56 | And the only thing I need to do is in
between them add the keyword GO so that
| | 02:02 | the machine knows these are
two separate chunks of code.
| | 02:05 | Everything above the GO is used to
modify the stored procedure and then
| | 02:09 | everything after GO is used to
execute the stored procedure.
| | 02:13 | When I click Execute, it will run both
and so I can modify the stored procedure,
| | 02:20 | and we see the
modifications of that immediately.
| | 02:23 | So during development, this is
typically the way that I like to work.
| | 02:27 | It's the quickest and easiest way to
modify stored procedure, and immediately
| | 02:31 | see the results of those modifications.
| | Collapse this transcript |
| Returning data using data sets| 00:00 | Now I'd like to talk about
returning data through stored procedures,
| | 00:04 | specifically returning
data as datasets primarily.
| | 00:08 | We'll also talk a little
bit about a return value.
| | 00:10 | I have some code on the screen here
that is available in our exercise files.
| | 00:15 | This is very similar to what we
were doing in the previous section.
| | 00:18 | It's code that allows us to modify a stored
procedure and execute that stored procedure.
| | 00:23 | I'm going to modify the stored
procedure to have a return value.
| | 00:27 | So at the end, the very last thing
before the end statement, I will simply issue
| | 00:32 | the keyword Return, and give it a numeral.
| | 00:35 | So in this case, Return 1.
| | 00:37 | When we execute that, we will likely not
see that one returned just yet, we have
| | 00:42 | to go through a little more effort
to actually see what is returned.
| | 00:47 | We will declare a variable to hold that value.
| | 00:52 | We will then execute the stored
procedure, and take what is returned, and store
| | 00:58 | it in our newly created variable, and
then we'll need one last line of code to
| | 01:03 | display that returned value.
| | 01:07 | Go ahead and execute all of that,
and so we see we got two results in the
| | 01:12 | Results pane at the bottom;
| | 01:13 | one says, Hello World, this is fun
which is the data returned from the
| | 01:17 | stored procedure, and then also the
numeral that we returned, and in this
| | 01:21 | case, we returned 1.
| | 01:22 | We could change that easily to return a
different numeral and now it returns 12.
| | 01:28 | Microsoft recommends as a best practice
every stored procedure should return a
| | 01:33 | value. Typically you return
either 0 or 1 for success or failure.
| | 01:37 | So if the stored procedure failed for
some reason, you would return a 0, if it
| | 01:43 | was successful, you would return 1.
| | 01:45 | Different people like to have a little
different naming conventions for that.
| | 01:48 | For example, if the stored procedure
returned an error, you could return a code
| | 01:52 | that signifies an error in your
mind, maybe something like -1.
| | 01:55 | But the bottom line is Microsoft
recommends, and I also agree with this
| | 02:00 | recommendation, the last line of every
stored procedure should be returned a
| | 02:04 | numeral to me, and that
numeral should have some meaning.
| | 02:07 | Beyond returning numerals, you probably
want your stored procedure to return some data.
| | 02:12 | And we've been doing that;
| | 02:13 | we have Select, and then we're
returning some text. We can return multiple
| | 02:18 | datasets by having multiple SELECT statements.
| | 02:23 | So I'll have a second SELECT statement
that will return a second set of data.
| | 02:28 | Now at the bottom, we see that
we return three different things;
| | 02:32 | the first data set, below that the second
dataset, and then finally the return value.
| | 02:38 | You could additionally add a third
dataset, a fourth dataset, a fifth dataset.
| | 02:42 | The machine does not put an upper
limit on what we can and cannot return.
| | 02:46 | Good common sense says, you probably
want to limit what one stored procedure
| | 02:51 | returns, but two or three
datasets is not uncommon.
| | 02:55 | Notice I said that the return -1 or
return whatever numeral you have should be
| | 03:00 | the last line of the stored procedure.
| | 03:02 | Let's see what happens if you violate
that recommendation, and we put that in
| | 03:07 | the middle right here.
| | 03:09 | You notice it returned the first
dataset, "Hello World this is fun."
| | 03:12 | It returned the -1, it did not
return the phrase "This is more text."
| | 03:17 | The machine stops returning after
the line with the keyword RETURN.
| | 03:21 | It will perform the return on that line.
| | 03:24 | It will not do any returns after that.
| | 03:27 | So the keyword RETURN almost always
needs to be the very last thing in
| | 03:32 | your stored procedure.
| | 03:33 | So in this demonstration, we've seen
returning data from a stored procedure as
| | 03:38 | either a numeral or a dataset.
| | 03:40 | In our next video, we'll talk
about returning data as a cursor.
| | Collapse this transcript |
| Returning data using cursors| 00:00 | Next, I'd like to talk about returning
data from a stored procedure using a cursor.
| | 00:05 | A cursor is a data structure that
contains multiple rows of data, and it allows
| | 00:10 | us to cycle through each row one at a
time and perform an action on each row.
| | 00:16 | Returning a cursor from a stored
procedure is pretty similar to returning data
| | 00:20 | from a stored procedure using output parameters.
| | 00:22 | And in a lot of ways you can think of a
cursor as a special type of output parameter.
| | 00:27 | I have some code on the screen.
| | 00:29 | This code is available in your exercise files.
| | 00:32 | The top-half of this code is going to
create a stored procedure called procedure
| | 00:36 | cursor that accepts one parameter
that's called Authors, and that parameter has
| | 00:41 | a data type that is defined by two words;
| | 00:44 | one word is CURSOR and
the second word is VARYING.
| | 00:49 | Meaning, this cursor isn't
always going to be exactly the same.
| | 00:52 | It could be a different size based on
the data that's currently in the database.
| | 00:58 | This cursor will sometimes return a
different number of records than other times.
| | 01:02 | And then the last word is OUTPUT.
That's the same as we've had before because
| | 01:06 | this cursor will be used as an output parameter.
| | 01:09 | We still need the keyword OUTPUT.
| | 01:11 | In the body of the stored procedure on
line 4, we set the author's cursor equal
| | 01:16 | to a cursor, and then we put a SELECT statement.
| | 01:19 | So line 6, 7, and 8, I wrote a SELECT
statement that's just going to select the
| | 01:23 | first name from the author's table.
Just keeping it simple right now.
| | 01:27 | Line 10 is very important
where we open the author's cursor.
| | 01:32 | Notice in this stored procedure we never
close the author's cursor. We will close it;
| | 01:37 | we're just going to close it
outside of the stored procedure.
| | 01:40 | So in the stored procedure, we only open it.
| | 01:43 | On line 13, we have the keyword GO, and
I'm going to scroll down so we can see
| | 01:48 | what happens after this.
| | 01:49 | This is all the code to execute the
stored procedure, and get back that cursor.
| | 01:53 | So the top two lines are declaring
variables, then line 18 executes the stored
| | 01:59 | procedure, and takes the
output and puts it into my cursor.
| | 02:04 | Line 20 and 21, cycle through the
first record in my cursor, and then very
| | 02:08 | important, 23 and 24 close the
cursor, and de-allocate the cursor.
| | 02:14 | Close the cursor means it cannot be
used anymore, de-allocate means it is
| | 02:19 | removed from memory.
| | 02:20 | It's important to do these things because the
machine will not do them for you automatically.
| | 02:24 | If you do not specifically say de-
allocate the cursor, it will remain in memory
| | 02:29 | for quite a long time;
| | 02:31 | probably will remain there
until you reboot your SQL server.
| | 02:34 | So for the sake of efficiency,
you always want to de-allocate.
| | 02:38 | Let's go ahead and run this, and we
see the stored procedure is created and
| | 02:43 | it returns one record;
| | 02:45 | the first name of the first author in the table.
| | 02:48 | We could if we wanted to modify this
code at the bottom to do another fetch
| | 02:53 | to get the next record, and another fetch
to get the next record, so on, and so forth.
| | 02:58 | Or most likely, you would put it in a loop to
walk through all of the items in the cursor.
| | 03:04 | Some developers are very much against cursors.
| | 03:07 | Cursors can take up a lot of memory
space, obviously the amount of memory they
| | 03:11 | use is always proportional
to the size of the table.
| | 03:15 | So if you're working with a small
amount of data, cursors are a good idea.
| | 03:19 | If you're working with a very large table,
you might want to look at an alternative.
| | 03:24 | As mentioned earlier, there are
particular performance problems if you do not
| | 03:29 | remove the cursor from memory.
| | 03:31 | So again, the DEALLOCATE statement is
important and the DEALLOCATE statement has
| | 03:35 | to come after the CLOSE statement.
| | 03:38 | So when working with cursors,
remember, keep it small and always destroy
| | 03:43 | what you create.
| | Collapse this transcript |
| Using input and output parameters| 00:00 | Now I'd like to talk about sending
input parameters and returning output
| | 00:05 | parameters from a stored procedure.
| | 00:07 | These are optional.
| | 00:08 | We have worked with stored procedures
thus far without working with parameters,
| | 00:12 | so they are optional.
| | 00:13 | I have some code on the screen.
| | 00:15 | It's available in our exercise files.
| | 00:17 | It basically alters the stored procedure
and then executes the stored procedure.
| | 00:22 | So I am going to alter the stored
procedure to include a 1 input parameter for now.
| | 00:28 | I do that by creating some parentheses.
| | 00:30 | We will need to put all of
our parameters in parentheses.
| | 00:33 | Then I need to give the parameter a name.
| | 00:36 | And remember, in SQL Server, variable
names always start with the at sign, and
| | 00:42 | beyond that, you can name it almost
anything you would like to name it.
| | 00:45 | We will also need to declare
what datatype we're going to use.
| | 00:49 | Any of the typical SQL Server
datatypes are in play, so for example, integer,
| | 00:54 | varchar, datetime, all of
those are perfectly valid.
| | 00:57 | So I will declare this one as an integer.
| | 01:00 | So we're going to alter this
procedure to now accept one parameter.
| | 01:04 | That parameter is called @param1.
| | 01:07 | We can reference that parameter
throughout our code, and I will also alter our
| | 01:12 | stored procedure to select parameter 1.
| | 01:16 | So rather than select some arbitrary
text, it's going to select that parameter
| | 01:21 | that we passed to it.
| | 01:22 | The code on top is now ready to go.
| | 01:24 | It will successfully alter the stored procedure.
| | 01:26 | The code on the bottom will need to
be modified to pass it a parameter.
| | 01:30 | The stored procedure is
now expecting a parameter.
| | 01:33 | So we had better pass it a parameter and
that parameter had better be an integer.
| | 01:37 | So I will just pass it the
integer 12. We'll run that.
| | 01:41 | And it behaves as expected.
| | 01:44 | Our code passed the parameter 12 to
the stored procedure, and that exact
| | 01:48 | value was returned to us.
| | 01:50 | No surprises there. We'll call this a success.
| | 01:52 | A few little notes on the syntax, when
you are passing the parameter, you should
| | 01:57 | not use parentheses.
| | 01:59 | Although your instincts may tell you to
use parentheses, you will in fact get an
| | 02:03 | error if you use the parentheses.
| | 02:04 | So no parentheses when passing parameters.
| | 02:08 | And also like I mentioned, it
has to in fact be an integer.
| | 02:10 | If I try to pass it some text, the
machine will also get upset about that.
| | 02:16 | It says error converting from
varchar to integer and that is expected.
| | 02:21 | So that's the basics of an input parameter.
| | 02:23 | We could do multiple input parameters.
| | 02:26 | I will come back up here to my
parameter list, and put a single comma, and then
| | 02:31 | I can list a second parameter.
| | 02:33 | And again, it will need a datatype,
and again, I will have to come down here,
| | 02:37 | and make sure I am passing a second
parameter to it, and that runs successfully.
| | 02:44 | We haven't actually done anything
with the second parameter, so this becomes
| | 02:47 | mildly more interesting, if we go
ahead and display both parameter 1 and parameter 2.
| | 02:53 | So no surprises there.
| | 02:55 | We passed it the value 12 and Hello.
| | 02:57 | It returned the value 12
and Hello. This is good.
| | 03:00 | So now we've successfully worked with
input parameters and we want to talk a
| | 03:05 | little bit about output parameters.
| | 03:07 | So we want to take this
value that's being passed to it.
| | 03:10 | Parameter 1 has a value of 12 and we're
going to modify that value and then read
| | 03:16 | the value as it comes out.
| | 03:18 | In order to do this, I will need to
change a little bit of the code around
| | 03:21 | the execute statement.
| | 03:22 | Rather than just pass it the value
12, I want to pass it a variable.
| | 03:26 | I will declare a new variable, just
call it X, and we'll have to say that it is
| | 03:32 | an integer, and I will go
ahead and give it the value.
| | 03:35 | We will then pass that variable to the
stored procedure, and then we will read
| | 03:41 | the value of that variable.
| | 03:43 | And if all goes well, once we're
done with this, it will be changing the
| | 03:47 | value of that variable.
| | 03:48 | We haven't written the code for that just yet.
| | 03:50 | But we can see it successfully passes
the value of 13, and then reads the value
| | 03:55 | of 13, as we are now, it is expected.
| | 04:00 | So let's go ahead and try to
change the value of the parameter.
| | 04:03 | And here, we can say SET @param1
equal to some value other than 13;
| | 04:09 | I will just choose a random number, 27.
| | 04:11 | So what's going on here is lines 11, 12,
and 13, should pass the value 13 to
| | 04:18 | the stored procedure, the stored
procedure should change that to 27, and then
| | 04:22 | we can read that value.
| | 04:24 | This is not going to work just yet though.
| | 04:27 | There is one more step that is a
little counterintuitive to some people.
| | 04:30 | We have to actually declare the
parameter to be an output parameter.
| | 04:35 | SQL Server assumes all
parameters are input only.
| | 04:39 | So if you would like them to be
output, you have to specifically use the
| | 04:43 | keyword OUTPUT in line 1 where we
declare the stored procedure, we have to
| | 04:48 | specifically say OUTPUT.
| | 04:50 | And then again, on line 12 where we are
passing the parameter, we also have to
| | 04:57 | say specifically OUTPUT.
| | 04:59 | Now we should be able to
successfully return the value 27.
| | 05:03 | That is in fact what's
happening. We've had success.
| | 05:06 | So again, parameters are
assumed to be input values.
| | 05:11 | If you would like them to be both input
and output, you have to use the keyword
| | 05:15 | OUTPUT, and you have to use that keyword twice.
| | 05:17 | We have to use output once when you
declare the stored procedure, and once where
| | 05:21 | we execute the stored procedure.
| | Collapse this transcript |
| Using security and permissions| 00:00 | One of the main advantages of stored
procedures is how they allow us to have
| | 00:04 | more control over the security of the database.
| | 00:07 | We will be working with a
hypothetical user in this exercise called John.
| | 00:11 | There is a script in your exercise
files for creating the John user.
| | 00:15 | Consider the scenario where we
want to give John read only access to a
| | 00:18 | particular table, and maybe not even
the entire table. Maybe just one or two
| | 00:22 | columns in the table.
| | 00:24 | You could manually go in and set all
these permissions on the table of our
| | 00:28 | each individual column.
| | 00:29 | You could either grant or deny permission.
| | 00:32 | But it might be a lot of work to
do that for a whole bunch of users.
| | 00:36 | So we can hopefully lower our
administrative effort by using a different
| | 00:39 | technique to accomplish the same thing.
| | 00:41 | I have on the screen a basic
stored procedure. Again, you can find this in
| | 00:45 | your exercise files.
| | 00:46 | This stored procedure is called securityTest.
| | 00:49 | It will form a SELECT statement.
| | 00:51 | I'm going to select two columns
from the authors table, fairly simple.
| | 00:55 | When we Execute this, we
get the results we expected.
| | 00:58 | Nothing too exciting just yet.
| | 01:00 | We get FirstName and LastName
from every row in the table.
| | 01:05 | Now let's talk about John.
| | 01:06 | So let's go and give John
permission to run this stored procedure.
| | 01:10 | We will right-click on it and at the
bottom we have Properties, over here we can
| | 01:15 | go to Permissions, we will be setting
permissions for John and we'll go into it
| | 01:21 | and allow him to Execute and that's it.
| | 01:23 | I don't want him doing anything other
than executing the stored procedure.
| | 01:26 | So I'll Logout and then log back in, as John.
| | 01:35 | And he can get into the myDatabase.
| | 01:37 | You can see one of the stored procedures.
| | 01:40 | Now remember our database has three stored
procedures. John can only see one of them,
| | 01:45 | the one we gave him permission to, and he
should be able to execute that stored procedure.
| | 01:51 | And yes, in fact he can, and he gets
the exact same results as any other user.
| | 01:56 | John can not see the table.
| | 01:58 | He doesn't see the underlying table.
| | 02:00 | So he has no way of knowing there
were other columns in this table.
| | 02:04 | Some of these other columns in the
table are in fact storing things like
| | 02:08 | Address and Phone Number, which
could be confidential information.
| | 02:11 | Using this technique, we've
completely masked not only the contents of
| | 02:16 | those columns from John,
| | 02:17 | we've also masked even the
fact that those columns exist.
| | 02:21 | So we are in a situation like this
where we want a stored procedure to
| | 02:26 | allow access to a table where the user does
not have permission to that underlying table,
| | 02:32 | in order for it to work, the stored
procedure in the table, we need to have the
| | 02:36 | same owner, and in fact, our
stored procedure is owned by dbo.
| | 02:40 | And the table is also owned by dbo.
| | 02:44 | If either of them was owned by
someone else this would not work.
| | 02:47 | So let's go ahead and demo that.
| | 02:49 | I'm going to logout as John.
| | 02:53 | Log back in as someone who has the
necessary permissions to change this stuff.
| | 02:59 | So our authors table is currently owned by dbo.
| | 03:03 | Let's go ahead and change that.
| | 03:05 | So we're going to use this stored
procedure designed for changing ownership.
| | 03:10 | And it's called SP_changeObjectOwner
and the thing we want to change, the
| | 03:17 | owner of is dbo.Authors and we will
want to change the owner to Martin, and it
| | 03:25 | looks like it worked.
| | 03:26 | Click Refresh right here, yes.
| | 03:29 | We'll also need to make one
change to the stored procedure.
| | 03:32 | The stored procedure is looking for
dbo.Authors, which no longer exists.
| | 03:37 | So we'll change that to Martin.Authors.
| | 03:41 | Then now, I'd like to test to make
sure this stored procedure still works for
| | 03:44 | Martin, because Martin should still
have enough permission for this to run.
| | 03:47 | So Execute dbo.securityTest.
| | 03:53 | And that still runs for Martin.
| | 03:55 | I'm anticipating this will
not work properly for John.
| | 03:58 | Let's go ahead and test that.
| | 03:59 | I'm going to logout as
Martin, log back in as John.
| | 04:05 | John can still see the stored procedure,
| | 04:06 | but when he tries to execute the
stored procedure, it gives the error:
| | 04:12 | The SELECT permission was
denied on the object "Authors".
| | 04:16 | So now, because the stored procedure
and the authors table have different
| | 04:20 | owners, the permissions are not
passed back and forth the same way.
| | 04:24 | And John is no longer able to query
that from the stored procedure, even though
| | 04:29 | he has permission to the stored procedure.
| | 04:31 | In this case, he would also need
permission to the underlying table.
| | 04:34 | So the hypothetical, we were working
through, we first stored this, now we
| | 04:40 | want to allow access to John, will only
work if both items are owned by the same owner.
| | 04:45 | So now let's do a little housekeeping to
clean up some of the changes we made here.
| | 04:49 | First of all, I'm going to logout as
John, because the remainder of the work I
| | 04:53 | want to do as a different user.
| | 04:54 | I'll log back in as myself.
| | 04:59 | And we should see the authors
table is still owned by Martin.
| | 05:03 | I'll prefer to put it
back to be and owned by dbo.
| | 05:06 | And if you want your environment to
match mine, go ahead and execute the code
| | 05:09 | that's on the screen and
make sure it's Martin.Authors.
| | 05:15 | And when we refresh, yes;
| | 05:17 | we should see that is now owned again by
dbo and it will remain that way for the
| | 05:21 | remainder of our course.
| | Collapse this transcript |
| Using transactions| 00:00 | Database developers can and should
use transactions, to provide a logical
| | 00:05 | grouping of actions.
| | 00:06 | Transactions can happen inside of a stored
procedure or outside of the stored procedure.
| | 00:10 | I typically do most of my
transactions inside of stored procedures.
| | 00:15 | And we'll talk a few reasons about why.
| | 00:16 | I have on the screen some code
you can get from the exercise files.
| | 00:21 | It's a basic stored procedure, that's
going to either delete or deactivate an author.
| | 00:25 | There's one UPDATE statement
that runs on line 6, 7 and 8.
| | 00:30 | That's going to set the particular author's
active status to 0, meaning he is inactive.
| | 00:36 | And then lines 10, 11 and 12,
set his phone number equal to Null.
| | 00:40 | So this is a hypothetical where we
want to stop tracking personal information
| | 00:45 | about people that are no longer active.
| | 00:48 | Lines 14 to 17, allow us to potentially roll-
back the transaction if there's a problem.
| | 00:55 | So this will help us avoid the situation,
where we might end up with an author
| | 01:00 | who we couldn't mark as inactive.
| | 01:02 | We don't want to still remove his phone number.
| | 01:05 | So fairly straightforward, I'm
going to scroll down some more.
| | 01:07 | So we can see at the bottom where
we're actually going to execute this stored
| | 01:11 | procedure on John Doe.
| | 01:14 | So we execute this and it does in
fact mark John Doe as inactive.
| | 01:19 | And set his phone number equal to null.
| | 01:22 | This is certainly not the only way to do this.
| | 01:24 | We could have created one store
procedure that does the UPDATE statement on
| | 01:29 | line 6, 7 and 8 and a different store procedure
that does the UPDATE statement on 10, 11 and 12.
| | 01:36 | And then run those to stored
procedures as part of the same transaction.
| | 01:40 | From a developer point of view, I would
rather do it the way it's on the screen
| | 01:44 | here, where the structure of my
code also reflects some business logic.
| | 01:48 | Now these are two things that I've always
want to be run together in the same transaction.
| | 01:53 | And this type of mindset can often
help us decide what to put into a single
| | 01:57 | stored procedure versus when to
create a new stored procedure.
| | 02:01 | So anytime you are inside of a stored
procedure, creating a second transaction,
| | 02:06 | you should stop and think, hey, maybe
that information, maybe that code, maybe
| | 02:10 | that function should be moved
to a different store procedure.
| | 02:13 | So if you have two transactions or
three transactions inside of the same stored
| | 02:17 | procedure, you need to think about
maybe that should actually be broken out in
| | 02:21 | to separate stored procedures.
| | 02:22 | Conversely, if we have two stored
procedures that we pretty much always run
| | 02:26 | together and they always run as part of
the same transaction, you need to stop
| | 02:31 | and think about maybe we could
combine those two stored procedures into one
| | 02:34 | stored procedure, and in there put the
business logic about the transactions.
| | 02:39 | Meaning, we really have to think about
the logic once but get it done in the
| | 02:43 | store procedure right and
never have to worry about it again.
| | Collapse this transcript |
|
|
4. User-Defined FunctionsCreating a user-defined function| 00:00 | Now I'd like to talk a little bit about
functions, sometimes called user-defined
| | 00:05 | functions in SQL Server 2012.
| | 00:08 | I'm going to start off
with creating a new function.
| | 00:11 | So very similar to creating a stored procedure.
| | 00:14 | We start off with the keyword
CREATE and then the keyword FUNCTION.
| | 00:17 | And then we need to give a name,
typical naming rules in play here.
| | 00:23 | After the name, you have to supply a
list of parameters in parenthesis.
| | 00:28 | So we could list out as
many parameters as we want.
| | 00:33 | You also have the option to do zero
parameters which if you'd like to do
| | 00:37 | zero parameters, you still have to put the
empty parenthesis, which I will do here.
| | 00:43 | Next, we have to declare what type of
data, this function is going to return.
| | 00:48 | Every function will return something
and we don't at this point have to decide
| | 00:52 | the exact value we're returning,
| | 00:54 | but we do have to decide what
data type you're going to return.
| | 00:58 | So are you going to return a
date, a piece of text, a number?
| | 01:02 | You have to make that decision at this time.
| | 01:04 | You type in the keyword RETURNS, plural,
and I'll use an integer for this one.
| | 01:10 | So this function will return an integer.
| | 01:12 | Then we put the keyword
AS and the keyword BEGIN.
| | 01:16 | Most of these things do not
have to be on separate lines.
| | 01:19 | I just find it makes it a little bit
easier to read, if you do in fact put them
| | 01:23 | all on separate lines.
| | 01:24 | After the keyword BEGIN, we'll need to
add some T-SQL statements that accomplish
| | 01:29 | whatever it is that you want to accomplish.
| | 01:31 | For now, we'll keep it very simple
and we're just going to return a value.
| | 01:36 | I previously declared we
would be returning an integer.
| | 01:39 | So I will stick with that and say return 7.
| | 01:44 | Now that I'm done with my function,
I will put in the keyword END.
| | 01:49 | I can execute this and we see some
positive feedback at the bottom, command(s)
| | 01:54 | completed successfully.
| | 01:56 | I'll go over to the left and expand my
favorite menu, Programmability. Underneath
| | 02:01 | that, we see Functions and
this is a Scalar-valued Function.
| | 02:06 | So I'll hit refresh and now my
function appears there, my Test Function.
| | 02:13 | So notice under the functions
category, we have Table-valued Functions,
| | 02:18 | Scalar-valued Functions,
Aggregate Functions and System Functions.
| | 02:22 | The System Functions are the one created
by the machine. We cannot change those.
| | 02:27 | The other three Table-valued Functions,
Scalar-valued Functions and Aggregate
| | 02:32 | Functions, we will be working
within this course starting off with
| | 02:35 | Scalar-valued Functions for now.
| | 02:38 | So I've successfully created a Scalar-
valued Function called myTestFunction, I
| | 02:43 | would like to run that function.
| | 02:45 | Typically, the easiest way to do that is
to put it as part of a SELECT statement.
| | 02:50 | So I will say SELECT and then I need to
give it the name of the schema, the name
| | 02:54 | of the function, and empty parentheses.
| | 02:57 | When I run that, it returns 7 and
that is exactly what we had hoped for
| | 03:03 | returning a single value.
| | 03:06 | Notice, if I don't put the parenthesis,
I will in fact receive an error or
| | 03:11 | if I don't list the name of the
schema, I will receive an error.
| | 03:16 | That's a little unusual because at most
places in Microsoft SQL Server, the name
| | 03:19 | of the schema is optional,
here it is not optional.
| | 03:23 | You do have to in fact have
say schema.function name ().
| | 03:29 | That is the only way it's going to work.
| | 03:31 | Back looking at our function, if we
would like to modify it, we can alter
| | 03:37 | our function thusly.
| | 03:38 | I wanted to talk briefly about a
few common mistakes that happens;
| | 03:43 | the most common of which
is the data type mismatch.
| | 03:46 | So I said I was going to return an
integer. What if I write code that
| | 03:51 | doesn't return an integer?
| | 03:54 | Surprisingly, it let me do this!
The machine said command(s)
| | 03:57 | completed successfully.
| | 03:59 | But, now let's go back and
try and run that function.
| | 04:02 | The error message says;
| | 04:03 | conversion failed when
converting from text to number.
| | 04:07 | So it couldn't take a value that I gave it.
| | 04:10 | I told it to return the value test.
| | 04:12 | It could not convert that to an
integer and it did give me an error.
| | 04:16 | The surprising part is it allowed me
to create the function with the error.
| | 04:21 | It just never allows me to run the function.
| | 04:24 | So you get the error at a time may be a
little different than what you thought.
| | 04:28 | The other thing to be real careful
about, when creating these is, the keyword
| | 04:33 | RETURN with no S versus the
keyword RETURNS with an S.
| | 04:39 | The first time we use returns, it does
have an S, the second time it does not
| | 04:45 | and if you get those backwards.
| | 04:47 | It will say incorrect
syntax near the keyword RETURN.
| | 04:52 | So this first one needs an S
and the second one does not.
| | Collapse this transcript |
| Exploring single-value functions| 00:00 | Now I would like to talk a little
more about user defined functions,
| | 00:04 | specifically, when Microsoft
calls a Scalar-valued Function.
| | 00:09 | Scalar is a single piece of data,
such as a single number, a single date, a
| | 00:13 | single piece of text.
| | 00:15 | So any function that returns a
single piece of data is called a
| | 00:18 | Scalar-valued Function.
| | 00:19 | These are the most common functions
we work with in Microsoft SQL Server.
| | 00:24 | In this example, we will be working with
some of the code in your exercise files.
| | 00:29 | The first thing, I'd like to do is
populate the authors table with a few addresses.
| | 00:33 | We're going to be working
through some examples with addresses.
| | 00:37 | And right now no one has an address
yet, so we'll go ahead and execute some
| | 00:41 | code that should give two
our users some addresses.
| | 00:48 | Yes, look at that, now John Doe and
Sally Smith both have valid addresses.
| | 00:55 | So now looking at the code
that creates the function;
| | 01:01 | the function is called format addresses.
| | 01:03 | It takes four inputs one called city,
one called street, one called state
| | 01:08 | and one called zip.
| | 01:09 | I think those are all self explanatory.
| | 01:12 | On line 7, we see it returns a varchar 255.
| | 01:16 | So it returns a single piece of text
that makes it a Scalar-value Function.
| | 01:21 | Lines 12 through 15, you see we are
checking to see if any of the inputs were
| | 01:27 | null. If that is the case, if any of
them are null, we are just going to return
| | 01:31 | the phrase 'incomplete address'.
| | 01:33 | We don't have enough information
to completely format an address.
| | 01:37 | I'll scroll down a little more, we see
lines 19 through 23, or in a lightweight case
| | 01:43 | statement, that will take the
abbreviation for a state and change it into the
| | 01:47 | full name for the state.
I didn't do all 50 lines.
| | 01:50 | I just did enough for a demo.
| | 01:52 | We see line 26 returns, street
space city comma state space zip.
| | 02:00 | So in other words, how an
address is typically formatted.
| | 02:04 | Go ahead and run that and we will see the
command(s) completed successfully. That's good news.
| | 02:10 | Come back to our sample code, and I
have one line that will go ahead and
| | 02:15 | test this function.
| | 02:22 | We are passing the inputs 100 Main and
Buffalo New York and that zip code, and we
| | 02:28 | see the return is pretty
much exactly what we expected.
| | 02:32 | Just doing this for ad hoc
data is not very interesting.
| | 02:36 | These functions become much more useful
when we make them part of a larger query.
| | 02:41 | So here I have a SELECT statement, it's
going to select things from the authors table.
| | 02:48 | Select first name, last name and
then we see the name of the function
| | 02:52 | dbo.format address.
| | 02:55 | And then it will pass in the four parameters:
| | 02:57 | address, city, state and zip.
| | 02:59 | When we run that, we see the top two lines
returned incomplete address and that is correct.
| | 03:06 | We do not have address
information for those two people.
| | 03:10 | And then lines 3 and 4 returned a
properly formatted address, they changed the
| | 03:15 | state abbreviation to the full name for
the state, and it added in the comma in
| | 03:20 | between the city and state.
| | 03:22 | So that's very useful as being part of
the SELECT statement. We can also make a
| | 03:27 | function part of a 'where' clause.
| | 03:29 | So we can filter this query
based on results from that function.
| | 03:36 | To me a very logical thing would be,
I want to know everyone that has an
| | 03:40 | incomplete address, and we'll run that.
| | 03:44 | And now we see we've got only the users
with the incomplete address, fairly useful.
| | 03:48 | It is also possible to do an
order by, on that function field.
| | 03:56 | In this example, this may not be quite
as practical as doing the where clause
| | 04:01 | but, we can see that we can order
by the value of the functions field.
| | 04:05 | So any time you create a Scalar-
valued Function, it is possible to use that
| | 04:10 | function as part of the SELECT statement,
part of the where clause and or part
| | 04:16 | of the order by clause.
| | Collapse this transcript |
| Exploring table value functions| 00:00 | Now I'd like to talk about another
type of function, what Microsoft calls a
| | 00:05 | Table-valued Function.
| | 00:06 | As the name implies, the results of
this function will be an entire table.
| | 00:11 | I've a little code prepared for you
in your exercise files, copy that over.
| | 00:21 | So the top starts off
just like any other function.
| | 00:23 | We're going to create a function call
it 'authors by status' and it will take
| | 00:28 | one input parameter.
| | 00:29 | Line 2 says what we are going to
return, and we are going to return a table.
| | 00:34 | And then lines 3 through 11 define that table.
| | 00:37 | So just like if you were creating a
new table, you have to define all the
| | 00:41 | columns, give it a name, a data type
and whether or not we allow nulls for
| | 00:47 | each particular field.
| | 00:48 | So it can be a lot of typing to get
all this in because we have to define,
| | 00:53 | in a fair amount of detail of
the entire table we want to define.
| | 00:57 | The meat of this starts on around
line 13, and we see an INSERT statement.
| | 01:03 | So we defined a table.
Right now that table is empty.
| | 01:06 | In order for this to be useful, we
have to insert something into that table.
| | 01:10 | And what are we going to insert?
| | 01:12 | We are going to insert the results of a query.
| | 01:14 | Line 16, 17 and 18, define that query.
| | 01:18 | It's everything from the authors table
depending on the value in the active field.
| | 01:25 | Remember that the only parameter
passed to this function is a value that
| | 01:29 | represents is active.
| | 01:31 | So this query will filter our
results based on that value.
| | 01:35 | Go ahead and run this,
command(s) completed successfully.
| | 01:39 | You should see it in there now and there we go.
| | 01:47 | So now this function
behaves very much like a table.
| | 01:52 | We can do a SELECT, store.
| | 01:54 | Normally you would say SELECT, store FROM table.
| | 01:57 | But, instead we are going to say
select store from our function. Remember, it
| | 02:02 | does take one parameter.
| | 02:05 | So there would be all of our authors
whose status is equal to zero, or we can
| | 02:10 | pass it a 1, and there would be all of
our authors whose status is equal to one.
| | 02:15 | This is fairly useful.
| | 02:17 | Obviously, there's other ways that
could accomplish this. For example,
| | 02:20 | a view would do some similar things.
| | 02:23 | But, remember you can't
pass a parameter to a view.
| | 02:26 | So the strength of Tabled-valued
Functions is that we can pass one or more
| | 02:31 | parameters to it and get different
results based on those parameters.
| | 02:35 | Other than that they function
largely like tables or views.
| | Collapse this transcript |
|
|
5. TriggersUsing "after" triggers| 00:00 | In this chapter, I'll be working with
two new tables; a products table and
| | 00:04 | a categories table.
| | 00:06 | I've created a script that will generate
those tables and populate them with some data.
| | 00:11 | There in your exercise files. You
can copy and paste all of that into
| | 00:16 | Management Studio and run it.
| | 00:19 | And it should've given me two new tables.
| | 00:22 | And let's double check and make sure we have
some data in those tables. Oh, that looks good.
| | 00:28 | The first type of trigger I'd like to
talk about is called an 'after' trigger.
| | 00:32 | It's called this because it executes
immediately after an insert, update
| | 00:37 | or DELETE statement.
| | 00:39 | The trigger allows the original
statement to occur with no modifications.
| | 00:43 | Then it immediately takes
over and starts doing some work.
| | 00:47 | In this example, we're going to use
the trigger to enforce a business rule.
| | 00:51 | The business rule states that whenever
a category is marked as inactive, all of
| | 00:57 | the products in that category should
immediately be marked as inactive also.
| | 01:02 | So I have some code staged
for this in your exercise files.
| | 01:06 | Just like with functions and stored
procedures, we start with the keyword Create
| | 01:11 | and then we use the keyword Trigger.
| | 01:13 | Then we have to give it a name.
| | 01:15 | I called mine CategoryDeactivation.
| | 01:16 | Line 2, we are setting a
relationship with a table.
| | 01:20 | Every 'after' trigger is related to
one table and is going to react to
| | 01:24 | actions only on that table.
| | 01:26 | Line 3 says AFTER UPDATE.
| | 01:29 | So that's the one verb I'm interested in.
| | 01:31 | This trigger will ignore,
insert and DELETE statements.
| | 01:35 | It will only run
immediately after an UPDATE statement.
| | 01:38 | Then let's look at the bulk
of the code; line 6 through 14.
| | 01:43 | We start off on line 6 creating a
variable to hold the isActive bit.
| | 01:47 | I want to make sure with the update
that came in, was someone trying to set a
| | 01:54 | category as active or
trying to set it as inactive.
| | 01:56 | So on line 11 there, I'm looking at the
isActive bit in saying it whether or not
| | 02:02 | it is equal to zero.
| | 02:04 | If it is in fact equal to zero, I'll
perform the actions that start on line 12
| | 02:09 | which is UPDATE Products.
| | 02:11 | Set active equal to zero and then we
are saying where category ID is in the
| | 02:17 | category ID from inserted.
| | 02:19 | Notice there on the line 14, where you
are querying in a table called inserted.
| | 02:24 | Inserted is a temporary table that's
only available inside of the trigger.
| | 02:30 | It's automatically created by the
machine and it holds all of the new
| | 02:34 | information that someone is
trying to put into the table.
| | 02:38 | So let's go ahead and run this, command(s)
completed successfully. That's good.
| | 02:43 | And now we should be able to go under
the categories table and see a new trigger.
| | 02:47 | And there it is, CategoryDeactivation.
| | 02:53 | So to test this, let's first look again
at the products table, and we see three
| | 03:02 | items in category one all of them are active.
| | 03:05 | Now I'm going to issue a command that
will mark the category one as inactive.
| | 03:12 | When I execute this, it should
perform that action and then the trigger
| | 03:16 | will fire and update the product table, and
mark everything in category 1 as inactive.
| | 03:22 | That would appear to be successful.
| | 03:28 | Let's look at the product
table just to make sure.
| | 03:30 | And yes, our three products
in category 1 are now inactive.
| | 03:37 | We use the trigger to enforce the
business rule that whenever a category is
| | 03:42 | marked inactive all of the
products should immediately be marked as
| | 03:45 | inactive also.
| | Collapse this transcript |
| Using "instead of" triggers| 00:00 | The next type of triggers we will
talk about are 'instead of' triggers.
| | 00:04 | These triggers are executed as an
alternative to an INSERT, UPDATE, or DELETE statement.
| | 00:10 | These triggers completely block the
original statement leaving the data unchanged.
| | 00:14 | However, inside the body of the trigger we
can choose to modify data and we usually do.
| | 00:20 | In this example, I'll be of using a
trigger to enforce a data integrity rule.
| | 00:25 | The rule states that we
cannot ever delete a category.
| | 00:28 | We must just mark that category as inactive.
| | 00:31 | So if someone tries to delete the
category, I want to trigger to block the
| | 00:35 | action and instead update the
record and set active equal to 0.
| | 00:40 | I staged some code for
you in your exercise file.
| | 00:44 | Let's talk about this code.
| | 00:45 | CREATE TRIGGER CategoryDelete on (dbo).(Categories)
and line 3 says INSTEAD OF DELETE.
| | 00:53 | So again that will prevent the
DELETE statement from happening.
| | 00:57 | Line 6, 7, and 8 perform an update on
the categories table instead of the delete.
| | 01:03 | It's going to set active equal to 0
where the category is in and then we select
| | 01:09 | from the deleted table.
| | 01:11 | The deleted table is a temporary table
automatically created by the trigger that
| | 01:16 | contains the data the user was trying to delete.
| | 01:18 | So let's go ahead and run this.
| | 01:21 | Command successful.
| | 01:23 | We can refresh our trigger section and we
should see CategoryDelete. That's good news.
| | 01:29 | Now let's test it.
| | 01:31 | We can select from our categories
table and see what it looks like now.
| | 01:38 | And we see category number 2
Pants is currently marked as active.
| | 01:42 | Let's see what would happen if
someone tried to delete that category.
| | 01:48 | What should've happened is the trigger
should've caught this DELETE statement,
| | 01:52 | blocked the DELETE statement, and
turned it into an update instead.
| | 02:00 | That appears to be exactly what happened.
| | 02:01 | Category two is still there.
| | 02:03 | It has not been deleted it, but
instead the trigger marked it as inactive.
| | 02:07 | It certainly looks like success.
| | Collapse this transcript |
| Using nested triggers| 00:00 | In our two previous examples we
created one trigger that will block deletions
| | 00:04 | from the categories table, instead
marking records as inactive, and we created
| | 00:09 | another trigger that when a category
is marked as inactive, it marks all
| | 00:13 | products as inactive also.
| | 00:16 | This means that if I issue a DELETE
statement, the first trigger fires, blocking
| | 00:21 | my DELETE statement, and
instead updating the record.
| | 00:24 | That update, the update that was
caused by the trigger, will in turn cause the
| | 00:29 | second trigger to fire.
| | 00:31 | This situation where the actions of one
trigger cause the next trigger to fire
| | 00:35 | is called nested triggers.
| | 00:37 | The ability to nest triggers is turned on by
default, but you can turn it off if you want to.
| | 00:43 | I have some code in your
exercise files that will do this.
| | 00:47 | It's an SP configure, the feature we
would like to configure is nested triggers.
| | 00:52 | It is turned on by default which would be a 1.
| | 00:55 | So we'll use a 0 to turn it off and if
you'd like to turn it back on, just use a 1.
| | 01:01 | Even when nested triggers is turned on,
it is always limited to 32 levels.
| | 01:06 | So we can have trigger 1, cause
trigger 2 to fire which causes trigger 3 to
| | 01:11 | fire, which causes trigger 4 to fire,
but you can only go 32 levels deep.
| | 01:16 | That is at least the machine says,
you can only go 32 levels deep.
| | 01:20 | As a best practice I would
recommend a lot less than 32 levels.
| | 01:23 | Obviously, something that goes 30 or 31
levels deep could be very difficult to
| | 01:28 | troubleshoot and very difficult to
get your mind around what's going on.
| | 01:32 | As a best practice, I usually try and
limit my nesting to three levels or less.
| | Collapse this transcript |
| Using database-level triggers| 00:00 | The triggers we've seen thus far are all
at the table level, meaning they react to
| | 00:04 | an INSERT, UPDATE, or DELETE
statement issued to one table.
| | 00:09 | SQL Server also supports
triggers at the database level.
| | 00:13 | These trigger fire whenever a
statement is issued that would change the
| | 00:16 | structure of that database.
| | 00:17 | I have an example staged for
you in your exercise files.
| | 00:24 | This trigger we're saying is on the
database on line 2, so that means it applies
| | 00:28 | to the entire database.
| | 00:30 | Line 3 says it is for any attempt to drop
a table or any attempt to alter a table.
| | 00:37 | The body of the trigger is very simple.
| | 00:39 | It's going to PRINT a message say you do
not have permission to drop or alter to
| | 00:42 | the table and then is going to ROLLBACK.
| | 00:44 | In other word it's going to undo
whatever someone was attempting to do.
| | 00:49 | So I'll execute this, and now we
can find these triggers under the
| | 00:53 | Programmability menu.
| | 00:54 | We have a folder called Database Triggers.
| | 00:58 | If I refresh that, I see the
new trigger I just created.
| | 01:01 | So let's go ahead and test this trigger.
| | 01:03 | I'm going to attempt to drop a table and
the triggers should stop that from happening.
| | 01:12 | It did in fact say you do not have
permission to drop or alter tables.
| | 01:15 | It also said the batch has bit aborted.
| | 01:18 | Meaning it completely blocked me from happening.
| | 01:21 | And if I do a refresh, I see clearly the
Authors table is still there. So good job
| | 01:27 | trigger! You blocked me from
possibly making a mistake.
| | 01:30 | I do implement this type of trigger and
production databases often, because in
| | 01:35 | my opinion no one should be
changing my production databases.
| | 01:38 | You can make the triggers slightly
more robust and check permissions.
| | 01:42 | So check who is trying to do drop and
then possibly allow or do not based on that.
| | 01:48 | But this is a nice way to stop some
mistakes from happening or possibly even
| | 01:53 | stop some malicious users from
doing bad things to your database.
| | Collapse this transcript |
|
|
6. Putting It All TogetherExploring a real-world INSERT procedure| 00:00 | Now I'd like to demonstrate some
more complex stored procedures.
| | 00:03 | These are the types of procedures I
commonly see on large databases in the real world.
| | 00:08 | They implement business rules as well
as enforce data integrity requirements.
| | 00:13 | In order to execute the examples in
this section we'll need a new table.
| | 00:17 | I have a text file of data in
your folder of exercise files.
| | 00:20 | Let's import that data now.
| | 00:22 | I'll right-click on myDatabase, go to Tasks,
and near the bottom we have Import Data.
| | 00:29 | On this screen I'll click Next.
| | 00:31 | The first question it asked
me is, what's my data source?
| | 00:32 | My data source will be a Flat File
and I'll browse to that file now.
| | 00:39 | It's in the Chapter 6
section of your exercise files.
| | 00:43 | This dialog box defaults to text files.
| | 00:46 | What I want is want is actually a CSV,
so there we will pick up students.csv,
| | 00:51 | and now it's just a whole bunch
of Next, Next, Next, Next, Finish.
| | 00:58 | And I get a bunch of green check
mark saying Success. That seems good.
| | 01:02 | I'll hit a refresh here and
I have a new students table.
| | 01:06 | Let's go ahead and look at some of that data.
| | 01:08 | We have things like a student ID,
last name, first name, state, some other contact
| | 01:13 | information including an email address,
a graduation year, GPA, and a yes or
| | 01:20 | no on whether or not they would like
to receive our email newsletter. Great!
| | 01:25 | Now that we have data to work with the
first stored procedure I'll implement is
| | 01:28 | an insert stored procedure.
| | 01:30 | I'm going to make up the following fairly
plausible hypothetical rules about inserting.
| | 01:36 | When inserting, every ID must be unique.
| | 01:39 | It's something that could be handled
by the database automatically, if it was
| | 01:43 | automatically assigning IDs, but in this
hypothetical I'm going to say different
| | 01:47 | system is assigning IDs and as they come
in to my system, I want to double-check
| | 01:51 | and make sure they're unique.
| | 01:53 | Also, things like a GPA must be between 0
and 4 with no more than two decimal places.
| | 01:58 | That's something that a front-end
application should handle, but it doesn't hurt
| | 02:02 | for me to double-check it.
| | 02:03 | Let's say hypothetically someone else
is writing the front-end application and
| | 02:07 | they are either unwilling
or unable to make this check.
| | 02:11 | Then I'll check it on the way into the
database and we're going to return a 1
| | 02:16 | for success or 0 for failure.
| | 02:18 | So I have a stored procedure
stage already in your Exercise folder.
| | 02:22 | It significantly longer than some of the
stored procedures we've worked with earlier.
| | 02:26 | So I'll walk you through it.
| | 02:28 | We start off of course with keyword,
create keyword procedure, and then we have
| | 02:32 | to accept quiet number of parameters.
| | 02:33 | The number of parameters I am
accepting is exactly the same as the number of
| | 02:37 | columns in the table.
| | 02:39 | I need one parameter for each
field we're going to insert.
| | 02:42 | Scrolling down a little right after the
BEGIN statement, the first I want to do
| | 02:47 | is check to make sure the ID does
not already exist in the database.
| | 02:50 | So that's one of my business rules.
| | 02:52 | We have to have unique IDs.
| | 02:53 | So line 18, 19, 20, 21 will perform a query
to see how many times this ID already exists.
| | 03:01 | Line 23 checks to see if
that number is greater than 0.
| | 03:05 | If it is, it raises an error and returns 0.
| | 03:09 | That is exactly the desired functionality.
| | 03:12 | Once that happens, the code
will move on to line 31, 31, 32, 33.
| | 03:18 | We will format our GPA as two decimal places.
| | 03:21 | That was one of our business
requirements and then line 36, make sure the GPA is
| | 03:26 | within a certain range.
| | 03:28 | If the GPA is greater than 4 or less than 0, I
am going to return an error to the user again.
| | 03:35 | If we pass that check we're down to
lines 44 where it's time to actually attempt
| | 03:39 | the insert, fairly
straightforward, INSERT statement.
| | 03:43 | The very last thing I do is check to
see the value of a variable @@ROWCOUNT.
| | 03:48 | This as a very variable I did not create.
| | 03:51 | SQL Server automatically creates and
the updates this variable with how many
| | 03:55 | records were modified by the previous statement.
| | 03:58 | If everything worked properly, I
should've modified exactly 1 row.
| | 04:02 | So if that value does =1, I'll return
a 1 to the user indicating success of
| | 04:07 | the stored procedure.
| | 04:09 | So we'll go ahead and execute
that, command(s) completed successfully.
| | 04:12 | I always like hearing that.
| | 04:14 | So there's my stored procedure, Students_Insert.
| | 04:19 | Now let's go ahead and execute this.
| | 04:21 | I also have a little
code stage for you for that.
| | 04:25 | So I am going to try to insert the
values, ID 123456, last name Guidry, firstname
| | 04:30 | Martin, some basic contact information there.
| | 04:33 | Notice that 7.1; I am
intentionally trying to insert bad data.
| | 04:38 | I am claiming that my GPA is 7.1.
| | 04:40 | Obviously, very ambitious.
| | 04:42 | If our stored procedure functions
properly, it should return an error.
| | 04:46 | So let's go ahead and execute.
| | 04:47 | Look at that! It says GPA is invalid.
| | 04:50 | That is a good catch.
| | 04:51 | So I'll change that to a more
realistic value and we will try to run it again,
| | 04:56 | and now it's says one row affected.
| | 04:58 | That likely means success.
| | 04:59 | I'll go check the database here in just
a second, but I am going to run it again
| | 05:03 | and it should say ID already exists.
| | 05:06 | So it successfully did
the insert the first time.
| | 05:08 | If I try to run it again, it stops me
saying this would be a duplicate ID,
| | 05:12 | which is a bad thing.
| | 05:14 | Just to make sure it actually got in there.
| | 05:16 | Let's go ahead and query the table.
| | 05:21 | That returns exactly 1 row. That's good news.
| | 05:24 | We'll scroll over.
| | 05:25 | You see my GPA there
formatted to two decimal places.
| | 05:28 | That was one of the requirements.
| | 05:30 | So it seems like everything is working great.
| | Collapse this transcript |
| Exploring a real-world UPDATE procedure| 00:00 | Next I'd like to talk about a
real-world UPDATE stored procedure.
| | 00:04 | On this one we're going to work with a
slightly different set of business rules.
| | 00:09 | Now in the real-world your insert
and UPDATE stored procedure would likely
| | 00:12 | have the same set of rules.
| | 00:14 | I am going to go with a different set
of business rules just for the sake of
| | 00:16 | having more things to demo.
| | 00:18 | So our update rules are you can not
subscribe to the newsletter if your
| | 00:23 | email address is null.
| | 00:24 | This makes sense because our
newsletter is an email newsletter.
| | 00:27 | So you can't subscribe if you
don't have an email address.
| | 00:30 | The update stored procedure
should not be allowed to change the ID.
| | 00:33 | It can change any other piece of data,
but it cannot change someone's ID.
| | 00:37 | It needs to return an
error if the ID does not exist.
| | 00:40 | If someone attempts to modify student
1234 but student 1234 does not exist in
| | 00:46 | the database, we need to let them know
about that problem, and then we have my
| | 00:51 | general best practice return 1
for success and 0 for failure.
| | 00:55 | So I have a stored procedure staged
that should accomplish all of this and
| | 00:59 | we'll start from the top.
| | 01:01 | So we're going to CREATE
PROCEDURE Students_Update.
| | 01:04 | One parameter for every field will take
as an input, the ID will tell us which
| | 01:10 | record we want a change, and
everything else will represent the new values
| | 01:14 | that were changing to.
| | 01:15 | Scroll down a little.
| | 01:17 | Lines 18 through 21 will count the number
of times this ID exists in the database.
| | 01:23 | If it comes out as 1, we will continue
on, but if it comes out as anything other
| | 01:28 | than 1 that's an error condition.
| | 01:30 | So if the ID does not exist,
then there's nothing to update.
| | 01:34 | If the ID exists more than once
we've got a bigger problem on our hands.
| | 01:38 | So we'll only continue if the
ID exists once and exactly once.
| | 01:43 | Line 30 to 32 implement the rule of
you cannot subscribe to the newsletter
| | 01:49 | if your email is null.
| | 01:50 | This turned out to be a
relatively small amount of code.
| | 01:53 | It checks to see if email
address is null, and if so it sets your
| | 01:57 | subscription to off.
| | 01:58 | You're not subscribed to the newsletter.
| | 02:01 | Line 36 down is a big UPDATE statement
that concludes with a where clause on
| | 02:07 | line 47 where we're checking to make
sure the ID that we are updating is in fact
| | 02:11 | the ID that was passed to us.
| | 02:13 | Then similar as before, line 51 through
54 is checking to make sure we modified
| | 02:19 | exactly 1 record that would indicate success.
| | 02:22 | Anything other than modifying
one record would indicate failure.
| | 02:27 | So I'll run this,
commands completed successfully.
| | 02:32 | So I now have Students_Update and I
have a little code staged that'll run this.
| | 02:37 | So now let's go ahead and
execute this stored procedure.
| | 02:41 | Notice that I'm updating my own record.
| | 02:43 | I'm setting my email address to null and
I'm trying to subscribe to the newsletter.
| | 02:48 | I went ahead and put it 1 for my
newsletter subscription, the stored procedure
| | 02:52 | should catch that and change
that to a 0. So I'll run this.
| | 02:56 | It looks like success.
| | 02:58 | Now let's go ahead and
check that and there I am.
| | 03:05 | My record is still intact, email has been
set to null, and newsletter has been set to 0.
| | 03:10 | So stored procedure is functioning as desired.
| | Collapse this transcript |
| Implementing logging on DELETE| 00:00 | For our DELETE stored procedure, the
rules we're going to implement is the
| | 00:05 | stored procedure can only delete one
record at a time, and we must maintain a
| | 00:09 | log of who deleted and when.
| | 00:11 | So in order to maintain that log I'd
like to create a new table that will be
| | 00:15 | used for that logging.
| | 00:16 | I have some code staged for that.
| | 00:18 | It's a three column table; one
column for user name, the person who did the
| | 00:22 | deletion, one column for the ID that
they deleted, and one column for the date.
| | 00:26 | I also have some codes
staged for the stored procedure.
| | 00:30 | So starting at the top we have
with CREATE PROCEDURE Students_Delete.
| | 00:34 | This time we are only taking one parameter.
| | 00:36 | All I need is the ID of
what you would like to delete.
| | 00:39 | Line number 2 there is new to us.
| | 00:41 | We're saying EXCUTE AS CALLER.
| | 00:43 | There are some context in which a
stored procedure might execute as the
| | 00:47 | system, but in this one, one of my business
requirements is to log who is running this.
| | 00:52 | So I am going to
specifically saying EXCUTE AS CALLER.
| | 00:56 | Line 6 through 11 we check to make
sure that this ID already exists and make
| | 01:01 | sure it only exists in the database once.
| | 01:04 | Now we did something very similar to
this in the INSERT stored procedure, and the
| | 01:08 | UPDATE stored procedure.
| | 01:10 | Now we're going to have it a third
time in the DELETE stored procedure.
| | 01:13 | This is becoming a little displeasing
to me to have an essentially the exact
| | 01:17 | same code running in three different places.
| | 01:19 | So I'm going to use a technique we
talked about earlier in this course of
| | 01:22 | simplifying our lives.
| | 01:23 | I am going to create a function that
performs all of this and that way we'll
| | 01:28 | just be able to use the function.
| | 01:30 | We won't have to maintain the exact
same code in three different places.
| | 01:33 | So we'll take a slight detour and I'll
pull up the code I had for the function.
| | 01:37 | So this is a function that is going to
count the number of times that a certain
| | 01:41 | ID exists in our students table.
| | 01:45 | The only parameter you
have to pass to it is an ID.
| | 01:49 | The ID you're in fact looking for.
| | 01:50 | It'll return an integer, a single integer
saying how may times does this ID occur.
| | 01:58 | Execute that, command(s) completed
successfully. That's good news.
| | 02:02 | So now I'll go back to the stored
procedure and replace that code.
| | 02:05 | This code that appears
in three different places.
| | 02:08 | I want to simplify my life and get rid all that.
| | 02:12 | All I need to check on is the value of
this. So that code will pass the ID in
| | 02:19 | question to the function called Count
IDs, and that function will return the
| | 02:25 | number of times that ID occurs in our table.
| | 02:29 | Beyond that, things are fairly straightforward.
| | 02:31 | Line 50 and 16 will perform the delete.
| | 02:34 | Line 20 checks to see if we did in fact
delete exactly 1 record, and if we did
| | 02:39 | we need to meet our other business
requirements of logging who did the deletion.
| | 02:43 | So line 22 and 23 will insert a
new record into Student_Delete log.
| | 02:49 | The values we are looking for is suser_
sname which is a SQL Server variable that
| | 02:56 | stores the name of who is
running the stored procedure.
| | 02:59 | The next thing we pass to it is the ID
that was deleted, and the last thing we
| | 03:03 | pass is the current date.
| | 03:05 | Let's go ahead and run that and
that gives us one stored procedure for
| | 03:14 | deleting a student, one stored
procedure for inserting a student, and one for
| | 03:18 | updating a student.
| | 03:19 | We have nice a little package of all of the
things we're likely to perform on a student.
| | 03:24 | Go ahead and test the delete functionality.
| | 03:29 | It says one row affected so that
should have deleted that ID from the student
| | 03:34 | table, and yes, in fact that record is gone.
| | 03:38 | We also said we we're going to log this.
| | 03:41 | So let's check to make sure our log
is functioning properly, and we see one
| | 03:45 | record in there, User Name
Martin, deleted the ID 123456.
| | 03:50 | There is the date the record was deleted.
| | 03:52 | So we have successfully
implemented all of the business rules for a
| | 03:56 | DELETE stored procedure.
| | Collapse this transcript |
|
|
7. Using CLR AssembliesUnderstanding the Common Language Runtime (CLR) and the .NET framework| 00:00 | Now I would like to talk about a very
different technique of creating stored
| | 00:04 | procedures and functions.
| | 00:06 | I will create some stored procedures
using the .NET framework languages.
| | 00:10 | In the demos I'll focus on C# which is
certainly the most popular language, but
| | 00:15 | we can also use any of the hundreds
of .NET languages C#, VB.NET, F#, J#
| | 00:23 | IronPython, IronRuby, and many others.
| | 00:27 | It is possible to write a stored
procedure at the .NET framework, because both
| | 00:32 | the .NET framework and SQL Server
are heavily integrated with the Common
| | 00:36 | Language Runtime, commonly called the CLR.
| | 00:39 | However, not every version of the CLR
is compatible with every other version.
| | 00:45 | So here on the screen I have a chart
showing some of the compatibility levels.
| | 00:48 | SQL Server 2005 and 2008 is compatible
with the CLR versions 2.0, 3.0, and 3.5.
| | 00:59 | And SQL Server 2012 we still get 2.0, 3.0,
3.5, but we also additionally get 4.0.
| | 01:07 | This can lead to some interesting
decisions for a developer as to which version
| | 01:11 | should you write your code in.
| | 01:13 | But in many cases it's not that hard to
change what version you're an after the fact.
| | 01:19 | Inside of Visual Studio you can right-
click on any project, go to Properties,
| | 01:25 | and you'll see one of the
dropdowns is Target framework.
| | 01:28 | Here it's been selected to the 4.0.
| | 01:30 | It would be very easy to
change this to 2.0, 3.0, 3.5.
| | 01:34 | Obviously, there are a few features
that are limited to a specific version of
| | 01:40 | the framework and we can
have some compatibility issues.
| | 01:43 | But the majority of your code will work
with any version of the .NET framework
| | 01:50 | and it's very easy to change right here.
| | Collapse this transcript |
| Using CLR with SQL Server 2012| 00:00 | In order to use .NET code in our SQL
Server we must first enable CLR Integration.
| | 00:07 | CLR Integration is a default feature,
but the default is that it is turned off.
| | 00:11 | So we merely need to turn it on.
| | 00:13 | We will do this by using
the sp_configure command.
| | 00:19 | The feature we're interested in
configuring is called clr enabled.
| | 00:24 | Notice there is a space in there, no underscore.
| | 00:27 | We want to set that equal to a value
of 1, but the syntax here rather than
| | 00:32 | use the equal sign so we use a comma.
| | 00:35 | So this will reconfigure our
server to set clr enabled equal to 1.
| | 00:40 | After issuing a command like that you
have to issue another command called
| | 00:44 | RECONFIGURE and that should be all we need.
| | 00:50 | We notice at the bottom it says configuration
options clr enabled change from 0 to 1.
| | 00:55 | So in other word, it went
from turned off to turn on.
| | 00:58 | So that's the first step.
| | 01:00 | CLR is now enabled for the
entire instance of SQL Server.
| | 01:04 | There's no way to turn this on and
turn it off for one particular database.
| | 01:08 | It is a setting that applies to the
entire server, not just one database.
| | 01:13 | Also, once it turned on it will remain on.
| | 01:15 | So even if you reboot the server when
it comes back up, this would still be on.
| | 01:20 | The only way to turn it off would be to
issue the same command and use a 0 instead of 1.
| | 01:27 | Now that clr is enabled we can add an assembly.
| | 01:30 | So under myDatabase we have Programmability
and the fourth option below that is Assemblies.
| | 01:36 | It should already be one
assembly and there a default assembly
| | 01:40 | Microsoft.SqlServer.Types.
| | 01:42 | We can now add our own assembly.
| | 01:44 | An assembly is basically a DLL that
was written in any of the .NET languages.
| | 01:49 | For now I am going to
assume that DLL already exists.
| | 01:52 | Let's say it was written by a
different developer, although a few movies down
| | 01:56 | the road we're going to be ride in our own DLLs.
| | 01:59 | So we right-click, say New Assembly, and
we will have to say where that file is
| | 02:04 | and now have an assembly called Hello World.
| | 02:11 | Again, that's a DLL that was written in C#.
| | 02:15 | Compiled inside of that assembly is
some code to write a stored procedure.
| | 02:19 | So we will now need to tell SQL
Server that we want create a new stored
| | 02:23 | procedure based on the C# code.
| | 02:26 | The beginning of the syntax is very
similar to other stored procedures.
| | 02:29 | We will need the keyword CREATE and the
keyword PROC and then we have to give it a name.
| | 02:39 | Then instead of writing code here we're
going to tell the machine that this is
| | 02:44 | external to SQL Server.
| | 02:46 | So we say EXTERNAL name and
then we have to tell it the name.
| | 02:52 | The first part of this is going to be the
name of the Assembly which is helloworld.
| | 02:58 | The next part is the name of the class
that's inside of the Assembly, which I
| | 03:02 | happen to know is HelloWorldProc.
| | 03:08 | Then the third part is the name of the
particular method we're looking for which
| | 03:13 | is also HelloWorld.
| | 03:18 | So that successfully
created a new stored procedure.
| | 03:21 | Again the name in the EXTERNAL name
is three parts separated by periods.
| | 03:24 | It's the name of the assembly, dot name
of the class, dot name of the method, and
| | 03:32 | we have successfully created a stored procedure.
| | 03:35 | We can go onto our Stored Procedure
section and see a new one called test.CLR.
| | 03:40 | That's exactly what I expected,
and let's go ahead and execute that.
| | 03:49 | And it returns the phrase Hello world!
| | 03:50 | Again, we did not write any T-SQL there.
| | 03:54 | All of that was implemented in C#,
was compiled into a DLL, we imported the
| | 04:00 | DLL as an assembly, we created a new
stored procedure based on that DLL, and
| | 04:05 | now we can execute that stored
procedure just the exact same way as any other
| | 04:10 | stored procedure.
| | Collapse this transcript |
| Writing stored procedures with C# .NET| 00:00 | Now we're going to write some C# code that
will eventually become a stored procedure.
| | 00:05 | We'll start off by create a
new project in Visual Studio.
| | 00:09 | For the project type I
want it to be a Class Library.
| | 00:13 | The result of a Class Library is a DLL
and we will import that DLL into SQL Server.
| | 00:20 | We'll name our Class Library CLRtest.
| | 00:25 | Rather than use of the default
class they have, I'll create a new class.
| | 00:32 | And I'll call the class mySprocs.
| | 00:38 | The machine automatically wrote
some code for me.
| | 00:40 | Some of it I want, some of it I don't
want, and I'll keep the using statements,
| | 00:44 | but I'll also need to add a few of
my own using statements, because we're
| | 00:48 | working with a database, we
will need to add using system.Data.
| | 00:53 | We will also need to add using system.
Data.SqlClient and system.Data.SqlTypes.
| | 01:05 | One more with the Microsoft.SqlServer.Server.
| | 01:12 | So that should be all the
using statements we need.
| | 01:15 | I said I'd call myClass mySprocs.
| | 01:17 | It'll need to be marked as a public
class, and inside of that a class for now
| | 01:25 | we're only going to have one method.
| | 01:28 | The method will have to be
marked as public and static.
| | 01:32 | In this case it'll also be void,
because it does not return anything.
| | 01:37 | The name will be InstertAuthor which
is a descriptive name, because I plan to
| | 01:41 | use this method to insert one
record into the author's table.
| | 01:46 | Before I build out the body of the
method I'll need to decorate the method.
| | 01:51 | The decoration sends information to the
compiler on what we plan to do with this method.
| | 01:58 | It will slightly change
the behavior of the compiler.
| | 02:02 | In this case we will decorate with the phrase
| | 02:04 | Microsoft.SqlServer.Server.SqlProcedure,
and most other places it's called a
| | 02:14 | stored procedure and here
it's called a SqlProcedure.
| | 02:18 | So this declaration will tell the
compiler to compile this a little different,
| | 02:22 | because it's going to be a SQL stored procedure.
| | 02:26 | The body of the method will
need a connection to the database.
| | 02:31 | Typically, when creating a connection
to a database, we have to go through a
| | 02:35 | lot of effort to say the name of the
server or the IP address of the server,
| | 02:39 | the name of the database we want to connect
to, the permissions, et cetera, et cetera.
| | 02:43 | Here we can get off a lot easier,
because remember this code is going to be
| | 02:46 | running inside of a database.
| | 02:48 | So we can get away with just saying
connect to the database that we are part of
| | 02:54 | already, and we say context connection=true.
| | 02:59 | In other words, use the connection that
you are already part of the context of.
| | 03:05 | Execute a single SQL
statement, insert into authors.
| | 03:10 | For this case we're just going to insert
into two columns, FirstName and LastName.
| | 03:15 | We'll insert the VALUE Sally, Smith.
| | 03:18 | The next line, make sure that our
command and our connection are talking to each
| | 03:22 | other, and then we have three lines at
the bottom, one to Open the connection
| | 03:26 | one to ExecuteNonQuery.
| | 03:28 | That's a NonQuery, because we're not
expecting data to be returned and then we Close.
| | 03:33 | Obviously, best practice
to close whatever you open.
| | 03:37 | This looks like it's ready to go.
| | 03:39 | We can go ahead and Build, and off the
bottom we see a message saying build
| | 03:45 | succeeded in the very lower left-hand corner.
| | 03:48 | The only thing left to do
now is to save this DLL.
| | 03:51 | We're going to need to save it
someplace that's easy to find.
| | 03:55 | So I'll save it in the exercise files
and then we will save it as CLRtest.
| | 04:02 | Okay, so I should be done with Visual Studio.
| | 04:04 | Now I have to get into SQL Management
Studio and I want to import that assembly.
| | 04:10 | That will be a right-click > New
Assembly, and I'll have to browse to the same
| | 04:15 | directory I was just in, and
there's my DLL. That looks successful.
| | 04:28 | I now see the assembly CLRtest.
| | 04:31 | So now that CLRtest has been imported,
| | 04:34 | we can create a stored
procedure based off of it.
| | 04:38 | Keyword CREATE PROC, and
I'll need it to give it a name.
| | 04:43 | I could name it anything I want, but it
seems like the easiest thing would be to
| | 04:47 | use the same name here as I used in the
C# code which was InsertAuthor, and then
| | 04:55 | I'll say AS EXTERNAL NAME.
| | 04:58 | The first part is the name of the
assembly, and then the name of a class, and then
| | 05:07 | the name of the method
and that ran successfully.
| | 05:14 | Good news, good news. We will look at
our stored procedures, hit refresh and
| | 05:18 | we see InsertAuthor. So great!
| | 05:21 | Let's go ahead an execute that.
| | 05:26 | And it says again completed successfully.
| | 05:29 | Let's go ahead and look at the
AuthorsTable and we would expect to see one
| | 05:32 | record for Sally Smith, and there we go.
| | 05:36 | The last line Sally Smith has now been inserted.
| | 05:39 | So we've successfully wrote some C#
code, compiled that C# code into a DLL,
| | 05:44 | imported the DLL, made a stored
procedure based on that DLL, and we can execute
| | 05:49 | the stored procedure.
| | Collapse this transcript |
| Writing functions with .NET| 00:00 | In this section I am going to talk about
using C# to write a user defined function.
| | 00:06 | So here in Visual Studio we'll create
a new project. Again, I want it to be of
| | 00:11 | type Class Library and
I'll call it a CLRfunctions.
| | 00:19 | I prepared some code ahead of time that
you can find in your exercise files, so
| | 00:24 | I'll copy and paste that over to
Visual Studio and we can talk about it.
| | 00:28 | The top eight lines are typical using
statements that we have with any SQL
| | 00:32 | Server stored procedure or function, and
then I created a new class, starting on
| | 00:37 | line 10, public class myFunctions. There
is one method in there called isActive,
| | 00:43 | it is decorated with Microsoft.Sql.Server
.Server.Sql.Function. This will provide
| | 00:50 | a clue to the compiler that we
are in fact writing a SQL function.
| | 00:54 | Notice on line 13 we declared the method to be
public static and the Return type is SqlString.
| | 01:02 | When we're working with C# code,
that's going to eventually be executed
| | 01:06 | inside of SQL Server, we have to use
slightly different data types, rather
| | 01:10 | than use things like sting, an integer, we've
to specify it as a SqlString or a SqlInteger.
| | 01:17 | So we work with slightly different data types.
| | 01:19 | Here we have SqlIntegar as the Return
type of the function, and we're passing one
| | 01:24 | parameter called ActiveBit and that
is a SqlInt, in this case at 32bit
| | 01:30 | SqlInteger, so SqlInt32.
| | 01:34 | The point of this function, it is going
to read the bit, the 1 or 0 we used to
| | 01:39 | store whether or not someone is active,
and just return something that's a
| | 01:43 | little more friendly, return some text
that actually spells out the word Active
| | 01:48 | or Not Active, based on the value of that bit.
| | 01:51 | So we see some fairly straightforward
logic; line 15, 16, 17 and 18, a simple if
| | 01:57 | statement, if (ActiveBit == 1) we'll
return the phrase Active, else, we return
| | 02:04 | the phrase Not Active.
| | 02:06 | So this is all the code we need. We go
ahead and Build Solution, and in the lower
| | 02:11 | left we see the build succeeded.
That's certainly good news.
| | 02:14 | Now we'll need to save all of this in a
place that's easy to find, and now that
| | 02:21 | should be everything we
need to do in Visual Studio.
| | 02:23 | We will flip over to SQL Server
Management Studio, we will need to import the New
| | 02:30 | Assembly, and we'll have to browse
to where we just dropped that. We
| | 02:42 | successfully created the assembly CLRFunctions.
| | 02:46 | So now it's time to create our function.
| | 02:47 | I have again pre-staged some code for
you that's in your exercise files. We will
| | 02:55 | copy and paste that. We see it says
CREATE FUNCTION, we gave it a name, and then
| | 03:01 | it's going to take one parameter.
That parameter is an integer.
| | 03:04 | We say it RETURNS NVARCHAR.
| | 03:08 | So we talked earlier a little bit
about the differences in data types, C# is
| | 03:13 | going to return what it calls a SqlString.
| | 03:15 | SQL recognizes is that as the NVARCHAR
data type, and we'll have to match those up.
| | 03:22 | If those didn't match we would get
an error when creating this function.
| | 03:27 | And in the bottom part
EXTERNAL NAME, which again goes
| | 03:31 | assemblyname.classname.method, so
that CLRfunctions.myFunctions.isActive.
| | 03:39 | We execute that. It says Command(s)
completed successfully. That's good news.
| | 03:44 | I can come over here in my Functions
section and expand out Scalar-valued
| | 03:49 | Functions, hit a quick refresh, and I
see isActive is now a valid function.
| | 03:55 | So now we're going to write a
SELECT statement that actually uses that.
| | 04:02 | So I want to SELECT FROM the Authors table.
| | 04:08 | And I want to select three things:
FirstName, LastName, and this new function
| | 04:16 | we just created. That takes one input
parameter and that parameter will be the ActiveBit.
| | 04:26 | We run that, and we see every line
Returns Not Active, which is in fact accurate.
| | 04:32 | All of our Authors are In Active,
based on things we did in previous demos.
| | 04:37 | This might be a little bit more
interested if we had an author who was active,
| | 04:40 | so let's go ahead and do that.
| | 04:46 | We'll make John Doe, we'll make
him Active, and we'll run this again.
| | 04:52 | We see our function is operating
properly. Anytime the Active field is equal to
| | 04:57 | 1, our function writes the phrase
Active, and anytime the Active field is not
| | 05:04 | equal to 1, it writes the phrase Not Active.
| | Collapse this transcript |
| Choosing between T-SQL vs. CLR| 00:00 | Now that we've seen how to create a
stored procedures using T-SQL and how to
| | 00:04 | create stored procedures using C#,
you're going to be faced with a decision.
| | 00:09 | Every stored procedure you create, you
have to choose either T-SQL or C#. I'd like
| | 00:14 | to give you some tips on which one to use.
| | 00:18 | There's very little that can be done and
one that can't be done in the other. If
| | 00:22 | you try hard enough, you can
accomplish anything with either one.
| | 00:25 | But each one does have its
stress and its weaknesses.
| | 00:28 | T-SQL's strength is certainly querying data.
| | 00:32 | We can just go in there and directly write a
SELECT, UPDATE, INSERT or DELETE statement.
| | 00:37 | You don't have to set up a connection,
don't have to close a connection. We just
| | 00:41 | write our T-SQL statements and they run.
| | 00:44 | T-SQL however, is poor with complex logic.
| | 00:48 | We have very few decision structures.
Basically, you've an if, and not a whole
| | 00:53 | lot of else, and we have a
very few looping structures.
| | 00:55 | You have a while, and not a whole lot else.
| | 00:58 | T-SQL does not natively support, arrays,
collections, for-each or classes, and
| | 01:04 | all of those are things that
the typical programmer wants.
| | 01:07 | The .NET languages, their weakness
is querying data and it's not super
| | 01:12 | difficult to query data, but you're
going to have to write approximately 3 to 5
| | 01:16 | lines of code to set up a connection,
and then every time you want to use that
| | 01:21 | connection, you should manually open the
connection, and manually close the connection.
| | 01:25 | So it's placing some
administrative overhead on the developer.
| | 01:29 | The .NET however, is very robust
with looping and control of flow.
| | 01:34 | We've lots of great looping structures,
lots of great decision structures in
| | 01:38 | all of the .NET languages.
| | 01:40 | And the .NET languages can be compiled
for multiprocessor environments and will
| | 01:44 | run much faster and much more
efficiently in a multiprocessor environment.
| | 01:48 | So when making this choice, Microsoft
has clearly stated repeatedly that you
| | 01:55 | should use T-SQL for any stored procedure
where the primary focus is data access.
| | 02:01 | However, if you have complex logic
or a CPU intensive function, they
| | 02:05 | recommend the .NET framework.
| | 02:07 | I certainly agree with
both of those recommendations.
| | 02:11 | I'll go ahead and add a third one,
but you also have to think about human
| | 02:14 | resources, the people that are writing
the program, if they are much stronger
| | 02:18 | in T-SQL, or much stronger in C#, obviously,
that's going to influence your decision.
| | 02:24 | But if your team of developers has
approximately the same level of experience
| | 02:29 | with both, then certainly these
are valid ways to make the choice.
| | 02:32 | T-SQL for data access
and .NET for complex logic.
| | Collapse this transcript |
|
|
8. Exploring an ASP.NET Web ApplicationCreating a basic web form and connecting to a database| 00:00 | Now let's look at assembling some of the
pieces we're going to use to create our
| | 00:04 | web application that will communicate
with our SQL Server stored procedures.
| | 00:08 | Here in Visual Web Developer, I
am going to create a new website.
| | 00:13 | I'd like to use the template--a C#
template for an ASP.NET Empty Web Site.
| | 00:18 | I'd like to drop that in our Exercise
folder and we'll call it the AuthorsSite.
| | 00:25 | So this gives me an empty
solution with no data in there.
| | 00:32 | I'd like to add a new basic web form,
so I'll right-click and say Add New Item,
| | 00:37 | I'd like to add a web form and I'd
like to call that web form, AuthorsPage.
| | 00:42 | The machine will write some code for me,
but that--I'm not going to use that.
| | 00:49 | I've already written some code
that should be in the exercise files.
| | 00:53 | We'll look at the code real quick;
| | 00:55 | we have some styles that we're setting up.
| | 00:57 | Two styles, one for the headings, and
one for the body, and then we have a
| | 01:01 | table, that's creating a space for a
list of all active authors, and another
| | 01:06 | space for us to create a
form to insert new authors.
| | 01:11 | If we flip over to Design view, we can
see this, our two headings and two empty
| | 01:16 | spaces available and ready for
us to put exciting stuff in there.
| | 01:20 | Now I'd like to bring this home
with something a little more practical.
| | 01:24 | Obviously, users won't go directly to
the database to update and insert records.
| | 01:29 | More likely, they would go to an
application or a website to manipulate the
| | 01:33 | records in our database.
| | 01:35 | So I am going to go ahead and create a small
web application that connects to our database.
| | 01:40 | It'll use some stored procedures and
functions, and we'll see that if we take
| | 01:44 | some time and effort to set up our
stored procedures and functions correctly,
| | 01:48 | then the level of effort and the
amount of code we have to write on the
| | 01:52 | website is very easy.
| | 01:54 | The next thing we'd like to do is
create a connection between our web
| | 01:57 | application and our database.
| | 01:59 | We'll do this off of a
Window called Database Explorer.
| | 02:03 | Inside of Database Explore, the
top option is Data Connections.
| | 02:06 | We can right-click that and say Add Connection.
| | 02:09 | Up there you'll need to give it the name of
your SQL Server or the IP address. There is mine.
| | 02:15 | We have the option to Use Windows
Authentication of SQL Authentication.
| | 02:19 | I'm going to stick with Windows and
we will need to choose which database
| | 02:23 | we're interested in;
| | 02:24 | my database that we were working in previous
examples, hit Test Connection, it says succeeded.
| | 02:30 | That's good.
| | 02:32 | And now we see a new connection named
thusly, basically it's the name of our
| | 02:36 | server.name of the database.
| | 02:39 | To open that up in Explorer, we see the
tables we were working with previously.
| | 02:44 | And we also see the stored
procedures we worked with previously, and the
| | 02:48 | functions we worked with previously.
| | 02:50 | So we are very close to being able to
run the stored procedures off of the
| | 02:55 | web page.
| | Collapse this transcript |
| Executing a stored procedure| 00:00 | Now I'd like to go ahead and implement
the functionality that will call a
| | 00:04 | stored procedure and display data on the screen.
| | 00:07 | Here we can see we have a space
available for a list of active authors.
| | 00:12 | So first let's write a stored procedure
that returns all the active authors and
| | 00:17 | then we'll connect that to the web page.
| | 00:19 | I have some code prepared in the exercise files.
| | 00:22 | And it's a very simple stored
procedure that selects store from the Authors
| | 00:26 | table where Active equals 1. So that would be
all of our active authors. That looks good!
| | 00:32 | Going back to Visual Studio, I'll
insert my cursor into the appropriate table
| | 00:38 | cell, and off the toolbox
I want find the Data section.
| | 00:42 | So I'll scroll down a little and
underneath the Data section there is a GridView.
| | 00:49 | I'll drag our GridView over and it's
asking me a few questions; one of them,
| | 00:52 | what is my Data Source, and currently it's None.
| | 00:55 | I'll create a new Data Source.
| | 00:57 | I want that to be a SQL Server data source.
| | 01:02 | We created the connection a few
videos back, so now we have the option to
| | 01:06 | use that connection.
| | 01:09 | In this interface, it's asking me if I'd
like to use a table or a custom stored
| | 01:14 | procedure. I'd like to use
a custom stored procedure.
| | 01:17 | Then I'll hit next, and this one is asking
me the name of my stored procedure. We
| | 01:22 | just created getActiveAuthors.
That's the one I want.
| | 01:26 | We can hit a test, return some data and
that certainly looks like the accurate
| | 01:31 | data we were hoping for.
| | 01:32 | So I'll hit Finish.
We're now ready to test this.
| | 01:35 | So I'll go ahead and start debugging,
and look at that! We've got a web page and
| | 01:40 | we have got a list of all of
our active authors successfully.
| | 01:44 | We connected that stored procedure to
our website, didn't actually write any
| | 01:49 | code in the website just yet. We will be
getting into that a few sections down the road.
| | 01:54 | But for now it's just a matter of click
and drag and setting a few parameters, so
| | 02:00 | the bulk of the work was the
original setup of the stored procedure.
| | Collapse this transcript |
| Passing parameters| 00:00 | Now it's time to implement the
functionality that will allow the user to
| | 00:04 | insert a new author.
| | 00:06 | In order to do this, we'll have to get
our web page to accept some input and
| | 00:10 | then pass that input as
parameters to a stored procedure.
| | 00:14 | First let's create the stored
procedure that expects the parameters.
| | 00:19 | I have pre-staged a stored procedure.
| | 00:22 | We'll go ahead and look at that code.
| | 00:24 | The procedure's name is InsertNewAuthor
and accepts two parameters, a firstName
| | 00:28 | parameter and a lastName parameter,
and then it executes a simple INSERT
| | 00:33 | statement, inserted into the Authors
table, FirstName LastName and the Active
| | 00:38 | status of 1. Command(s) completed successfully.
That looks good.
| | 00:43 | Now back on our web page, I'll insert my
cursor into the blank area under insert
| | 00:48 | new author, and I'm going to type the
phrase FirstName and then, I'll drag a text
| | 00:55 | box from the Toolbox that will
be used to insert the FirstName.
| | 01:00 | I'll then put a hard Return after
that and type LastName, drag another
| | 01:05 | textbox, and then under all that,
we'll want a single button that will cause
| | 01:12 | the insert to happen.
| | 01:14 | I want to rename that first textbox, so I'll
right-click on it and look at the Properties.
| | 01:19 | At the very bottom of the Properties,
it currently has the ID of TextBox. It's
| | 01:23 | not very descriptive.
| | 01:24 | I'd like it to have the ID of FirstName.
| | 01:28 | and similarly the other textbox
should have the ID of LastName.
| | 01:34 | So that's ready to go.
| | 01:35 | Now I'll double-click on the button and
that'll take me to the area where I can
| | 01:39 | write the code, and again, I have pre-staged
some code, so I'm going to delete
| | 01:44 | all of the stuff that's already
there and copy and paste my own.
| | 01:49 | Looking at the top, we have several
using statements that are all necessary to
| | 01:53 | communicate between the web page and
the database, and then we see the bulk of
| | 01:57 | our work happens here in
the Button_Click method.
| | 02:00 | We're going to create a new connection
and I'll scroll over and show you that
| | 02:04 | connection is based on the
ConnectionString we created previously,
| | 02:08 | myDatabaseConnectionString.
| | 02:11 | We'll create a new command, the
CommandText will be InsertNewAuthor, the exact
| | 02:16 | name of our stored procedure and the
CommandType will be StoredProcedure and
| | 02:21 | we'll connect the command to the connection.
| | 02:23 | Now for the parameters.
We're creating two parameters:
| | 02:26 | one called firstNameParam and one
called lastNameParam. These correspond to the
| | 02:31 | parameters the stored procedure is
expecting called firstName and lastName
| | 02:35 | respectively, and they will get their values
from the textbox, FirstName.Text and LastName.Text.
| | 02:43 | Just creating the parameters
doesn't connect them to the command.
| | 02:47 | So we have to do that in two
additional lines of code, where we add the
| | 02:51 | parameters to the command.
| | 02:53 | Then we have our typical open the connection,
ExecuteNonQuery and close the connection.
| | 02:58 | I've also included one more line of
code to refresh the DataGrid. The DataGrid
| | 03:04 | that's on the top of the screen that
shows our active authors will now need to
| | 03:08 | be refreshed, because we just
inserted a new author. So let's test this.
| | 03:14 | So for my new author, we'll do a
FirstName of Steven, LastName of Jones, hit the
| | 03:22 | button, and look at that, in our Active
Authors grid, we now see Steven Jones.
| | 03:27 | So this proves we successfully made a
round-trip from the web page, it took the
| | 03:32 | parameter Steven and the parameter
Jones, passed those two parameters to the
| | 03:37 | stored procedure and the stored
procedure executed as desired.
| | Collapse this transcript |
| Getting return values| 00:00 | I mentioned earlier to you a
recommendation that as a best practice;
| | 00:04 | every stored procedure return a
value indicating success or failure.
| | 00:08 | Typically, we return a 0 for
failure and the 1 for success.
| | 00:12 | We haven't yet implemented that.
| | 00:14 | So let's go ahead and work on it.
| | 00:16 | I'll change this code that creates a
stored procedure into a code that alters
| | 00:20 | a stored procedure.
| | 00:21 | The last allow will be RETURN 1 for success.
| | 00:26 | We also need to define some failure condition.
| | 00:28 | As a hypothetical, we're going to say
that if the first name is too short,
| | 00:33 | let's say extremely short, less than
two characters, we'll define that as
| | 00:37 | a failure condition.
| | 00:38 | Check for the if len(@FirstName).
| | 00:42 | If it's less than 2 characters, that is
now defined as a failure condition.
| | 00:47 | So we'll need to RETURN a 0 to indicate
this stored procedure was unsuccessful.
| | 00:52 | And as soon as it hits that line of
RETURN 0, it will not execute the bottom.
| | 00:57 | It will not do the insert.
| | 00:59 | Command(s) completed
successfully. That's good news.
| | 01:01 | Now we need to go over to our
website and do a little more work.
| | 01:06 | In order to accept that return value
we're going to have to create a new parameter.
| | 01:10 | So websites accept return values from
stored procedures as a special type of parameter.
| | 01:15 | I have some code already
written in your exercise files.
| | 01:18 | I'm going to insert that now.
| | 01:21 | There is one chunk that goes right
before the connection.open and another chunk
| | 01:27 | they goes right after connection.close.
| | 01:31 | Now let's look at what this code does.
| | 01:32 | The top part creates a new parameter
called returnParameter, and it sets an
| | 01:37 | unusual direction on that.
| | 01:39 | For every parameter we have
the choice to set a direction.
| | 01:42 | Our choices are Input, Output, both
Input and Output, or the one I'm going to
| | 01:48 | choose ReturnValue.
| | 01:50 | And this line just adds the
parameter to the existing commands.
| | 01:55 | Below the close, I added some code that
reads the value of the return value and
| | 02:00 | checks to see if it's equal to 0.
| | 02:02 | If it is in fact equal to 0, that is
an error condition and I'm going to put
| | 02:06 | a label on the screen that will hold the
message this is an error in the stored procedure.
| | 02:11 | So I'll need to come back over to the
graphical port and drag a Label right
| | 02:16 | next to the button.
| | 02:17 | I'll go ahead and run this, and I'm
going to intentionally enter some bad data.
| | 02:24 | So for the first name I'll make sure
does in fact too short and we receive the
| | 02:29 | message there was an
error in the stored procedure.
| | 02:32 | It did not do the insert and
instead did what we expected.
| | 02:35 | It returned a 0 to us indicating
the stored procedure had failed.
| | 02:40 | So this follows a common best practice
of using a 0 to indicate failure, a 1 to
| | 02:46 | indicate success, and every stored
procedure should always return either
| | 02:51 | success or failure.
| | Collapse this transcript |
|
|
ConclusionNext steps| 00:00 | This concludes our course on Microsoft SQL
Server Triggers Functions and Stored Procedures.
| | 00:06 | I want to thank you for your time.
| | 00:08 | I hope you enjoyed watching this
course as much as I enjoyed making it.
| | 00:11 | I'd like to leave you with a few resources
| | 00:14 | you can look into if you have further
questions about Microsoft SQL Server or
| | 00:18 | just want to explore the vast world
of SQL Server information out there.
| | 00:22 | The first one I'll recommend
to you as Microsoft itself.
| | 00:25 | Personally, I feel SQL Server is the best
documented product I've ever worked with.
| | 00:30 | So I often visit the Microsoft
website, probably on a daily basis;
| | 00:34 | microsoft.com/sqlserver will take you
to the official Microsoft website with
| | 00:40 | lots of good information
directly from Microsoft.
| | 00:42 | One of the most important areas
for beginners is the Learning Center.
| | 00:47 | Another good resource is SQL Server
Central which you can find just at
| | 00:50 | sqlservercentral.com.
| | 00:53 | It has lots of good information, mostly
developed by a community of SQL Server
| | 00:57 | developers and practicing DBAs.
| | 00:59 | Some of the content here
does require you to sign up.
| | 01:02 | It is a free sign up.
| | 01:03 | I recommend you do that.
| | 01:05 | I subscribed to a few of their newsletters.
| | 01:07 | About once a week they'll send you
lots of good information, specifically on
| | 01:11 | SQL Server and also about database
administration and database development in general.
| | 01:16 | The last thing I'd like to
recommend to you is my blog which is
| | 01:19 | windowsdba.blogspot.com.
| | 01:24 | I maintain a blog about Microsoft SQL
Server and also some information about
| | 01:29 | Microsoft Windows, particularly things
that will affect how SQL Server runs and
| | 01:34 | things that developers and
DBAs need to be aware of.
| | 01:38 | Again, thank you for your time and I
wish you the best of luck on your career as
| | 01:42 | a database developer.
| | Collapse this transcript |
|
|
