Tutorial on how to use comments in a SQL Server stored procedure

 
One of the document conventions to perform with your stored procedures is to mention comments to your code. It is a kind of reference document that anytime can be referrred to know what was done.

Description

SQL Server provides two categories of comments in a stored procedure, namely the line comments and block comments.

The examples show below depicts how to add comments via both the techniques. Note, comments are mentioned in green color in a SQL Server query window.

Line Comments

To create line comments, use two dashes “–” in front of the code you wish to comment. You are also allowed to comment on one or multiple lines using this technique.
In this given 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
 
In the next given example you will find 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

For creating block comments the block begins with “/*” and ends with “*/”. And anything mentioned within that block is 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

Combination of Line and Block Comments

You are also allowed to use both categories of comments mentioned 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

Minimizing amount of network data for SQL Server stored procedures

There are several tricks that can be used when you script T-SQL code. One of these is to minimize or reduce the amount of network data for each and every statement that happens within your stored procedures.

Every time a SQL statement gets executed it returns the number of rows which were affected. via using “SET NOCOUNT ON” present within your stored procedure you can shut off these messages and minimizes some of the traffic.

Description

As mentioned above there is not any reason to return messages about what is happening within SQL Server when you execute a stored procedure.

In case you are running things from a query window, this might be useful, however most end users that run stored procedures via an application would never view these messages.
You are still allowed to use @@ROWCOUNT to get the number of rows impacted via a SQL statement, hence turning SET NOCOUNT ON will not going to change that behavior.

Not using SET NOCOUNT ON

This given below example depicts store procedure without using SET NOCOUNT ON:
 
— not using SET NOCOUNT ON
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SELECT *
FROM Person.Address
WHERE City = @City
GO
 
The messages that will be received in returned would be similar to as shown below:
 
(23 row(s) affected)

Using SET NOCOUNT ON

This given example uses the SET NOCOUNT ON as mentioned below. It is a best practice to put this at the starting of the stored procedure.
 
— using SET NOCOUNT ON
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SET NOCOUNT ON
SELECT *
FROM Person.Address
WHERE City = @City
GO
 
The messages that you are going to returned would be similar to this:
 
Command(s) completed successfully.

Using SET NOCOUNT ON and @@ROWCOUNT

This given example uses SET NOCOUNT ON, however, this still return the number of rows effected by the previous statement. This depicts that this still going to work.
 
— not using SET NOCOUNT ON
CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SET NOCOUNT ON
SELECT *
FROM Person.Address
WHERE City = @City
PRINT @@ROWCOUNT
GO
 
The returned messages would be similar to this:
 
23

SET NOCOUNT OFF

In case you wish to turn this behavior off, then use the command “SET NOCOUNT OFF”.
 

Kristin is a content strategist at Techarex Networks. Kristin follows the B2B technology space closely and loves to write on the latest changes in technology, futuretech and fixes for day to day how to issues. Besides writing Kristin also loves music, moves and skating.