Now that you know how to build and work with SQL Server objects, and insert, update, and delete data as well as retrieve it, you can move on to more of the T-SQL essentials required to complete your programming knowledge.
Potentially, the most important area covered by this chapter is error handling. After all, no matter how good your code is, if it cannot cope when an error occurs, then it will be hard to keep the code stable and reliable. There will always be times that the unexpected happens, from strange input data to something happening on the server. However, this is not the only area of interest. You will be looking at methods to perform aggregations of data and grouping data together. Finally, there will be times that you want to hold data either in a variable or a table and have it exist for only a short period. This is quite a great deal to cover, but this chapter and the next will be the stepping stones that move you from novice to professional developer.
This chapter will therefore look at the following:
Sometimes you’ll need to hold a value or work with a value that does not come directly from a column. Or perhaps you’ll need to retrieve a value from a single row of data and a single column that you want to use in a different part of a query. It is possible to do this via a variable.
You can declare a variable at any time within a set of T-SQL instructions, whether it is ad hoc or a stored procedure or trigger. However, a variable has a finite lifetime.
To inform SQL Server that you want to use a variable, use the following syntax:
DECLARE @variable_name datatype[, @variable_name2 datatype]
All variables have to be preceded with an @
sign, and as you can see from the syntax, more than one variable can be declared, although multiple variables must be separated by a comma or a new line with a new DECLARE
statement. Code can be held on more than one line within the editor, just like all other T-SQL syntax, such as having DECLARE
on line 1 and then the variables on line 2. All variables can hold a NULL
value, and there is not an option to say that the variable cannot hold a NULL
value. By default, then, when a variable is declared, it will have an initial value of NULL
. It is also possible to assign a value at the time of declaration. You’ll see this in the first set of the following code. To assign a value to a variable, you can use a SET
statement or a SELECT
statement. It is standard to use SET
to set a variable value when you are not working with any tables, and it is useful when you want to set the values of multiple variables at the same time. It is also useful when you want to check the system variables @@ERROR
or @@ROWCOUNT
following some other DML statements. You will see these system variables later within this chapter. Let’s take a look at some examples to see more of how to work with variables and their lifetime.
TRY IT OUT: DECLARING AND WORKING WITH VARIABLES
Note Recall from previous chapters that the query as detailed in step 4 will retrieve a value in any order; it will not necessarily be the last row inserted into the table.
There are two types of temporary tables: local and global. These temporary tables are created in tempdb
and not within the database you are connected to. They also have a finite lifetime. Unlike a variable, the time such a table can “survive” is different.
In Chapter 10, when looking at the SELECT
statement, you were introduced to SELECT...INTO
, which allows a permanent table to be built from data from either another table or tables, or from a list of variables. You could make this table more transient by defining the INTO
table to reside within tempdb
. However, it will still exist within tempdb
until either it is dropped or SQL Server is stopped and restarted. This is slightly better, but not perfect for when you just want to build an interim table between two sets of T-SQL statements.
Requiring a temporary table could happen for a number of reasons. Building a single T-SQL statement returning information from a number of tables can get complex, and perhaps could even not be ideally optimized for returning the data quickly. Splitting the query into two may make the code easier to maintain and perform better. To give an example, as your customers “age,” they will have more and more transactions against their account IDs. It may be that when working out any interest to accrue, the query will take a long time to run, as there are more and more transactions. It might be better to create a temporary table of just the transactions you are interested in, and then use this temporary table in code that then calculates the interest, rather than trying to complete all the work in one pass of the data.
When it comes time to work with a temporary table, such a table can be built either by using the CREATE TABLE
statement or by using the SELECT...INTO
command. From a performance perspective, you will find defining a temporary table and then populating it faster than a SELECT…INTO
. If you define a table, SQL Server will know each column’s data type from the definition; however, with a SELECT…INTO
, SQL Server has to calculate what data each column holds from the data and then decide on the data type. Let’s take a look at temporary tables in action.
TRY IT OUT: TEMPORARY TABLES
An aggregation is where SQL Server performs a function on a set of data to return one aggregated value per grouping of data. This will vary from counting the number of rows returned from a SELECT
statement through to figuring out maximum and minimum values. Combining some of these functions with the DISTINCT
function, discussed later in the “Distinct Values” section, can provide some useful functionality. An example might be when you want to show the highest value for each distinct share to demonstrate when the share was worth the greatest amount.
Let’s dive straight in by looking at different aggregation types and working through examples of each.
COUNT/COUNT_BIG
is probably the most commonly used aggregation, and it finds out the number of rows returned from a query. You use this for checking the total number of rows in a table, or more likely the number of rows returned from a particular set of filtering criteria. Quite often this is used to cross-check the number of rows from a query in SQL Server with the number of rows an application is showing to a user.
The syntax is COUNT(*)
or COUNT_BIG(*)
. There are no columns defined, as it is rows that are being counted.
Note The difference in these two functions is that COUNT
returns an integer
data type, and COUNT_BIG
returns a bigint
data type.
TRY IT OUT: COUNTING ROWS
If you have numeric values in a column, it is possible to aggregate them into a sum. One scenario for this is to add the transaction amounts in a bank account to see how much the balance has changed by. This could be daily, weekly, monthly, or over any time period required. For example, a negative amount would show that more has been taken out of the account than put in.
The syntax can be shown as SUM(column1|@variable|Mathematical function)
. The summation does not have to be of a column, but could include a math function. One example would be to sum up the cost of purchasing shares, so you would multiply the number of shares bought by the cost paid.
TRY IT OUT: SUMMING VALUES
On a set of data, it is possible to get the minimum and maximum values of a column of data. This is useful if you want to see values such as the smallest share price or the greatest portfolio value, or in other scenarios outside of the example, as the maximum number of sales of each product in a period of time, or the minimum sold, so that you can see if some days are quieter than others.
TRY IT OUT: MAX AND MIN
As you might expect, the AVG
aggregation returns the average value of a column of data for the filtered rows. All of the values are added and then divided by the number of rows that formed the underlying result set.
TRY IT OUT: AVERAGING IT OUT
Now that you have taken a look at aggregations, you can move on to looking at grouping data. Aggregations, as you have seen, are useful but limited. In the next section, I will show you how you can expand these aggregations so that they are used with groups of data.
Using aggregations works well when you want just a single row of results for a specific filtered item. If you want to find the average price of several shares, you may be thinking you need to provide a SELECT AVG()
for each share. This section will demonstrate that this is not the case. By using GROUP BY
, you instruct SQL Server to group the data to return and provide a summary value for each grouping of data. To clarify, as you will see in the upcoming examples, you could remove the WHERE ShareId=1
statement, which would then allow you to group the results by each different ShareId
. In Chapter 14, where you will look at more advanced T-SQL, you will see how it is possible to create more than one grouped set. For the moment, though, let’s keep it straightforward.
The basic syntax for grouping follows. (It is possible to expand GROUP BY
further to include rolling up or providing cubes of information, which, as part of grouping sets, you will see in Chapter 14.)
GROUP BY [ALL] (column1[,column2,...])
The option ALL
is a bit like an OUTER JOIN
. If you have a WHERE
statement as part of your SELECT
statement, any grouping filtered out will still return a row in the results, but instead of aggregating the column, a value of NULL
will be returned. I tend to use this as a checking mechanism. I can see the rows with values and the rows without values, and visually this will tell me that my filtering is correct.
When working with GROUP BY
, the main point that you have to be aware of is that any column defined in the SELECT
statement that does not form part of the aggregation must be contained within the GROUP BY
clause and be in the same order as the SELECT
statement. Failure to do this will mean that the query will give erroneous results, and in many cases, will use a lot of resources in giving these results.
TRY IT OUT: GROUP BY
When using the GROUP BY
clause, it is possible to supplement your query with a HAVING
clause. The HAVING
clause is like a filter, but it works on aggregations of the data rather than the rows of data prior to the aggregation. Hence, it has to be included with a GROUP BY
clause. It will also include the aggregation you want to check. The code would therefore look as follows:
GROUP BY column1[,column2...]
HAVING [aggregation_condition]
The aggregation_condition
would be where you place the aggregation and the test you want to perform. For example, my bank charges me if I have more than 20 nonregular items pass through my account in a month. In this case, the query would group by customer ID, counting the number of nonregular transactions for each calendar month. If the count were less than or equal to 20 items, then you would like this list to not include the customer in question. To clarify this, the query code would look something like the following if you were running this in August 2011:
SELECT CustomerId,COUNT(*)
FROM CustomerBankTransactions
WHERE TransactionDate BETWEEN '1 Aug 2011' AND '31 Aug 2011 '
GROUP BY CustomerId
HAVING COUNT(*) > 20
TRY IT OUT: HAVING
With some of the tables in the examples, multiple entries will exist for the same value. To clarify, in the ShareDetails.SharePrices
table, there can be multiple entries for each share as each new price is stored. There may be some shares with no price, of course. But what if you want to see a listing of shares that do have prices, but you want to see each share listed only once? This is a simple example, and you will see more complex examples later on when I cover how to use aggregations within SQL Server. That aside, the example that follows serves its purpose well.
The syntax is to place the keyword DISTINCT
after the SELECT
statement and before the list of columns. The following list of columns is then tested for all the rows returned, and for each set of unique distinct values, one row will be listed.
TRY IT OUT: DISTINCT VALUES
To bring more flexibility to your T-SQL code, you can use a number of functions with the data from variables and columns. This section does not include a comprehensive list, but it does contain the most commonly used functions and the functions you will come across early in your development career. They have been split into three categories: date and time, string, and system functions. There is a short explanation for each, with some code demonstrating each function in an example with results.
The functions in the first set involve either working with a variable that holds a date and time value or using a system function to retrieve the current date and time.
If you want to add or subtract an amount of time to a column or a variable, then display a new value in a rowset or set a variable with that new value; DATEADD()
will do this. The syntax for DATEADD()
is as follows:
DATEADD(datepart, number, date)
The datepart
option applies to all of the date functions and details what you want to add from milliseconds to years. These are defined as reserved words and therefore are not surrounded by quotation marks. There are a number of possible values, as listed in Table 13-1.
For the second parameter of the datepart
function, to add, use a positive value, and to subtract, use a negative value. The final parameter can be a value, a variable, or a date-type column holding the date and time you want to change.
TRY IT OUT: DATEADD()
To find the difference between two dates, use the function DATEDIFF()
. The syntax for this function is as follows:
DATEDIFF(datepart, startdate, enddate)
The first option contains the same options as for DATEADD()
, and startdate
and enddate
are the two days you want to compare. A negative number shows that the enddate
is before the startdate
.
TRY IT OUT: DATEDIFF()
Returning the name of the part of the date is great for using with things such as customer statements. Changing the number 6 to the word June makes for more pleasant reading.
The syntax is as follows:
DATENAME(datepart, datetoinspect)
You will also see this in action in DATEPART()
.
TRY IT OUT: DATENAME()
If you want to achieve returning part of a date from a date variable, column, or value, you can use DATEPART()
within a SELECT
statement.
As you may be expecting by now, the syntax has datepart
as the first option, and then the datetoinspect
as the second option, which returns the numerical day of the week from the date inspected.
DATEPART(datepart, datetoinspect)
TRY IT OUT: DATEPART()
The next example in this section on dates demonstrates a function that allows you to format locale-aware dates and numbers. Be aware that this function will not be available on SQL Server installations that do not have the .NET CLR installed. It does not have to be enabled. The FORMAT()
function in SQL Server is similar to the function found in Excel and .NET.
TRY IT OUT: FORMAT()
There may be times that you have either a date or part of a date in three variables or columns holding the year, month, and day or a combination in which one or more of these are derived and the others are supplied. It is possible to convert these three values into a valid date using DATEFROMPARTS()
. You can then expand this with other functions if required.
If you want to build a time as well as a date, then you can use the SMALLDATETIMEFROMPARTS()
function to achieve this.
TRY IT OUT: DATEFROMPARTS() AND SMALLDATETIMEFROMPARTS()
Similar to DATEFROMPARTS()
, there is a TIMEFROMPARTS()
function that will return a time up to fractions of a second from separate variables or values. The function takes five parameters, with the first four being hour, minute, seconds, and fractions of a second. The fifth parameter determines the precision of the fraction portion of the time. If you had a precision of 2, then the fraction number would be defined in 1/100ths.
TRY IT OUT: TIMEFROMPARTS()
It is possible to combine the previous two functions to take a date and time set of values and return a datetime2
data type.
TRY IT OUT: DATETIME2FROMPARTS()
There will be times when you have a date and a time and you have to apply an offset to it. One example would be when you are working with different time zones or you want to add to a date and time how long a response queue estimate is before a response to a query. Adding an hour and minute offset to a date and time will provide you with this functionality. The DATETIMEOFFSETFROMPARTS()
function still has the ability to work in fractions and precision, keeping it in alignment with the other “from parts” functions.
TRY IT OUT: DATETIMEOFFSETFROMPARTS()
Knowing the last day of the month can be more important than knowing the first day of the month, as months vary in the number of days they have. By knowing the last day of the month, you can work backward or even add one day to get the first day of the next month. This is achieved in SQL Server using EOMONTH()
, which can also have a date offset option with it.
TRY IT OUT: EOMONTH()
GETDATE()
is a great function for returning the exact date and time from the system. You have seen this in action when setting up a table with a default value, as well as at a couple of other points in the book. There are no parameters. If you need accuracy to nanoseconds and further, then use SYSDATETIME()
.
This next section will look at some functions that can act on character-based types, such as varchar
and char
.
ASCII()
converts a single character to the equivalent ASCII code.
TRY IT OUT: ASCII()
The reverse of ASCII()
is the CHAR()
function, which takes a numeric value within the ASCII character range and turns it into an alphanumeric character.
TRY IT OUT: CHAR()
When it is necessary to return the first n left characters from a string-based variable, you can achieve this through the use of LEFT()
, replacing n with the number of characters you want to return.
TRY IT OUT: LEFT()
To change alphabetic characters within a string, ensuring that all characters are in lowercase, you can use the LOWER()
function.
TRY IT OUT: LOWER()
There will be times that leading spaces will occur in a string that you’ll want to remove. LTRIM()
will trim these spaces on the left.
TRY IT OUT: LTRIM()
The opposite of LEFT()
is, of course, RIGHT()
, and this function returns the characters from the right-hand side.
TRY IT OUT: RIGHT()
When you have a CHAR()
data type, no matter how many characters you enter, the variable will be filled on the right, known as right-padded, with spaces. To remove these, use RTRIM
. This changes the data from a fixed-length CHAR()
to a variable-length value.
TRY IT OUT: RTRIM()
Some data types have implicit conversions. You will see later how to do explicit conversions, but a simple conversion that will take any numeric value and convert it to a variable-length string is STR()
, which you will look at next. However, SQL Server sometimes has to decide which way the conversion has to go. You will see this problem in the first example in the “Try It Out” exercise. If you want to concatenate two values into a string, then, as you will see in the second example, you will invoke the STR()
function. However, you will see that this also causes a little bit more work to be done within the code to achieve the desired results. To concatenate two values, you can use the CONCAT()
function, similar to the function found in Excel.
TRY IT OUT: STR()
As you have seen, you can take a number of characters from the left and from the right of a string. To retrieve a number of characters that do not start with the first or last character, you need to use the function SUBSTRING()
. This has three parameters: the variable or column, which character to start the retrieval from, and the number of characters to return.
TRY IT OUT: SUBSTRING()
The final string example is the reverse of the LOWER()
function and changes all characters to uppercase.
TRY IT OUT: UPPER()
System functions are functions that provide extra functionality outside of the boundaries that can be defined as string-, numeric-, or date-related. Three of these functions will be used extensively throughout your code, and therefore you should pay special attention to CASE
, CAST
, and ISNULL
.
The first function is for when you want to test a condition. WHEN
that condition is true, THEN
you can do further processing; ELSE
if it is false, then you can do something else. What happens in the WHEN
section and the THEN
section can range from another CASE
statement to providing a value that sets a column or a variable.
The CASE WHEN
statement can be used to return a value or, if on the right-hand side of an equality statement, to set a value. Both of these scenarios are covered in the following examples.
TRY IT OUT: CASE
If you have used Excel or Access among some other tools, you have quite possibly met IIF()
when coding. IIF()
, also known as an inline IF
, is very similar to the CASE
statement demonstrated in the previous example. Where CASE
can have many choices, IIF()
has only two choices. The syntax is as follows:
IIF(boolean_test_condition, true_value, false_value)
TRY IT OUT: IIF()
The ability to return a value based on a separate value can be useful. A good explanation example would be a number that has a range of one through seven could be used as the basis of determining the day name of the week. There is a function that already does this, as demonstrated earlier in the chapter with DATENAME()
. In the ApressFinancial
database, it might have been possible to use CHOOSE()
for the transaction types to denote a debit or a credit type. However, for only two choices, IIF()
is a better option. CHOOSE()
is good for where there are three or more possible options for the testing value and translating it to a new value.
There is a large caveat, and that is that you should not necessarily replace tables that have a few rows with a CHOOSE()
statement. Having the data in a table gives a number of advantages, including a single point for the data and the ability to join the data; if you have to add any new data, you can simply add it to the table rather than having to alter one or more stored procedures and functions.
I am yet to be convinced of using CHOOSE()
instead of a cross-reference table unless you have the CHOOSE()
choices within a user-defined function. So, for example, you would place the following example as a user-defined function to return the choice value rather than the TransactionDetails.TransactionTypes
table.
TRY IT OUT: CHOOSE()
These are two functions used to convert from one data type to another. The main difference between them is that CAST()
is ANSI SQL–92 compliant, but CONVERT()
has more functionality.
The syntax for CAST()
is as follows:
CAST(variable_or_column AS datatype)
This is opposed to the syntax for CONVERT()
, which is as follows:
CONVERT(datatype,variable_or_column)
Not all data types can be converted between each other, such as converting a datetime
to a text
data type, and some conversions need neither a CAST()
nor a CONVERT()
. There is a grid in “Books Online” that provides the necessary information.
If you want to CAST()
from numeric
to decimal
or vice versa, you need to use CAST()
; otherwise, you will lose precision.
TRY IT OUT: CAST()/CONVERT()
Although ISDATE()
is a function that works with dates and times, this system function takes a value in a column or a variable and confirms whether it contains a valid date or time. The value returned is 0, or false, for an invalid date, or 1 for true if the date is okay. The formatting of the date for testing within the ISDATE()
function has to be in the same regional format as you have set with SET DATEFORMAT
or SET LANGUAGE
. If you are testing in a European format but have your database set to US format, then you will get a false value returned.
TRY IT OUT: ISDATE()
Many times so far, you have seen NULL
values within a column of returned data. As a value, NULL
is very useful, as you have seen. However, you may want to test whether a column contains a NULL
. If there were a value, you would retain it, but if there were a NULL
, you would convert it to a value. This function could be used to cover a NULL
value in an aggregation, for example. The syntax is as follows, where the first parameter is the column or variable to test if there is a NULL
value, and the second option defines what to change the value to if there is a NULL
value:
ISNULL(value_to_test,new_value)
This change occurs only in the results and doesn’t change the underlying data that the value came from.
TRY IT OUT: ISNULL()
This system function tests the value within a column or variable and ascertains whether it is numeric. The value returned is 0, or false, for an invalid number, or 1
for true if the value can be converted to a numeric.
Note Currency symbols such as £ and $ will also return 1
for a valid numeric value.
TRY IT OUT: ISNUMERIC()
As you have seen in the last few examples, you can test if data fit certain data types and that you can cast from one data type to another. However, it is possible to be in a situation where you cannot cast the data to the relevant data type. In the previous example, an error would be received if you tried to convert to a data type where the data were not valid. Using TRY…CONVERT()
, you can attempt to cast the data, and if the conversion is not possible, a NULL
value is returned.
One example of where TRY…CONVERT()
would be better than a straight conversion is where data that are valid in one system are transferred and not valid in another. An example of this is where one system for a date can hold a specific date, a date range, or a date length such as three months. Another example is where mnemonics are stored for a number, like M for million. The receiving system could then use the TRY_CONVERT()
to place the data in one of three columns depending on the scenario. By using TRY…CONVERT()
and the CASE
statement, you could fulfill the three tests on the date and populate the relevant correct column in your system. The example you will see demonstrated will show this.
TRY IT OUT: TRY_CONVERT()
CAST
and CONVERT
are ideal for general conversion, but for specific conversions from string to a number or a datetime
, then PARSE
is the preferred option. From other data types, you should use CONVERT/TRY_CONVERT
. PARSE
is similar to CONVERT
, and, as you might expect, TRY_PARSE
is similar to TRY_CONVERT
, in that you can test the variable, and if the cast fails, then a NULL
value is returned. It is possible to include culture information as part of the parsing so that the conversion takes the defined locale into consideration.
TRY IT OUT: PARSE() AND TRY_PARSE()
Even though the many system functions, several of which allow you to test data to try to avoid an unexpected error occurring, have been demonstrated, you can never plan for every eventuality. In the following section, you will see how to work with errors when they do occur.
Before you learn about handling errors, you need to be aware of what an error is, how it is generated, the information it generates, and how to generate your own errors when something is wrong. The T-SQL command RAISERROR
allows you as a developer to have the ability to produce your own SQL Server error messages when running queries or stored procedures. You are not tied to using just error messages that come with SQL Server; you can set up your own messages and your own level of severity for those messages. It is also possible to determine whether the message is recorded in the Windows error log.
However, whether you want to use your own error message or a system error message, you can still generate an error message from SQL Server as if SQL Server itself raised it. Enterprise environments typically experience the same errors on repeated occasions, since they employ SQL Server in very specific ways depending on their business model. With this in mind, attention to employing RAISERROR
can have big benefits by providing more meaningful feedback as well as suggested solutions for users. You will see later in the chapter a second method for generating an error.
By using RAISERROR
, SQL Server acts as if SQL Server raised the error, as you have seen within this book.
RAISERROR
can be used in one of two ways; looking at the syntax will make this clear.
You can either use a specific msg_id
or provide an actual output string, msg_str
, either as a literal or a local variable defined as string-based, containing the error message that will be recorded. The msg_id
references system and user-defined messages that already exist within the SQL Server error messages table.
When specifying a text message in the first parameter of the RAISERROR
function instead of a message ID, you may find that this is easier to write than creating a new message:
RAISERROR('You made an error', 10, 1)
The next two parameters in the RAISERROR
syntax are numerical and relate to how severe the error is and information about how the error was invoked. Severity levels range from 1 at the innocuous end to 25 at the fatal end. Severity levels of 2 to 14 are generally informational. Severity level 15 is for warnings, and levels 16 or higher represent errors. Severity levels from 20 to 25 are considered fatal, and require the WITH LOG
option, which means that the error is logged in the Windows Application Event Log and the SQL error log and the connection terminated; quite simply, the stored procedure stops executing. The connection referred to here is the connection within Query Editor, or the connection made by an application using a data access method like ADO.NET. Only for a most extreme error would you set the severity to this level; in most cases, you would use a number between 1 and 19.
The last parameter of the function specifies state. Use a 1 here for most implementations, although the legitimate range is from 1 to 127. You may use this to indicate which error was thrown by providing a different state for each RAISERROR
invocation in your stored procedure. SQL Server does not act on any legitimate state value, but the parameter is required.
A msg_str
can define parameters within the text. By placing the value, either statically or via a variable, after the last parameter that you define, msg_str
replaces the message parameter with that value. This is demonstrated in an upcoming example. If you do want to add a parameter to a message string, you have to define a conversion specification. The format is as follows:
% [[flag] [width] [. precision] [{h | l}]] type
The options are as follows:
flag
: A code that determines justification and spacing of the value entered:
-
(minus): Left-justify the value+
(plus): The value shows a + or a –
sign.
0
: Prefix the output with zeros#
: Preface any nonzero with a 0, 0x, or 0X
, depending on the formatting
width
: The minimum width of the outputprecision
: The maximum number of characters used from the argumenth
: Character types:
%
sign followed by one of the following options: d
or i
for a signed integer, p
for a pointer, s
for a string, u
for an unsigned integer, x
or X
for an unsigned hexadecimal, and o
for an unsigned octal. Note that float, double, and single are not supported as parameter types for messages. You will see this in the upcoming examples.Finally, there are three options that could be placed at the end of the RAISERROR
message. These are the WITH
options:
LOG
places the error message within the Windows error log.NOWAIT
sends the error directly to the client.SETERROR
resets the error number to 50000 within the message string only.When using any of these last WITH
options, do take the greatest of care, as their misuse can create more problems than they solve. For example, you may unnecessarily use LOG
a great deal, filling up the Windows Application Event Log and the SQL error log, which leads to further problems.
There is a system stored procedure, sp_addmessage
, that can create a new global error message that can be used by RAISERROR
by defining the @msgnum
. The syntax for adding a message is as follows:
sp_addmessage [@msgnum =]msg_id,
[@severity = ] severity , [ @msgtext = ] 'msg'
[ , [ @lang = ] 'language' ]
[ , [ @with_log = ] 'with_log' ]
[ , [ @replace = ] 'replace' ]
The parameters into this system stored procedure are as follows:
@msgnum
: The number of the message is typically greater than 50000.@severity
: A number in the range of 1 to 25@lang
: Use this if you need to define the language of the error message. Normally, this is left empty.@with_log
: Set to 'TRUE'
if you want to write a message to the Windows error log.@replace
: Set to 'replace'
if you are replacing an existing message and updating any of the preceding values with new settings.Note Any message added will be specific for that database rather than the server.
It is time to move to an example that will set up an error message that will be used to say a customer is overdrawn.
TRY IT OUT: RAISERROR
When working with T-SQL, it is important to have some sort of error handling to cater to those times when something goes wrong. Errors can be of different varieties; for example, you might expect at least one row of data to be returned from a query, and then you receive no rows. However, what I am discussing here is when SQL Server informs you there is something more drastically wrong. You have seen some errors throughout the book, and even in this chapter. There are two methods of error catching you can employ in such cases. The first uses a system variable, @@ERROR
.
This is the most basic of error handling. It has served SQL Server developers well over the years, but it can be cumbersome. When an error occurs, such as you have seen as you have gone through the book creating and manipulating objects, a global variable, @@ERROR
, is set with the SQL Server error message number. Similarly, if you try to do something with a set of data that is invalid, such as dividing a number by zero or exceeding the number of digits allowed in a numeric
data type, then SQL Server will set this variable for you to inspect.
The downside is that the @@ERROR
variable setting lasts only for the next statement following the line of code that has been executed; therefore, when you think there might be problems, you need to either pass the data to a local variable or inspect it straightaway. The first example demonstrates this.
TRY IT OUT: USING @@ERROR
Although a @@ERROR
is a useful tool, it would be better to use the next error-handling routine to be demonstrated, TRY...CATCH
with THROW
.
It can be said that no matter what, any piece of code has the ability to fail and generate some sort of error. For the vast majority of this code, you will want to trap any error that occurs, check what the error is, and deal with it as best you can. As you saw previously, this could be done one statement at a time using @@ERROR
to test for any error code. Improved functionality exists whereby a set of statements can try to execute, and if any statement has a runtime error, it will be caught. This is known as a TRY...CATCH
block. If you find an error within your code where you were expecting something such as a specific value or a range of values, for example, then it is possible to generate an errorwith a THROW
statement. In essence, TRY…CATCH
and THROW
are greatly improved and more flexible versions of RAISERROR
and @@ERROR
. There is one slight catch, and that is a THROW
statement must be preceded with a line of code that is terminated with a semicolon.
Surrounding a single batch of code with the ability to execute that code and to then catch any errors and try to deal with them has been around for quite a number of years in languages such as C++. Gladly, you see this within SQL Server. You can use TRY…CATCH
with a transaction; however, you have to take care where to place the BEGIN TRANSACTION
along with the ROLLBACK
and COMMIT TRANSACTION
statements. You will see an example of this within the examples.
The syntax for TRY...CATCH
is pretty straightforward. There are two “blocks” of code. The first block, BEGIN TRY
, is where there is one or more T-SQL statements that you want to try to run. If any of statements has an error, then no further processing within that block will be done, and processing will switch to the second block, BEGIN CATCH
.
BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
{ sql_statement | statement_block }
END CATCH
It is possible to generate your own error via a RAISERROR
, as you saw earlier, or you can execute a THROW
statement either within or outside a TRY
or CATCH
block. A RAISERROR
within a TRY…CATCH
will work as it would outside a TRY...CATCH
, and that is that it will set the error details, and then if the severity is 10
or lower, processing will continue. If the severity is in the range of 11
to 19
, then the RAISERROR
would pass execution to the CATCH
block. Any severity of 20
or above would stop execution immediately and close the connection, and processing would not pass to the CATCH
block. Rather than using RAISERROR
, you can THROW
an error that will always move the execution of the code to the CATCH
block of code. This allows you as a developer to do cleanup actions when an error has occurred, such as to roll back a transaction or do any other actions that you want to perform that will help you with any investigation.
TRY…CATCH
blocks can be nested. This gives you several advantages. The first is that you can pass any error up through the nested levels with a rethrow. This is achieved by issuing the THROW
statement with no parameters in each CATCH
block in the nesting. You will sometimes hear this called “bubbling up the error.” There are advantages to bubbling as it allows a controlled way of getting out of a difficult situation; however, it can also lead to confusion if you rethrow every exception. So when you rethrow an error, ensure that this is the action you want to do.
Nesting allows you to surround a stored procedure call with a TRY…CATCH
. This will then allow for any error in the called stored procedure that is thrown to be caught in the calling stored procedure.
Within the TRY…CATCH
block, it is possible to retrieve error information. The system functions that can be used to find useful debugging information are detailed here:
ERROR_LINE()
: The line number that caused the error or performed the RAISERROR
or the THROW
statement; this is physical rather than relative (i.e., you don’t have to remove blank lines within the T-SQL to get the correct line number, unlike some software that does require this).ERROR_MESSAGE()
: The text messageERROR_NUMBER()
: The number associated with the messageERROR_PROCEDURE()
: If you are retrieving this within a stored procedure or trigger, the name of it will be contained here. If you are running ad hoc T-SQL code, then the value will be NULL
.ERROR_SEVERITY()
: The numeric severity value for the errorERROR_STATE()
: The numeric state value for the errorNot all errors are caught within a TRY...CATCH
block, unfortunately. These are compile errors or errors that occur when deferred name resolution takes place and the name created doesn’t exist. To clarify these two points, when T-SQL code is either ad hoc or within a stored procedure, SQL Server compiles the code, looking for syntax errors. However, not all code can be fully compiled and is not compiled until the statement is about to be executed. If there is an error, then this will terminate the batch immediately. The second error that will not be caught is that if you have code that references a temporary table, for example, then the table won’t exist at runtime and column names won’t be able to be checked. This is known as deferred name resolution, and if you try to use a column that doesn’t exist, then this will also generate an error, terminating the batch.
There is a great deal more to TRY...CATCH
blocks in areas that are quite advanced, but now that you know the basics, let’s look at some examples demonstrating what I have just discussed.
TRY IT OUT: TRY...CATCH…THROW
The text for this chapter is not the most fluid, but the information will be very useful as you start using SQL Server. Each section you have read contains a great deal of useful and pertinent information, and rereading the chapter and maybe even trying out different ideas based on the basics demonstrated will give you a stronger understanding of what is happening. The main areas of focus were error handling and joining tables to return results. Also looking at some of the system predefined functions and how useful they can be is important, as you will use some of these functions a great number of times when coding. Take time to fully understand what is happening and how you can use these two features.
18.119.235.79