Conditional Processing, Control-of-Flow, and Cursors

Abstract

In this chapter, I’ll present recipes that demonstrate SQL Server Transact-SQL for

• Conditional processing: You’ll learn how to use the CASE and IF...ELSE statements to evaluate conditions and return values accordingly. I’ll review how to use the CASE function to evaluate a single input expression and return a value, and also how to evaluate one ormore Boolean expressions. Finally, I’ll demonstrate returning a value when the expressions are TRUE.

• Control-of-flow functionality: This recipe demonstrates how to control the execution of Transact-SQL statements or batches based on commands such as RETURN, WHILE, WAITFOR, and GOTO. RETURN is used to exit the current Transact-SQL batch immediately, and doesn’t allow any code in the batch that executes after it. The WHILE command is used to repeat a specific operation or batch of operations while a condition remains TRUE. The WAITFOR command is used to delay the execution of Transact-SQL code for a specified length of time or until a specific time. GOTO is used to jump to a label in your Transact-SQL batch, passing over the code that follows it.

• Creating and using cursors: Here, I’ll demonstrate Transact-SQL cursors, which allow you to work with one row at a time. Based onmy experiences in the field, I’mnot a big fan of cursors. Cursors can cause significant performance problems due to excessive singleton row calls,memory consumption, and code bloat issues when not implemented correctly. However, there stillmay be rare occasions when the use of a cursor is a better choice than a set-based solution.

An understanding of how and when (and when not) to use these techniques will allow you to create flexible and intelligent Transact-SQL code.