Control-of-Flow Language Usage

Use control-of-flow language with interactive statements, in batches, and in stored procedures.

This table lists the control-of-flow and related keywords and their functions.

• if...else
  The keyword if, with or without its companion else, introduces a condition that determines whether the next statement is executed. The Transact-SQL statement executes if the condition is satisfied, that is, if it returns true.
• case Expression
  case expression simplifies many conditional Transact-SQL constructs. Instead of using a series of if statements, case expression allows you to use a series of conditions that return the appropriate values when the conditions are met. case expression is ANSI-SQL-compliant.
• begin...end
  The begin and end keywords enclose a series of statements so they are treated as a unit by control-of-flow constructs like if...else. A series of statements enclosed by begin and end is called a statement block.
• while and break...continue
  while sets a condition for the repeated execution of a statement or statement block. The statements are executed repeatedly as long as the specified condition is true.
• declare and Local Variables
  Local variables are declared, named, and typed with the declare keyword and are assigned an initial value with a select statement; this must all happen within the same batch or procedure.
• goto
  The goto keyword causes unconditional branching to a user-defined label. goto and labels can be used in stored procedures and batches.
• return
  The return keyword exits from a batch or procedure unconditionally. It can be used at any point in a batch or a procedure. When used in stored procedures, return can accept an optional argument to return a status to the caller. Statements after return are not executed.
• print
  The print keyword, used in the previous example, displays a user-defined message or the contents of a local variable on the user’s screen.
• raiserror
  raiserror displays a user-defined error or local variable message on the user’s screen and sets a system flag to record the fact that an error has occurred.
• Create Messages for print and raiserror
  You can call messages from sysusermessages using either print or raiserror with sp_getmessage. Use sp_addmessage to create a set of messages.
• waitfor
  The waitfor keyword specifies a specific time of day, a time interval, or an event at which the execution of a statement block, stored procedure, or transaction is to occur.
• Comments
  Use the comment notation to attach comments to statements, batches, and stored procedures. Comments are not executed and have no maximum length.