Using comments in a SQL Server stored procedure

By: Greg Robidoux

Overview

One very helpful thing to do with your stored procedures is to add comments to your code.  This helps you to know what was done and why for future reference, but also helps other DBAs or developers that may need to make modifications to the code.

Explanation

SQL Server offers two types of comments in a stored procedure; line comments and block comments.   The following examples show you how to add comments using both techniques.  Comments are displayed in green in a SQL Server query window.

Line Comments

To create line comments you just use two dashes "--" in front of the code you want to comment.  You can comment out one or multiple lines with this technique.

In this example the entire line is commented out.

-- this procedure gets a list of addresses based 
-- on the city value that is passed
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SELECT * 
FROM Person.Address
WHERE City = @City
GO

This next example shows you how to put the comment on the same line.

-- this procedure gets a list of addresses based on the city value that is passed
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SELECT * 
FROM Person.Address
WHERE City = @City -- the @City parameter value will narrow the search criteria
GO

Block Comments

To create block comments the block is started with "/*" and ends with "*/".   Anything within that block will be a comment section.

/* 
-this procedure gets a list of addresses based 
 on the city value that is passed
-this procedure is used by the HR system      
*/
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SELECT * 
FROM Person.Address
WHERE City = @City
GO

Combining Line and Block Comments

You can also use both types of comments within a stored procedure.

/* 
-this procedure gets a list of addresses based 
 on the city value that is passed
-this procedure is used by the HR system      
*/
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SELECT * 
FROM Person.Address
WHERE City = @City -- the @City parameter value will narrow the search criteria
GO