C H A P T E R 13 Essentials for Effective Coding 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: · · · · · · · Having a method of storing information temporarily using variables Storing rows of information in a nonpermanent table Aggregating values Organizing output data into groups of relevant information Returning unique and distinct values Looking at and using system functions Handling errors, e.g., creating your own errors, trapping errors, and making code secure Variables 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] 493