Tutorial

Flow control statements

Flow-control statements from T-SQL are rather rudimentary compared to similar commands in other modern programming languages such as Visual Basic and C#. Their use requires knowledge and some skill to overcome their lack of user friendliness. However, on a positive note, they allow the creation of very complex procedures.
This section covers the use of the following Transact-SQL statements and programming constructs:

  • Comments
  • Statement block
  • If…Else
  • While…Break
  • Break
  • Continue
  • GoTo
  • WaitFor
  • Begin…End

    Comments
    You can include comments inside the source code of a batch or a stored procedure; these comments are ignored during compilation and execution by SQL Server. It is a common practice to accompany source code with remarks that will help other developers to understand your intentions.
    Comments can also be a piece of Transact-SQL source code that you do not want to execute for a particular reason (usually while developing or debugging). Such a process is usually referred to as commenting out the code.

    Single-line Comments
    There are two methods to indicate a comment. A complete line or part of the line can be marked as a comment if the user places two hyphens

    • Requires Free Membership to View

    • (--) at the beginning. The remainder of the line becomes a comment. The comment ends at the end of the line:

      -- This is a comment. Whole line will be ignored.

      You can place the comment in the middle of a Transact-SQL statement. The following example comments out the last column:

      Select LeaseId, LeaseVendor --, LeaseNumber
      From dbo.Lease
      Where ContractDate > '1/1/1999'
      This type of comment can be nested in another comment defined with the same or a different method:

      -- select * from dbo.Equipment –- Just for debugging This commenting method is compatible with the SQL-92 standard.

      Multiline Comments: /* … */
      The second commenting method is native to SQL Server. It is suitable for commenting out blocks of code that can span multiple lines. Such a comment must be divided from the rest of the code with a pair of delimiters—(/*) and (*/):

      /*
      This is a comment.
      All these lines will be ignored.
      */
      
      /* List all equipment. */
      select * from Equipment
      
      Comments do not have a length limit. It is best to write as much as is necessary to adequately document the code.
      Single-line comments can be nested inside multiline comments:

      /*
      -- List all equipment.
      Select * from Equipment
      */
      
      SQL Server 2005 also supports the nesting of multiline comments. Earlier versions of SQL Server had problems with that. In different versions and in different tools, the following may or may not generate a syntax error:

      /* This is a comment.
      /* Query Analyzer in SQL Server 2000 will understand the following
      delimiter as the end of the first comment. However, it will work fine
      in SQL Server 2005 Management Studio.*/
      This will generate a syntax error in some cases. */
      Select * from dbo.Equipment

      In Chapter 5 where I discuss batches, I will illustrate the restriction that multiline comments cannot span more than one batch.

      Documenting Code
      Again, your comments will be of benefit to other developers who read your code; your comments will be better still if you make their presence in the code as obvious as possible. It is a favorable, although not required, practice to accompany comment delimiters with a full line of stars, or to begin each commented line with two stars:

      /*****************************************************************
      ** File: ap_Equipment_Insert.sql
      ** Name: ap_Equipment_Insert
      ** Desc: Insert equipment and equipment type
      **       (if not present).
      **
      ** Return values: ErrorCode
      **
      ** Called by:   middleware
      **
      ** Parameters:
      ** Input                                  Output
      ** ----------                             -----------
      ** Make                                   EqId
      ** Model
      ** EqType
      **
      ** Auth: Dejan Sunderic
      ** Date: 1/1/2005
      ******************************************************************
      ** Change History
      ******************************************************************
      ** Date:        Author:      Description:
      ** --------     --------     -------------------------------------
      ** 11/1/2005     DS          Fixed:49. Better error handling.
      ** 11/2/2005     DS          Fixed:36. Optimized for performance.
      *****************************************************************/
      
      Inserting two stars at the beginning of each line serves two purposes:

    • They are a visual guide for your eye. If you comment out code this way, you will not be in doubt whether a piece of code is functional or commented out.
    • They force SQL Server to report a syntax error if somebody makes an error (for example, by nesting comments or by spanning comments over multiple batches).

      The preceding example is based on part of a SQL script for creating a stored procedure generated by Visual InterDev. It is very useful to keep track of all these items explicitly, especially Description and Change History. It is a personal choice to be more elaborate in describing stored procedures, but if you are, your comments can be used as instant design documentation.

      Occasionally, developers believe that this type of header is sufficient code documentation, but you should consider commenting your code throughout. It is important to comment not how things are being done, but what is being done. I recommend that you write your comments to describe what a piece of code is attempting to accomplish, and then write the code itself. In this way, you create design documentation that eventually becomes code documentation.


      Basic Transact-SQL Programming Constructs

       Home: Introduction
       Tip 1: T-SQL identifiers
       Tip 2: Database object qualifiers
       Tip 3: Character string data types
       Tip 4: Date, time and number data types
       Tip 5: Special data types-Part 1
       Tip 6: Special data types-Part 2
       Tip 7: Local variables
       Tip 8: Global variables
       Tip 9: Table variables
       Tip 10: Flow control statements
       Tip 11: Blocks, looping, and conditional statements
       Tip 12: unconditional and scheduled execution
       Tip 13: Transact SQL cursors
       Tip 14: Cursor related statements
       Tip 15: Problems and justified use of cursors

      The previous tip is from "15 tips in 15 minutes: Basic Transact-SQL Programming Constructs," excerpted from Chapter 3, of the book "Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET" by Dejan Sunderic, courtesy of McGraw-Hill Publishing.


      This was first published in February 2007

    There are Comments. Add yours.

     
    TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

    REGISTER or login:

    Forgot Password?
    By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
    Sort by: OldestNewest

    Forgot Password?

    No problem! Submit your e-mail address below. We'll send you an email containing your password.

    Your password has been sent to: