For a second time I encountered a strange problem with a stored procedure, which is simply not parsing and executing correctly. On both occasions the problem occurred with pretty long stored procedures - the one I have now is 2000 lines long. The first time I saw this I was working on SQL Server 2000, now I have a SQL Server 2005, but the database is in SQL Server 2000(80) compatibility level.
The problem is that a regular SQL comment is sometimes merged with the line under it.
--some comment
some SQL code
is parsed as
--some comment some SQL code
You can see how this can be a huge problem. From what I know the contents of a sp are stored in the system table syscomments, which splits the longer stored procedures into several records. I am wondering if there isn't a parsing problem when putting together a longer stored procedure. Perhaps if a "new line" character happens to be at the records split it is ignored and the two lines of TSQL get merged.
My "fix" has been to readjust/remove the comments, add additional empty rows in between or just split the stored procedure into several smaller sps.
I will appreciate your feedback if you know more about parsing the stored procedure from the database engine or if you have a better work-around.