How to add header comments when altering stored procedures in SQL Server

Question

I have a stored procedure where there are no header comments. I want to add them, but whenever I try, it is not included.

In SQL Server Management Studio I :

1.Right-click my stored procedure and click modify

USE [ABigDB]
GO
/****** Object:  StoredProcedure [dbo].[spDoWork]    Script Date: 21/08/2015 14:11:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spDoWork]
      @Id uniqueidentifier,
      @Session nvarchar(50),
      @XMLData xml
WITH EXECUTE AS OWNER
AS
BEGIN
--etc etc...
END

2.I paste comments above the stored procedure and run the script :

-- Stored Procedure
--    Author:           Dave
--    Create date:      21/08/2015
--    Description:      Does Stuff      
--  Change history
--      07/08/2015  - Overlord - Done stuff
--      06/08/2015  - Kerrigan - Done more stuff
USE [ABigDB]
GO
/****** Object:  StoredProcedure [dbo].[spDoWork]    Script Date: 21/08/2015 14:11:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spDoWork]
      @Id uniqueidentifier,
      @Session nvarchar(50),
      @XMLData xml
WITH EXECUTE AS OWNER
AS
BEGIN
--etc etc...
END

3.When I modify the same stored procedure it appears as :

USE [ABigDB]
GO
/****** Object:  StoredProcedure [dbo].[spDoWork]    Script Date: 21/08/2015 14:11:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spDoWork]
      @Id uniqueidentifier,
      @Session nvarchar(50),
      @XMLData xml
WITH EXECUTE AS OWNER
AS
BEGIN
--etc etc...
END

So how do I get the comments to appear there?

Try putting the comments underneath the `ALTER PROCEDURE`, before `AS` — Sam Cohen-Devries, Aug 21 '15 at 13:14
Try adding `USE GO` at the top then `-- Stored Procedure -- Author: Dave -- Create date: 21/08/2015 -- Description: Does Stuff -- Change history -- 07/08/2015 - Overlord - Done stuff -- 06/08/2015 - Kerrigan - Done more stuff` your comments and then the stored procedure `ALTER PROCEDURE [dbo].[spDoWork] @Id uniqueidentifier, @Session nvarchar(50), @XMLData xml WITH EXECUTE AS OWNER AS BEGIN --etc etc... END` — Abhishek, Aug 21 '15 at 13:18

score 15 · Accepted Answer · answered Aug 21 '15 at 13:23

I solved it by doing the following:

USE [ABigDB]
GO
/****** Object:  StoredProcedure [dbo].[spDoWork]    Script Date: 21/08/2015 14:11:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Stored Procedure
--    Author:           Dave
--    Create date:      21/08/2015
--    Description:      Does Stuff      
--  Change history
--      07/08/2015  - Overlord - Done stuff
--      06/08/2015  - Kerrigan - Done more stuff

ALTER PROCEDURE [dbo].[spDoWork]
      @Id uniqueidentifier,
      @Session nvarchar(50),
      @XMLData xml
WITH EXECUTE AS OWNER
AS
BEGIN
--etc etc...
END

This is basically @Abhishek 's answer -- yes, you put your comment-header *just before* the ALTER/CREATE PROCEDURE line. — NateJ, Mar 29 '16 at 19:12

score 1 · Answer 2 · answered Sep 05 '17 at 17:39

1

Consider using the meta data in addition to your procedures, tables, columns, etc. for documentation purposes.

See the following that helps when reviewing your db objects.

Is it possible to add a description/comment to a table in Microsoft SQL 2000+

answered Sep 05 '17 at 17:39

Ray Porrata

87
2
2

How to add header comments when altering stored procedures in SQL Server

2 Answers2