Flow control statements

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

/*
-- List all equipment.
Select * from Equipment
*/

/* 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.
*****************************************************************/

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.

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL/Transact SQL (T-SQL) Working with sparse columns in SQL Server 2008 Determining the source of full transaction logs in SQL Server New GROUP BY option provides better data control in SQL Server 2008 Using the OPENROWSET function in SQL Server Loading data files with SQL Server's BULK INSERT statement Importing and exporting bulk data with SQL Server's bcp utility Testing transaction log autogrowth behavior in SQL Server Securing SQL Server with access control, login monitoring and DDL triggers Top 10 SQL Server development tips of 2008 The sqlcmd utility in SQL Server SQL/Transact SQL (T-SQL) Research SQL Server Stored Procedures Top 10 SQL Server development tips of 2008 SQL Server trigger vs. stored procedure to receive data notification SQL Server errors, failures and other problems fixed from the trenches SQL Server and data manipulation in T-SQL How to use SQL Server 2008 hierarchyid data type SQL Server stored procedures tutorial: Write, tune and get examples Check SQL Server database and log file size with this stored procedure SQL Server source code analysis and management adds database security Configure SQL Server Service Broker for sending stored procedure data Find size of SQL Server tables and other objects with stored procedure XML in SQL Server Processing XML files with SQL Server functions Top 10 SQL Server development tips of 2008 Manage traces in SQL Server 2005 Analysis Services with XMLA commands Create a computed column in SQL Server using XML data Create DDL table in SQL Server 2005 to audit DDL trigger activity Retrieve XML data values with XQuery in SQL Server 2005 XML data type in SQL Server 2005 vs. VARCHAR (MAX) T-SQL commands vs. XML AUTO in SQL Server Basic Transact-SQL programming constructs: 15 tips, 15 minutes T-SQL identifiers

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary ACID  (SearchSQLServer.com) commit  (SearchSQLServer.com) DAO  (SearchSQLServer.com) fetch  (SearchSQLServer.com) OLE DB  (SearchSQLServer.com) query  (SearchSQLServer.com) SQL  (SearchSQLServer.com) T-SQL  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary