Based on my years of writing code, I tend to put 'header level' comments at the top of a file. As a result, when I write stored procedures, I tend to also put the header comments at the very top of the SQL script file.
However, my understanding was that comments at the very top of a SQL Server stored procedure script, located before the CREATE PROCEDURE command, would not be saved to SQL Server. So if you later scripted out an existing stored procedure from SQL Server, the resulting script would not include the header comments.
I was about to write a post about this situation, recommending where to place comments in stored procedures, when a weird thing happened.
I couldn't reproduce the issue.
On SQL Server 2014, I created a test stored procedure.
In this script, I put header comments at the top, before the CREATE PROCEDURE command. I then ran the script and created the procedure.
But when I scripted out the procedure, to my surprise, the resulting script showed my header comments.
Say what???
I was very surprised, and confused. Was I going crazy? Had I imagined that header comments were not saved with stored procedures?
I have worked with Victoria Yudin for several years, and one of our rituals is that I will sometimes draft a procedure, forgetting about the comment position, and she would always have to move my header comments down into the body of the proc.
Coincidentally, this week I received an email from a customer who said he modified my stored procedure script to move my header comments into the body of the procedure so that they would be saved. So that's three people who believed that SQL stored procedure header comments aren't saved.
I emailed the above screen shots to Victoria, and she was as surprised as I was. She tested the behavior on SQL 2014, 2012, 2008 R2, and even 2008. All versions behaved the same, with all versions retaining the stored procedure header comments.
So, in short, this seems to be a SQL Server urban legend. Sort of.
We're pretty sure we aren't going crazy, and that we've seen the issue in action, so Victoria and I are wondering whether the issue might have existed in SQL 2000 or SQL 2005. Neither of us have a vintage install of SQL Server handy, so we weren't able to test it with those older versions. Regardless, that would mean that we have been mistaken about this issue for the last 8 years. Yikes.
The other possible explanation is that someone might be highlighting just the CREATE PROCEDURE portion of the proc scripts when creating them, therefore excluding the header comments. This seems odd and unlikely, as we typically deliver one procedure per SQL file, and there would be no need to highlight a portion of the script. I certainly don't highlight a portion of the script when running my proc script files.
At this point I'm puzzled, but I'm happy to know that the myth of SQL Server stored procedure header comments has been busted. Everyone go ahead and add header comments to your heart's delight in all of your stored procedure scripts.
If there's something I missed, or if you have an explanation of why stored procedure header comments may not be saved, please post a comment and let me know.
Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics
GP Certified IT Professional in Los Angeles. He is the owner of Precipio
Services, which provides Dynamics GP integrations, customizations, and
automation solutions.
3 comments:
Hi Steve!
No, that was just a myth. On the other hand it is nice to know that even Victoria Yudin is wrong sometimes.
If you CREATE PROCEDURE then the comments that are automated in the header will disappear, but the rest of the comments will stay. So if you've changed the header comment from
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE dbo.MyStoredProc
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT 1;
END
Then that comment will go.
But if you'd change it to
-- =============================================
-- Author: Endow, Steve
-- Create date: 21/06/2016
-- Description: MyStored Proc concur the world
-- =============================================
Then the comment field will stay.
BTW comments before
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Will disappear.
Could it be that you had a "GO" between your header comments and the "CREATE PROC"? If you did, then the comments would be considered a separate batch, and not sent to SQL Server as part of the proc itself.
HiSteve,
The header will be saved in all SQL Server versions (from 2000 to 2016), unless you separate the header from the definition with the GO statement
Mohamed Bouarroudj
www.sqldbtools.com
Post a Comment