Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
All these functions that we have are great and they're very useful and you'll use them all the time, but you probably won't end up writing a lot of them. However, spend any time with SQL Server and you will write stored procedures. A stored procedure is simply a chunk of SQL wrapped up, given a name, and then stored in the database. It can then be executed multiple times from SQL Server Management Studio, called from an application. It's available to anyone who can get to the database.
And these will be the main way that you reuse SQL. Talk to developers and database administrators and they'll tell you that almost all of the SQL they write is intended for a stored procedure. Now we've written a lot of SQL in the past few hours, but if I'd wanted to save and reuse any of this I simply would have just have the option to go to my File menu and save this as a text file on my desktop. Well, that's fine for some personal learning and testing. But when you write some more complex SQL that you're happy with and you want to reuse it, you want to wrap it up in a stored procedure so you can use it later.
Like there are lots of built-in functions, there are hundreds of stored procedures already in SQL Server. If I expand any of the databases here, even the AdventureWorksLT, open up Programmability, this is where we saw our functions earlier. We also have stored procedures, and if I expand the System Stored Procedures here I see just quite a ridiculous amount of stored procedures, There is hundreds of them. Having said that, what you'll find is that many of these are intended for advanced administration tasks.
Working with Active Directory policy management, logging, optimizing your indexes and your partitions, and it's very common that that'll begin with Sys for sys.sp_, meaning stored procedure of something. I'll find in some of these test databases, there are also a couple of user-defined stored procedures, which you'll often see with USP at the start of them. There is couple of them in AdventureWorks Light. There is a few more if you look at the larger AdventureWorks examples.
Now there are similarities between creating a stored procedure and creating a function. They both need a name, and they can be defined with parameters. They can return values. Well, that does sound very similar to a function, but there are some substantial differences really about what they're intended for. Functions are typically designed to return a scalar value and they're SELECT only. they're not allowed to change anything. If you write a function, you cannot put an INSERT, an UPDATE or DELETE inside it.
It's designed to be a quick way of returning a value. Functions themselves are designed to be used in-line as part of other SQL statements. So, while a function can be used inside of an INSERT or an UPDATE, it can't itself do an INSERT or an UPDATE. Stored procedures on the other hand are much bigger in scale and much bigger in scope. And they are executed directly. There is in E-X-E-C, EXEC statement. You execute the entire stored procedure.
Stored procedures can do multiple things including inserting and updating and deleting. Stored procedures themselves can use functions, but functions won't be using stored procedures. Stored procedures can do multiple things at the same time. They can contain batches of SQL. Particularly useful if you want to do say an insert to several different tables at the same time. They're great for that. And after just taking your SQL and wrapping up and saving it as a stored procedure we can add parameters, variables, error handling, even flow control, having basic control in the case of IF statements.
It's very much like turning it into a mini program. And in fact I've worked in several places, which did not allow developers to write their own INSERTs, UPDATEs, DELETEs, even SELECT statements in their programs. They had to execute stored procedures. One of the benefits of that is all the SQL stays in the database and can be controlled and hopefully optimized by the database administrator. But let's see how to do one.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.