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

First let’s take a look at what a stored procedure is.

What Is a Stored Procedure?

In simplest terms, a stored procedure is a collection of compiled T-SQL statements and commands that are directly accessible by SQL Server. The code placed within a stored procedure is executed as one single unit, or batch, of work. The benefit of this is that network traffic is greatly reduced, because multiple T-SQL statements are not forced to travel over the network individually. Only the name of the stored procedure to execute and the parameters to it need to be transmitted. In addition to executing SELECT, UPDATE, INSERT, or DELETE statements, stored procedures are able to call other stored procedures, use statements that control the flow of execution, create temporary tables using different commands, and perform aggregate functions or other calculations.

Any developer with access rights to create objects within SQL Server can build a stored procedure. There are also hundreds of system stored procedures, all of which start with a prefix of sp_, within SQL Server.

There is little point in building a stored procedure just to run a set of T-SQL statements only once; conversely, a stored procedure is ideal for when you want to run a set of T-SQL statements many times. The reasons for choosing a stored procedure are similar to those that would persuade you to choose a view rather than letting users access table data directly. Stored procedures also supply benefits; for example, SQL Server will always cache a stored procedure plan in memory, and it is likely to remain in cache and be reused, whereas ad hoc SQL plans created when running ad hoc T-SQL may or may not be stored in the procedure cache. The latter may lead to bloating of the procedure cache with lots of very similar plans for similar batches, as SQL Server won’t match plans that use the same basic code but have different parameter values, for example.

Stored procedures give your application a single proven interface for accessing or manipulating your data. This means that you keep data integrity, make the correct modifications or selections to the data, and ensure that users of the database do not need to know structures, layouts, relationships, or related processes required to perform a specific function. You can also validate any data input and ensure that the data brought into the stored procedure are correct.

Just like views and tables, you can grant very specific execute permission for users of stored procedures (the only permission available on a stored procedure is EXECUTE). Once you have worked with stored procedures, I will go through the security of each object from a list of tables, views, and stored procedures and how secure or insecure each can be.

To prevent access to the source code, you can encrypt stored procedures, although this really ought to be used in only the most required cases. The code itself isn’t actually encrypted; it is only obfuscated, which means it is possible to decrypt the code if required. Therefore, it isn’t a total prevention of viewing the code, but it does stop stray eyes. It also limits what can be seen in a tool called SQL Server Profiler, which is used to profile performance of stored procedures, code, and so on, thus causing difficulty in checking what is happening if there is a problem. SQL Server Profiler is not available in the Express Edition of SQL Server. Therefore, to reiterate, you need to carefully justify any “encryption” you want to do.

Creating a Stored Procedure

Now that you know what a stored procedure is, it is time to create your first stored procedure. Once the syntax has been covered, you will build a stored procedure in Management Studio that will return a set of rows and multiple sets of rows, and then you will move on to look at controlling the flow through your stored procedure. Some of the stored procedures built in this chapter will then be used in Chapter 16, when I demonstrate how to connect to SQL Server using different development languages and how to execute stored procedures.

The first task, though, is to take a look at the syntax.

CREATE PROCEDURE Syntax

Begin a stored procedure with a CREATE PROCEDURE statement. The CREATE PROCEDURE syntax offers a great many flexible options and extends T-SQL with some additional commands. The syntax generally appears as follows:

First of all, it is necessary to inform SQL Server which action you want to perform. Obviously, you want to create a stored procedure, so you need to supply a CREATE PROCEDURE statement.

The next part of the syntax is to give the procedure a name. It would be advisable, just as it is with any SQL Server object, to adhere to a naming standard. Everyone has their own standard within their installation, but if you prefix the name with sp_, a very common naming convention, then you will know what that object is. However, this is not something I recommend, for two reasons. The first is that stored procedures prefixed by sp_ are seen as system stored procedures, and therefore, if you name your stored procedures similarly, confusion will occur. If you then create a stored procedure with sp_ as the prefix and Microsoft releases a system stored procedure of that name, then even more confusion will occur. I have seen user stored procedures with a double underscore in their name, such as sp__, which is where a developer has expanded the functionality of a system stored procedure, in this case sp_who. This is valid. The second reason is that you can hit some unnecessary compile locks because of system stored procedure lookups. If you name your stored procedure with a prefix of sp_, then SQL Server checks the master database for the stored procedure first. Therefore, do avoid this naming convention.

Many people adopt a different naming convention whereby the prefix defines what the stored procedure will do; for example, an update would have a prefix of up, a deletion dt, and a selection sl. There are many different prefixes you could use, including prefixing the name of the stored procedure with the system name. Some large systems are split into subsystems but share the same database. One example is when you have one database for your whole company but there are subsystems for purchasing, sales, and so on. Once you have decided on your standard, you should stick with it.

Some procedures may require information to be provided in order for them to do their work; this is achieved by passing in a parameter. For example, passing in a customer number to a stored procedure would provide the necessary information to allow creation of a list of transactions for a statement. More than one parameter can be passed in: all you do is separate them with a comma.

Any parameter defined must be prefixed with an @ sign. Not all procedures will require parameters, so this is optional; however, if you do want to pass in parameters to a stored procedure, name the parameters and follow them with the data type and, where required, the length of the data to pass in. For example, the following specifies a parameter of name L_Name, with a data type of varchar of length 50:

You can also specify a default parameter value in the event that a user does not provide one at execution time. The value specified must be a constant value, like 'DEFAULT' or 24031964, or it can be NULL. It is not possible to define a variable as a default value, since the procedure cannot resolve this when the procedure is built. For example, if your application is commonly, but not exclusively, used by the marketing department, you could make the department parameter variable optional by setting a default of 'marketing':

Thus, in this example, if you were from marketing, you would not need to provide the department input. If you were from information services, however, you could simply provide a value for a department that would override the default.

It is also possible to return a value, a number of values, or even a table of data from a stored procedure using a parameter to pass the information out. The parameter would still be defined as if it were for input, with one exception and one extra option. First of all, the exception: It is not possible to define a default value for this parameter. If you try to do so, no errors will be generated, but the definition will be ignored. The extra syntax option that is required is to suffix the parameter with the keyword OUTPUT. This must follow the data type definition:

You are not required to place OUTPUT parameters after the input parameters; they can be intermixed. Conventionally, however, try to keep the OUTPUT parameters until last, as it will make the stored procedure easier to understand.

Before continuing, one last thing about parameters needs to be discussed, and it has to do with executing the procedure and working with the defined parameters. When it comes to executing a stored procedure that has input parameters, you have two ways to run it.

The first method is to name the stored procedure and then pass the input values for the parameters in the same order that they are defined. SQL Server will then take each comma-delimited value set and assign it to the defined variable. However, this makes an assumption that the order of the parameters does not change, and that any default value-defined parameters are also set with a value.

The second, and preferred, method of executing a stored procedure is to name the parameter, and follow this with the value to pass in. You are then ensuring that, at execution time, it doesn’t matter what order the stored procedure has named the parameters, because SQL Server will be able to match the parameter defined with the parameter defined within the stored procedure. You then don’t need to define a value for parameters that already have default values. Also, if the stored procedure needs to be expanded, for backward compatibility, any new parameters can be defined with default values, therefore removing the need to change every calling code. Finally, when you call a stored procedure from code such as .NET, Excel, etc., as you will see in Chapter 16, each parameter has to be named.

There will be examples of each of the two different methods of passing in values to parameters within this chapter.

Next come two options that define how the stored procedure is built. First of all, just as a reminder, a stored procedure, when first run without an existing plan in the procedure cache, is compiled into an execution plan, which is an internal data structure in SQL Server that describes how it should go about performing the operations requested within the stored procedure. SQL Server stores the compiled code for subsequent executions, which saves time and resources. If there is an existing plan in the procedure cache, then SQL Server will use this.

However, the RECOMPILE option on a stored procedure dictates to SQL Server that every time the stored procedure is run, the whole procedure is recompiled. Typically, when a parameter can greatly affect the number of rows returned, you may want to add the RECOMPILE option to a stored procedure to force the optimizer to produce the best plan every time (i.e., you want to avoid reuse of a plan that may not be very good for certain parameter values).

The second of the two options is the ENCRYPTION keyword. It is possible to encrypt—well, obfuscate at least—a stored procedure so that the contents of the stored procedure cannot be viewed easily. Keep in mind that ENCRYPTION does not secure the data, but rather protects the source code from inspection and modification. Both ENCRYPTION and RECOMPILE are preceded by the WITH keyword and can be employed together when separated by a comma:

The keyword AS defines the start of the T-SQL code, which will be the body of the stored procedure. AS has no other function, but is mandatory within the CREATE PROCEDURE command defining the end of all variable definitions and procedure creation options. Once the keyword AS is defined, you can then start creating your T-SQL code.

It is then possible to surround your code with a BEGIN...END block. I tend to do this as a matter of course so that there is no doubt where the start and end of the procedure lie.

There is one option not covered, and that is EXECUTE AS, which I will cover later in the chapter.

Returning a Set of Rows

One method of achieving output from a stored procedure is to return a set of rows, also known as a result set. This result set may contain zero, one, or many rows as a single batch of output. It is also possible to return more than one result set and then process these one result set at a time. This is not common but does happen, and there can be some small performance gains from doing this. Returning a result set is achieved through the use of the SELECT statement within a stored procedure—what is selected is returned as the output of the stored procedure.

In this chapter, you will see single and multiple result sets of data returned and how these look within Query Editor.

Creating a Stored Procedure: Management Studio

Now that you have seen some of the merits of a stored procedure over other methods of working with data, it is time to create the first stored procedure in this chapter. This stored procedure will be built within SQL Server Management Studio to insert a customer into the CustomerDetails.Customers table from the information passed to it. This is also the first part in the overall security solution. By using a stored procedure to enter the data into the underlying table, you will be in control of what data are entered, as the data can be validated and verified. You can also remove all access from the table and leave the stored procedure to serve as the only method of inserting data (you would also have stored procedures that update, delete, and retrieve data). You will look at this toward the end of the chapter.

TRY IT OUT: CREATING A STORED PROCEDURE USING SQL SERVER MANAGEMENT STUDIO

Different Methods of Execution

There are two different methods of executing a stored procedure. The first is to just call the stored procedure, as you saw in the preceding example. The second method is to use the EXEC(UTE) command. Both have the end result of invoking the stored procedure, but which is better for you to use depends on the particular situation.

• No EXEC: It is possible to call a stored procedure without prefixing the stored procedure name with the EXEC(UTE) statement. However, the stored procedure call must be the first statement within a batch of statements if you want to execute this statement.
• With EXEC: As I have just indicated, if the stored procedure call is the second or subsequent statement within a batch, then you must prefix the stored procedure with the EXEC(UTE) statement. On top of this, if you are calling a stored procedure within another stored procedure, then you will need to prefix the call with the EXEC(UTE) statement.

Returning Results

A stored procedure is built for more than inserting, updating, or deleting data. It is also used to return data from a return value, a value in a parameter, a value within a single column of a single row known as a scalar value, or finally in one or more record sets. In the following sections, you will see each of these types of stored procedures, which will then be used in Chapter 16, when you look to call these from within an executable.

The first area you will look at is returning a value. This could be an error code or a specific value denoting a piece of information.

Using RETURN

One reason for returning a value from a stored procedure is to indicate an error; this is done with the RETURN statement. This statement immediately stops a stored procedure and passes control back out of it. Therefore, any statements after the RETURN statement will not be executed.

It is not compulsory to have a RETURN statement within your code; it is necessary only when you want to either return an error code or exit from a stored procedure without running any further code from that point. A logical RETURN is performed at the end of a stored procedure, returning a value of 0.

By default, 0 is returned if no value is specified after the RETURN statement, which means that the stored procedure was successful. Any other integer value could mean that an unexpected result occurred and that you should check the return code. It is also possible to return the number of rows affected by the stored procedure. Notice that the word “error” wasn’t mentioned, as it may be valid for a nonzero return code from a stored procedure.

In this example, you will create a stored procedure that will return two output parameters back to the calling procedure or code, indicating the cleared and uncleared balances of a specific customer.

The stored procedure will also use RETURN to indicate whether the customer ID passed to the stored procedure finds no rows. Note that this is not an error, as the stored procedure code will be working as expected.

You are probably wondering when to use output parameters and when to use RETURN. Output parameters are used to return information to a calling set of code and can handle any data type. On the other hand, a RETURN can return only an integer numeric value and is used more often for indicating success or failure.

TRY IT OUT: USING RETURN AND OUTPUT PARAMETERS

You have now built two very basic stored procedures in which you are performing an INSERT and a SELECT. Next you will look at returning multiple result sets.

Returning Multiple Result Sets

As mentioned earlier, although not common, it is possible to return multiple result sets to your calling program. You will see how to handle this in Chapter 16. In the ApressFinancial database, you may want to return every share with the share details and the price movement of each share price for analysis. You could call two stored procedures, one for each set of data, or you could call one stored procedure that completes both actions. There is nothing new from a SQL Server viewpoint, but here you create a stored procedure that will be used when you call the stored procedure from an example in Chapter 16.

TRY IT OUT: RETURNING MULTIPLE RESULT SETS

Figure 12-8. Results after running the OUTPUT stored procedure

Controlling the Flow

When working on a stored procedure, there will be times when it is necessary to control the flow of execution through it. The main control of flow is handled with an IF...ELSE statement. You can also control the flow with a WHILE...BREAK statement.

Controlling the flow through a stored procedure will probably be required when a procedure does anything more than working with one T-SQL statement. The flow will depend on your procedure taking an expression and making a true or false decision, and then taking two separate actions depending on the answer from the decision.

IF...ELSE

At times, a logical expression will need to be evaluated that results in either a true or false answer. This is where an IF...ELSE statement is needed. You have come across this statement a few times within the chapter. There are many ways of making a true or false condition, and most of the possibilities involve relational operators such as <, >, =, and NOT; however, these can be combined with string functions, other mathematical equations, comparisons between values in local variables, or even system-wide variables. It is also possible to place a SELECT statement within an IF...ELSE block, as long as a single value is returned.

A basic IF...ELSE statement block would perhaps look like the following:

IF...ELSE statements can also be nested and would look like the following. This example also shows you how to include a SELECT statement within an IF decision.

As you can see, there is only one statement within each of the IF...ELSE blocks. If you want to have more than one line of executable code after the IF or the ELSE, you must include another control-of-flow statement, the BEGIN...END block.

BEGIN...END

If you want to execute more than one statement in the IF or ELSE code block, you need to batch the statements up. To batch statements together within an IF...ELSE block, you must surround them with a BEGIN and END block. If you try to have more than one statement after the IF, the second and subsequent statements will run no matter what the setting of the IF statement is.

So if you have the following code, then the SELECT 2 statement would run no matter what value you have for @VarTest.

If you want SELECT 2 to run only when @VarTest is 1, then you would use the following code, thus placing the code you want to run within the BEGIN...END block.

If you use an ELSE statement after a second or subsequent statement after an IF that has no BEGIN...END block, you would get an error message. Therefore, the only way around this is to use BEGIN...END.

WHILE...BREAK Statement

The WHILE...BREAK statement is a method of looping around the same section of code from zero to multiple times based on the answer from a Boolean test condition, or until explicitly instructed to exit by the BREAK statement.

The syntax for this command is as follows:

The code defined for the WHILE statement will execute while the Boolean expression returns a value of True. You can have other control-of-flow statements such as an IF...ELSE block within your WHILE block. This is where BREAK and CONTINUE could be used if required. You may want to test a condition and, if it returns a particular result, BREAK the loop and exit the WHILE block. The other option that can be used is the CONTINUE statement. This moves processing straight to the WHILE statement again and will stop any execution of code that is defined after it. The best way to illustrate these concepts is to show a simple example of these three options in action.

TRY IT OUT: WHILE...BREAK

The third statement you will look at in this section is the CASE statement. While not a control-of-flow statement used in your stored procedure, it also can control the execution flow.

CASE Statement

When a query has more than a plain true or false answer—in other words, when there are several potential answers—you should use the CASE statement.

A CASE statement forms a decision-making process within a SELECT or UPDATE statement. It is possible to set a value for a column within a result set based on a CASE statement and the resultant value. Obviously, with this knowledge, a CASE statement cannot form part of a DELETE statement.

Several parts of a CASE statement can be placed within a stored procedure to control the statement executed depending on each scenario. Two different syntaxes exist for the CASE statement depending on how you want to test a condition or what you want to test. Let’s take a look at all the parts of the first CASE statement syntax:

First, you need to define the expression that is to be tested. This could be the value of a variable, a column value from within the T-SQL statement, or any valid expression within SQL Server. This expression is then used to determine the values to be matched in each WHEN clause.

You can have as many WHEN clauses as you want within the CASE condition, and you do not need to cover every condition or possible value that could occur. Once a condition is matched, then only the statements within the appropriate WHEN block will be executed. Of course, only the WHEN conditions that are defined will be tested. However, you can cover yourself for any value within the expression that has not been defined within a WHEN statement by using an ELSE condition. This is used as a catch-all statement. Any value not matched would drop into the ELSE condition, and from there you could deal with it.

The second syntax is for computing a value depending on the condition.

As just indicated, CASE statements can be part of a SELECT, UPDATE, or INSERT statement, therefore possibly working on multiple rows of data. As each row is retrieved from the table, the CASE statement kicks in, and instead of the column value being returned, it is the value from the decision-making process that is inserted instead. This happens after the data have been retrieved and just before the rows returned are displayed in the results pane. The actual value is returned initially from the table and is then validated through the CASE statement; once this is done, the value is discarded if no longer required.

Now that you are familiar with CASE statements, you can look at them in action.

TRY IT OUT: USING THE CASE STATEMENT

Figure 12-12. Searched CASE statement output

Bringing It All Together

Now that you have seen the control-of-flow statements, you can bring all of this together in the most complex set of code so far. The aim of this stored procedure is to take a “from” and “to” date, which can be over any period, and return the movement of a particular customer’s transactions that have affected the cash balance. This mimics your bank statement when it says whether you have spent more than you have deposited.

In the following example, you will be returning a value that will be used as a “scalar” value within Chapter 16.

TRY IT OUT: BRINGING IT ALL TOGETHER

Security

Now that you have tables, views, and stored procedures, the current system is so secure that nobody has access to any data once a login has been granted to ApressFinancial. The only people that have access are database owners. This is very restrictive, and you need to be able to give the right database users the correct access. In the following section, I will discuss further areas of security, building on the foundations laid in Chapter 4.

Security Gains

The two worst scenarios from a security standpoint are that all database users are database owners or that you grant all database users full access rights to all objects. In this section of the chapter, I will cover security, what is best practice, and how to implement this.

Here is a quick reminder of some terminology for a reference point before you proceed:

• Windows user id/account: An account used to log on to a Windows computer
• Windows group: A group of users that can be created in some Windows editions and on networks that allow Windows accounts to be grouped for simplifying tasks
• Server login: Allows you to connect to the server
• Database user account: Allows a server login to connect to a database
• Principal: A name given to a collection of security-based items; examples of a principal are application roles, database users, a Windows login, and a Windows group.

First of all, here is a quick summary of the current security settings from earlier in the book. In Chapter 4, you created two Windows accounts, MSmith and StepBrown, as well as two Windows groups, Apress_Product_Controllers and Apress_Client_Information. MSmith was then added to Windows group Apress_Product_Controllers. The Windows group was added as a server login to SQL Server, and this allows anyone in that Windows group to log on to SQL Server. The same process was completed for the MSmith Windows account as part of the examples in that chapter. Both Apress_Product_Controllers and MSmith were added as database users to the ApressFinancial database, although MSmith was added as a database owner. So at the moment, MSmith as a database owner has full access to the ApressFinancial database. At present, only your account and MSmith have any access to the data, stored procedures, views, and so on, as no further access has been granted at any point throughout the book.

To prove MSmith has full access, log on to Windows as MSmith, then start SQL Server Management Studio, open a Query Editor window, and, within a transaction, update a table. The example code I used to prove MSmith’s access was as follows:

You can see the Windows groups and MSmith in Figure 12-14 in the Permissions page of the Database Properties screen.

Figure 12-14. ApressFinancial database users and effective permissions

So now you know how the security is currently defined within the database. You can now correct any errors and implement the correct security model. MSmith should not be a database owner as it is not a developer- or database administrator–based account.

The first security consideration is to define who can create, modify, or drop objects in your database. Objects are tables, views, stored procedures, and so on as well as database users, roles, and so on. This is the most dangerous area of a database as users can accidently or maliciously complete these actions and potentially destroy your database. Hackers look for ways to access a SQL Server database to complete these actions. This can occur by users in the database owner role giving all database users the database owner role. Under no circumstances should you implement this. Only developers (or in larger corporations, those who release code to production) should be given the database owner role.

The next security consideration is data access and how this should be implemented. There are two schools of thought on how to achieve this. The first school of thought is that all data access, regardless of whether it is to insert, update, delete, or view data, should be done through stored procedures or views. This means that there is no direct table access. A stored procedure should be written for each action on each table or tables. By having all-encompassing stored procedures, some advantages are as follows:

• Data integrity is strictly controlled via the stored procedures. The stored procedures will enforce referential data integrity on top of any relationships you have between tables by ensuring that data in columns fit into the overall database design, all data that are returned either internally in the stored procedure or externally to the executing connection are valid, in a defined format, and contain only the required information.
• User accounts less familiar with your data model can work with your data in a safe manner. If someone new joins a team or if you have a separate team of people who maintain your database data, allowing you and your team to develop further enhancements, the maintenance team doesn’t need to know the underlying data structures or design. Correcting any issues or enhancing the data can all be controlled via stored procedure access.
• It is a lot harder for hackers to see the database design and structure and, therefore, maliciously alter your database.

However, some of the downsides include the following:

• Ad hoc data corrections may not have a stored procedure available at the time the correction is required.
• Changes to the database design, as you enhance your system, can mean changing a large number of related stored procedures.
• You may find that you build a system in which one stored procedure calls another stored procedure, which in turn calls another stored procedure, and so on. This is known as nested procedures, and it could be that within the nesting you find it difficult to follow and perform the right actions in the right order.
• Stored procedures may have a large number of optional parameters with default values to fit every access path.

The second school of thought is that the application knows its data the best and all data access should be via direct T-SQL. The GUI would build up T-SQL statements and send them to SQL Server for action and the results sent back to the GUI to take further. Some advantages of this school of thought include the following:

• The GUI can build the specific code required to perform the necessary action. Optional data requirements can be included or omitted in the T-SQL statement as required.
• The development code, if built well, can implement object-oriented techniques, and this can help build solid T-SQL from the data held.
• Modifications to the data structure in the database could mean one alteration in the development code that would permeate all database changes.
• All data validation and business rules are held outside of the database, and this can aid development time.

And some of the disadvantages include the following:

• Extra code has to be written to stop SQL injection attacks. An SQL injection is where a hacker builds T-SQL statements, which can then be passed to the database and reveal data or schema out to the hacker, who can then hack your database further. This can happen where from a text box a hacker can insert a code terminator that would terminate the code you are building and then execute the hacker’s code.
• A larger amount of code is being passed over the network, and this will have some performance reduction depending on the network.

Changes to the database schema can mean also a deployment of the application to reflect those changes.

The lists are not exhaustive, and different developers will have different views and reasons for each method of proceeding. I just wanted to present to you that there is no right or wrong answer. What you have to decide is what is right for you and what is right for the development you are working on. I tend to adopt a mixed approach strategy in most of my development, where I will have stored procedures and direct T-SQL. I use T-SQL where the code required can alter on each request, such as the columns to return or the tables to work with, and I use stored procedures wherever else I can. If I do have to use T-SQL, I will also try to use views as much as possible before I use direct table access.

However, at this point, non-database owners can do nothing, so it is time to take a look at how you can grant and revoke access to database users.

Granting and Revoking Permissions

It is possible to give or bar a specific principal access rights to specific objects. Within the following section, you will see how you can grant and revoke rights to an object and how these actions can secure access to specific objects.

GRANT

To give a principal a specific permission on an object requires a GRANT statement, and to remove permission you use a REVOKE statement.

The first statement you will use will be the GRANT statement. This gives specifically defined permissions on a specific object to a specific principal. The generic format of the GRANT statement is as follows:

This statement alters depending on the object you are applying the GRANT to. However, let’s take a moment to look at the options:

• PRIVILEGES: This keyword is optional and exists due to ISO compliance and is non-functional on permissions.
• Permission: The permission you will be granting; permissions include EXECUTE, SELECT, INSERT, DELETE, UPDATE.
• Column: The name or list of the column(s) that you will be granting privileges on; the list has to be surrounded by parentheses.
• ON securable: Optional; the securable object you are granting privileges on; this could be the table, stored procedure, or view.
• TO principal: The security principal that is receiving the privilege; this could be a database user or an application role.
• WITH GRANT OPTION: Optional; you can allow the principal defined in the statement to grant this permission to other principals. This is something you need to take care with as you could allow that principal to grant the privilege to another principal that you have had no control over.
• AS principal: Optional; it is possible to grant an object permission to a specific principal, but that principal derives its permissions from a different principal.

The GRANT statement covers a large range of objects from granting a principal the ability to create a database through inserting data into tables, executing stored procedures, and even down to specific columns within a table. One example: You give access to the CustomerDetails.Customers table to several principals but grant to only one principal the ability to see the customer balance amounts within that table. You will see this in action in the example shortly.

REVOKE

Of course, you need the ability to revoke any privileges. Revoking privileges is ideal when you no longer wish a user/principal to have access to a specific securable/object. This can happen when you have given an object too much access. Revoking privileges tends to be used less than granting privileges once a system is live, as part of the go-live would be to have the right privileges granted at that time.

The format for this statement is as follows:

The options are as follows:

• GRANT OPTION FOR: If you have granted permission and included the WITH GRANT option, you can revoke the ability to pass on privileges.
• PRIVILEGES: The keyword is included for ISO compliance and has no effect on permissions.
• Permission: The permission you wish to revoke
• ON securable: The securable object that you are revoking the permission on
• TO | FROM principal: The security principal that you are revoking the privilege from
• CASCADE: If you granted permission on an object to a principal using the WITH GRANT option, and this principal granted permission to another principal and so on, then by using the CASCADE option, the privilege will be revoked for the principal mentioned plus all the principals down the chain.
• AS principal: Optional; it works for REVOKE much as it does for GRANT, but revokes instead of grants.

In the following example, you will allow the Apress_Product_Controllers database user access to execute two procedures and return data from one table, but the user will be unable to see the value from a column.

TRY IT OUT: SECURING YOUR DATABASE

User-Defined Functions

As you have seen, a stored procedure takes a set of data, completes the work as required, and then finishes. It is not possible to take a stored procedure and execute it within, for example, a SELECT statement. This is where user-defined functions (UDFs) come into play. There are two methods of creating UDFs: through T-SQL and through .NET. However you create them, stored functions take a set of inputs and produce output that a query invoking a particular function can further use. UDFs are similar to stored procedures, but it is their ability to be used within a query that provides their power. You have already seen a few system-defined functions within this book, including GETDATE(), which gets today’s date and time and returns it within a query such as SELECT GETDATE().

Functions come in two types, scalar and table-valued, and you will see both of these demonstrated in the final sections of this chapter.

Scalar Functions

The first function that is covered is a scalar function. A scalar function can return only a single value. The GETDATE() function that you are familiar with, shown in Chapter 5 when giving a column a default value, is a scalar function. The following shows the basic syntax to define a scalar function:

Note that zero, one, or more parameters can be passed into the function. Prefix each parameter in the definition with the local variable definition @ sign, and define the data type. Every parameter can be modified within the function as part of the function’s execution, unless you place the keyword READONLY after the data type when defining the function. As with stored procedures, it is possible to call a function and omit specifying one or more of that function’s parameters. Any parameters that you omit must have been defined with default values. In that case, you can call the function with the keyword DEFAULT in the location that such a parameter is expected. The use of default values is demonstrated within the example that follows in the upcoming “Try It Out” section.

The RETURNS clause in a function definition defines the type of data that will be returned. All data types, with the exception of the timestamp data type, can be returned.

The contents of a function are similar to a stored procedure, with the exceptions already discussed. You must place a RETURN statement when you want the function to complete and return control to the calling code.

Table-Valued Functions

A table-valued function lets you return a table of data rather than a single value. You can use a table-valued function to replace the list of tables in the FROM clause of a SELECT statement. The basic syntax for a table-valued function follows. Most of the syntax is similar; however, this time, you’re returning a TABLE data type, and the data to return is defined in terms of a SELECT statement.

A table function is not built in this chapter, but will be completed in Chapter 14, as I want to show more advanced T-SQL with this functionality, and you need to read Chapter 14 before I can do that.

Considerations When Building Functions

Functions must be robust. If an error is generated within a function, whether it is from invalid data being passed in or from errors in the logic, then the function will stop executing at that point, and the T-SQL calling the function will cancel. A function must also not alter any external resource such as a table, and it must not execute a system function that alters resources, such as a function that sends an e-mail. Finally, you need to know whether a function can be used in computed columns.

Once a column is added to a table, it is possible to apply a function to it to get a computed column. For example, you may have a column in a table that is holding customer orders. Within that table, there are two columns called NetValue and LocalTax. You could then have a third column called GrossValue, which is a computation of NetValue + Tax + LocalTax. The value of Tax would come from a user-defined function that computes the gross tax for where the order originated. Either the GrossValue column would have an expression of the gross value of the order using the data from the two columns and the function, or the function would complete all the work. Either way, the value is computed within the column directly and not set by the user.

The computed column would then have a property of IsComputed (set by SQL Server) defined for it. An important point to note is that by default, if a column is a computed column, then it won’t physically exist within the table but is recalculated each time it is used. If you need the value to be defined once and not recalculated when any other column value is altered that is used in the computation, then you need to set the Is Persisted option to Yes within the T-SQL you write. When building your table using the designer, you have to set the Is Persisted property to Yes within the Table Designer and then set the value within your INSERT or UPDATE. There are, of course, valid scenarios for having the column computed each time, but you have to be aware that there will be a small performance overhead with this. By defining the column with the PERSISTED keyword, the value will be stored in the table and will change only when a value in one of the columns used to perform the calculation alters. So, there is a trade-off with space and speed.

However, not all functions can be assigned to a column to make it computed. Once a function has been built, you can check its suitability using the OBJECTPROPERTY function to check whether it is deterministic. If a function is deterministic (defined shortly), then it is a candidate for using to generate a computed column.

The syntax is as follows:

Similar to checking your function properties, properties are assigned to a column when it is created that are useful when the column is a computed column. The values of the properties can be checked by using the COLUMNPROPERTY function once the function has been added to a column, making it a computed column.

If you want to use COLUMNPROPERTY, the function call syntax is as follows:

Here are five properties of interest that you can determine the value of when inspecting a computed column:

• IsDeterministic: If the column will have the same value every time you call the associated function with the same values, it is deterministic.
• IsPrecise: The column can be either precise or imprecise. For example, an exact number is precise, but a floating-point number is imprecise.
• IsSystemVerified: If SQL Server can determine the values of the first two properties, this property will be set to true; otherwise, it will be set to false.
• SystemDataAccess: This is true if any system information is accessed, such as information from system catalogs or views.
• UserDataAccess: This is true if any user data from the local instance of SQL Server are used.

In the following exercise, you will build a scalar function to calculate an amount of interest either gained or lost based on an amount, an interest rate, and two dates. Once the function is built, you will then see a simple usage of the function and check its deterministic value. In Chapter 14, when your T-SQL knowledge is advanced, you will then use this function against the TransactionDetails.Transactions table to calculate interest for every transaction entered.

TRY IT OUT: A SCALAR FUNCTION TO CALCULATE INTEREST

Summary

In this chapter, you have encountered stored procedures and functions, which are collections of T-SQL statements compiled and ready to be executed by SQL Server. You have learned the advantages of a stored procedure over an ad hoc query, encountered the basic CREATE PROCEDURE and CREATE FUNCTION syntaxes, and created some simple stored procedures and functions.

The basics of building a stored procedure are very simple and straightforward. Therefore, building a stored procedure within Query Editor may be as attractive as using a template. As stored procedures are sets of T-SQL statements combined together, you will tend to find that you build up your query, and then at the end surround it with a CREATE PROCEDURE statement.

You have also seen an inline T-SQL-based function. To reiterate, it is possible to also have functions that are written using .NET code, which provides you with more possibilities regarding functionality and other processing abilities.

Probably the largest area of code creation outside of data manipulation and searching will be through control-of-flow statements. You will look at other areas, such as error handling, in Chapter 13, which aims to advance your T-SQL knowledge.