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

Last Update: 3/24/2009






Comments For This Article




Sunday, September 24, 2017 - 1:47:42 PM - SUKHBIR SINGH Back To Top (66570)

 

 Very helpful and informative article on SP


Monday, August 07, 2017 - 6:47:57 PM - KEITH D HORLOCK Back To Top (64247)

This is great, but sql just deleted all my comments from all the sprocs I was working on today. Is there anyway to get them back?

I know I should have used something like Perforce, but up until today, SQL wasn't stripping out all the comments. Today, it did.

I didn't change anything on my installation.

 


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

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

 



download


get free sql tips
agree to terms


Learn more about SQL Server tools