C H A P T E R 12 Stored Procedures, Functions, and Security Now that you know how to build queries written as single executable lines of T-SQL statements, it is time to look at how to place these into a stored procedure or a function within SQL Server, allowing them to be run as often as they are required without the need to be retyped every time. It also allows code to be written that can accept input parameters, return a status, return rows of data, and, most importantly, allow you to improve the security of your database. You will see all of this and more in this chapter. Stored procedures and functions are two different types of objects that provide different, yet similar, functionality. You will see these differences within the examples, but the main point is that a stored procedure is a set of code that runs as its own unit of work, while a function, which also runs as its own unit of work, is executed within the context of another unit of work. When building tables, you saw the system function GETDATE(). When I discuss functions later in this chapter, you will learn more about both the similarities and differences between these two types of objects. Although you may save queries on a disk drive somewhere, you have not stored them within SQL Server itself up to this point, nor have you saved them as multiple units of work. Often, however, you need to execute multiple queries in series from SQL Server. To do this, you employ stored procedures or functions. SQL Server assumes that a stored procedure or a function will be run more than once. Therefore, when it is executed for the first time, a query plan is created for it, detailing how best to execute the query. It is also possible, just like any other database object, to assign security to a stored procedure or a function, so that only specific users can run it, lending added security compared to a one- time-only query saved to a hard drive. The aim of this chapter is to build a simple stored procedure that will insert a single row into a table. I will then cover error handling and controlling the flow of execution within the procedure. Once a stored procedure that has several steps has been built, you will see how you can use tools within SQL Server to debug your code. You will then move on to building a user-defined function and invoking it. You will look at some system functions in Chapter 13. Therefore, this chapter will do the following: · · · · · · · Describe what a stored procedure is Explain the advantages of a stored procedure over a view Cover the basic syntax for creating a stored procedure Show how to set values within variables Control the flow through a stored procedure Look at the differences between a function and a stored procedure Cover the basic syntax for creating a T-SQL user-defined function 445