SQL Server™ 2005 Bible

Chapter 8. Using Expressions and Scalar Functions

In This Chapter

• Working with expressions and scalar functions

• Using logic within a query

• Working with nulls, strings, and dates

When my son, David, was younger he built incredible monster trucks and gizmos out of K'NEX construction pieces. If you aren't familiar with K'NEX, do a Google image search and see the wild things kids build with them.

What makes K'NEX cool is that nearly any piece can plug into any other piece. This interconnectivity makes K'NEX flexible. In the same way, the interconnectivity of SQL expressions and functions makes SQL so flexible and powerful.

Expressions can retrieve data from a subquery, handle complex logic, convert data types, and manipulate data. If the secret to being a competent SQL database developer is mastering SQL queries, then wielding expressions and scalar functions is definitely in the arsenal.

An expression is any combination of constants, functions, or formulas that returns a single value. Expressions may be as simple as a hard-coded number, or as complex as a case expression that includes several formulas and functions.

Expressions may be employed in several places within the SQL syntax. Nearly anywhere a value may be used, an expression may be used instead. This includes column values, join on clauses, where and having clauses, and order by columns. Expressions can't be substituted for object names such as table names or column names.

Note

SQL statements are not case-sensitive. The statement will ...