Flow control statements

T-SQL flow-control statements require skill and lack user friendliness, yet they allow for complex procedures. This book excerpt covers the use of comments. Comments can be included inside the source code of a batch or stored procedure. Comments are ignored by SQL Server and can be used to help other developers understand your intentions.

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 (--) 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
    This Content Component encountered an error

    Pro+

    Features

    Enjoy the benefits of Pro+ membership, learn more and join.

    0 comments

    Oldest 

    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:

    -ADS BY GOOGLE

    SearchBusinessAnalytics

    SearchDataCenter

    SearchDataManagement

    SearchAWS

    SearchOracle

    SearchContentManagement

    SearchWindowsServer

    Close