Completing the Introduction to Transact-SQL

Now that you're revisited the previous article and your memory is refreshed, let me tell you what we're going to learn in this final part of the series. First, the TRY...CATCH construct will be presented. We covered the RETURN in the first segment. In this segment, you will see that RAISERROR is quite similar since it is a crucial part of error handling, just like RETURN. You will find out when to use which one.

Furthermore, as I promised, we are going to cover particular techniques for working with the cursors, and ultimately we will play around with functions. T-SQL sports a vast number of built-in functions, so that's all fine and dandy, but we also have the ability to create user-defined functions (UDFs). Once we get there, we will point out why user-defined functions shouldn't be confused with stored procedures.

Moreover, all three types of UDFs that are possible with MS SQL Server are going to be explained and exemplified. These are scalar functions, inline table-valued functions, and multistatement table-valued functions.

In short, you can anticipate the aforementioned throughout this part. That means we are prepared and ready to go. Enjoy the ride!

Error Handling

Error Handling

Let's revisit the RETURN instruction that was presented in the previous article. Simply put, it exits from the procedure or query without relying on conditions. This means that the statements that are next to the RETURN are ignored; the execution ends right as the RETURN instruction is met. Stored procedures can return integers.

When you want to use RETURN within a stored procedure, pay attention, because a NULL value cannot be returned. If you attempt to do this, a warning message as well as a value from the 0 and -14 interval is generated and returned. It is also worth pointing out that with stored procedures, usually a nonzero is akin to a failure while a returned 0 equals success.

Moreover, we have a global variable called @@error that basically always returns the error number for the last T-SQL statement executed. Unless documented otherwise, as I mentioned, a nonzero value means that the execution encountered an error. It is really important to immediately check or backup the value of @@error because it is cleared after each statement execution (to return the latest state).

You see, that's why experienced programmers often say that you need to make compromises and assume that nothing will go wrong. Verifying whether everything is all right after each execution is very boring and monotonous. However, you get my drift.

Moving on, we have the TRY...CATCH construct, which is similar to the exception handling that is possible with other programming languages such as C++, Java, and C#. Thanks to the TRY...CATCH blocks we can implement error handling a bit easier. Things can go very deep (yet be very simple) when you start to nest these blocks. Have no fear!

First of all, we need to understand its methodology. All of the statements that are enclosed within the TRY block are executed. Then the program proceeds to move on if and only if there weren't any errors encountered; then it skips the entire CATCH block. However, if one of the statements caused an error then the control immediately jumps to the beginning of the CATCH block and heads on to execute the statements within.

BEGIN TRY

lots of statements

dozens of statements

END TRY

BEGIN CATCH

catch statements

END CATCH

The above example really points out that by using TRY...CATCH blocks we can write code that is much easier and readable. There are six kinds of "catch functions" which can be used to obtain error information: 

• ERROR_LINE()
  

• ERROR_MESSAGE()
  

• ERROR_NUMBER()
  

• ERROR_PROCEDURE()
  

• ERROR_SEVERITY()
  

• ERROR_STATE().

The name of these functions are very intuitive and self-explanatory so we won't get into them. Should you call one of the aforementioned functions outside of a CATCH then don't be surprised because they always return NULL.

Warning: Errors that have a lower severity than 10 are considered warnings, and thus aren't considered errors, therefore they do not alter the flow of control. Additionally, neither of the errors that disconnect the server will cause branching to the CATCH block. Processes that are terminated with a KILL command aren't trapped either. Always consider these when implementing error handling.

The last instruction that we'll describe here is the RAISERROR. You as a programmer can opt for either obtaining one of the error messages from the sysmessages table or dynamically creating an error message of your own accompanied by user-specified severity and state information. We usually use these in the case of stored procedures.

RAISERROR ({msg_id | msg_str}{, severity, state})

Notes: msg_id is the user-defined error message number and should be larger than 50,000 if you are creating your own error message. The maximum value is 2^31-1; msg_str stands for the error message per se and its maximum length is 8,000 chars; severity is the user-defined value and it can go from 0 to 18 (any user) and 19 to 25 (sysadmins only); state can be 1-127 and it stands for the invocation state of an error.

Warning: Severity levels 20-25 are considered FATAL. If this occurs then error message gets returned and logged, then the client connection is interrupted.

Playing with Cursors

Playing with Cursors

You may ask what we can do with cursors. Cursors are really useful because we can access data on a row-by-row basis. Each time we use the SELECT statement, the table is returned as a whole, including all of the rows. However, from time to time a situation may arise where we need to work with rows independently.

What's more, with the help of cursors we can move throughout the table on a row-by-row basis using the return values of the SELECT statement. That's how we can work on the rows- modify, delete, and so forth. There are specific steps which must be followed when working with cursors:

1. Declaration of variables to store the return row-values of the SELECT statement.
   

2. Declaration of the cursor (by pointing out the appropriate SELECT statement).
   

3. Opening the cursor.
   

4. Accessing the rows (data) using the cursors.
   

5. Closing the cursor (and also de-allocate it if you won't need it anymore).

Keep in mind that the cursors allow us sequential forward movements only. There's no way you could step backwards. So be aware of how many rows you fetch forward.

Check out the following real-world example where we implement cursors to print the ProdId, ProdName, and UnitPrice columns of the table called Products.

Use NameOfDatabase

-- 1. Declaration of variables.

DECLARE @MyProdID smallint

DECLARE @MyProdName nvarchar(20)

DECLARE @MyUnitPrice smallmoney

-- 2. Declaration of the cursor.

DECLARE ProdCursor CURSOR FOR

SELECT ProdID, ProdName, UnitPrice

FROM Products

WHERE ProdID <= 10

-- 3. Opening the cursor.

OPEN ProdCursor

-- 4. Accessing the rows from the cursor.

FETCH NEXT FROM ProdCursor

INTO @MyProdID, @MyProdName, @MyUnitPrice

PRINT '@MyProdID = ' + CONVERT(nvarchar, @MyProdID)

PRINT '@MyProdName = ' + CONVERT(nvarchar, @MyProdName)

PRINT '@MyUnitPrice = ' + CONVERT(nvarchar, @MyUnitPrice)

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM ProdCursor

INTO @MyProdID, @MyProdName, @MyUnitPrice

PRINT '@MyProdID = ' + CONVERT(nvarchar, @MyProdID)

PRINT '@MyProdName = ' + CONVERT(nvarchar, @MyProdName)

PRINT '@MyUnitPrice = ' + CONVERT(nvarchar, @MyUnitPrice)

END

-- 5. Closing and de-allocating the cursor.

CLOSE ProdCursor

DEALLOCATE ProdCursor

As you can see from the above example, the variables that are declared must be of the same data type as the columns from the database. We declare the cursor using the CURSOR FOR... FROM... WHERE construct of code. As soon as the cursor is opened using the OPEN instruction, the SELECT instruction is executed right away.

Thereafter, as I mentioned at the beginning of this chapter, we use the FETCH instruction to move on a row-by-row basis throughout the table. There's the variable @@FETCH_STATUS which is used in that WHILE loop. It can have one of the following return values: 0 when the FETCH statement was successfully executed; -1 when an error occurred during the FETCH execution; -2 when the required row does not exist.

The CLOSE statement closes the cursor, freeing up if there are any cursor locks, but leaves it available for reopening. DEALLOCATE removes the cursor from the reference. After de-allocation the cursor can be reopened only if you re-declare it again.

Oh, and if you really paid attention, then you may have noticed from the example above that I used the two hyphens (--) to introduce comments. They can be used for single-line comments; you just write them at the beginning of the line that's going to be commented. But we can also use multi-line comments with the /* ... */ block. Comments improve the readability of the code; they are not evaluated nor executed.

The World of Functions

The World of Functions

The Transact-SQL programming language has three types of built-in functions: aggregate functions, rowset functions, and scalar functions. The aggregate functions work on the summarizing principle; they work on a group of values but return one single value. Rowset functions are in general used as table references. Scalar functions, on the other hand, work on just one value and likewise return one single value.

Check out the following table of aggregate functions:

As I mentioned, rowset functions return an object that can be used as a reference in a table. We have the following rowset functions: CONTAINSTABLE, FREETEXTTABLE, OPENQUERY, and OPENROWSET. But explaining these is beyond the scope of this article.

As for the scalar functions, we have dozens of them, but we categorize them in the following distinct groups:

• Mathematical Functions: ABS, COS, EXP, LOG, PI, SIN, SQRT, and more.
  

• String Functions: LEFT, LOWER, LTRIM, NCHAR, REPLACE, REVERSE, etc.
  

• Date and Time Functions: GETDATE, DAY, MONTH, YEAR, and so forth.
  

• System Functions: CURRENT_USER, NEW_ID, PERMISSIONS, and such.
  

• Configuration Functions: @@LANGUAGE, @@MAX_PRECISION, etc.
  

• Cursor Functions: @@FETCH_STATUS, CURSOR_STATUS, etc.
  

• Metadata Functions: DB_ID, DB_NAME, FILE_ID, FILE_NAME, etc.
  

• Security Functions: IS_MEMBER, SUSER_ID, SUSER_NAME, USER_ID, etc.
  

• System Statistical Functions: @@CPU_BUSY, @@IDLE, @@PACK_SENT, etc.
  

• Text and Image Functions: TEXTPTR, TEXTVALID, PATINDEX.

Now let's talk about user-defined functions (UDFs). At the beginning of this article I pointed out that there are three types of UDFs: scalar functions, inline table-valued functions, and multistatement table-valued functions. Let's see which is which.

Scalar UDFs always return a single data value, which can be of any type except text, ntext, image, cursor, table, timestamp, and of course other user-defined types. Inline table-valued functions return a table type object, which is basically the result set of a single SELECT statement. Multistatement table-valued functions also return a table type object; however, these can contain a larger number of T-SQL statements.

We declare user-defined functions using the CREATE FUNCTION statement. Moreover, we shouldn't forget about the RETURNS part where we explicitly specify the data type (scalar) that's going to be returned later on.

Check out the following prime of a scalar UDF example that returns the new sale price of a product using the next formula: new sale price = price * discount.

-- We declare the function alongside we specify the RETURNS

CREATE FUNCTION DiscPrice (@Price money, @Disc float)

RETURNS money

-- Now we specify what exactly to do in the function

AS

BEGIN

RETURN @Price * @Disc

END

As you can see from the above example, the statements that are to be executed in order to calculate the returnable value are enclosed in the "BEGIN...END" block of code. So that's where we write the function per se. Also, don't forget that we need to call user-defined functions using the owner_name.fuction_name() principle; generally, the owner is akin to the database user that has created the function.

Working with inline table-valued functions is different. The returned object is of the table data type. There's no "BEGIN...END" code block because the whole function is built up by a single SELECT instruction. However, should we need to use multiple statements, we are going to create a multistatement table-valued function. In this case we need to enclose the statements inside the "BEGIN...END" block of code.

Final Thoughts

We've come to the end of this series. I really hope that you found it informative.

Throughout this segment we covered most of the error handling concepts including the statements RETURN, RAISERROR, and the TRY...CATCH block of code. Moving on, we discussed cursors and how we should work with them. Then we drifted into the world of functions. First, we analyzed some of the built-in function categories.

Likewise, today we learned how we can create user-defined functions. UDFs should not be confused with stored procedures. Why? Stored procedures cannot be used like user-defined functions because they are not expressions. Procedures accept parameters and execute T-SQL instructions in relation to those specified parameters.

Microsoft SQL Server allows the user to create user-defined functions in the .NET programming languages, not only in T-SQL. Other DB management systems such as Oracle allow the creation of UDFs in other languages like Java and C, too.

Anyway, the time has come for me to end this article. Thanks for reading! Oh, and by the way, should you need any more help then don't hesitate to join "ASP Free Forums" and ask for help. Our community is knowledgeable, practical, and easy-going.