Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using comments in a SQL Server stored procedure



By:
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





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, February 25, 2016 - 8:27:25 AM - Zuwaib Back To Top

 yeah... this tip really helped me. Thank You

 


Learn more about SQL Server tools