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) Physical data storage in SQL Server 2005 and 2008 SQL Server 2008 data types: Datetime, string, user-defined and more SQL Server and data manipulation in T-SQL Enforcing data integrity in a SQL Server database Supertype and subtype tables in SQL Server Using SQL Server datetime functions GETDATE, DATENAME and DATEPART Ordering the results of a SQL query How to use SQL Server 2008 hierarchyid data type SQL Server data conversions from date/time values to character types SQL and SQL Server Tutorial and Reference Guide SQL/Transact SQL (T-SQL) Research SQL Server stored procedures How to use SQL Server 2008 hierarchyid data type SQL and SQL Server Tutorial and Reference Guide 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 Track changes to SQL Server 2000 and 2005 with one simple utility Troubleshoot SQL Server 2005 temporary table performance problems Use SQL Profiler to find long running stored procedures and commands XML in SQL Server 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) SQL Server Blog Watch T-SQL commands vs. XML AUTO in SQL Server Basic Transact-SQL programming constructs: 15 tips, 15 minutes T-SQL identifiers Date, time and number data types in SQL Server

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