Using comments in a SQL Server stored procedure

 

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

Using try catch in SQL Server stored procedures

 

Overview

A great new option that was added in SQL Server 2005 was the ability to use the Try..Catch paradigm that exists in other development languages.  Doing error handling in SQL Server has not always been the easiest thing, so this option definitely makes it much easier to code for and handle errors.

Explanation

If you are not familiar with the Try...Catch paradigm it is basically two blocks of code with your stored procedures that lets you execute some code, this is the Try section and if there are errors they are handled in the Catch section. 

Let's take a look at an example of how this can be done.  As you can see we are using a basic SELECT statement that is contained within the TRY section, but for some reason if this fails it will run the code in the CATCH section and return the error information.

CREATE PROCEDURE dbo.uspTryCatchTest
AS
BEGIN TRY
    SELECT 1/0
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber
     ,ERROR_SEVERITY() AS ErrorSeverity
     ,ERROR_STATE() AS ErrorState
     ,ERROR_PROCEDURE() AS ErrorProcedure
     ,ERROR_LINE() AS ErrorLine
     ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH