Sunday, 30 August 2015

syntax error in textheader of stored procedure - Cause and Solution

Hi Folks,

Comments play an important part in writing any code. Especially when somebody else reads your code. So the other day I was writing a stored procedure and some comments on top to list the tasks being performed inside the stored procedure. We are using sql server 2005 and the code snippet was something like this :-

/*abc
/*
/*
-----
----The logic of the stored procedure is ...

*/

*/
*/

alter proc abcd @i int
as
select 1

I was able to create the sp without any problems, however on clicking modify (when you try to see or edit the sp inside ssms) it gave me an error:-
syntax error in textheader of stored procedure

On further searching online I found that it has to do with embedded comments like the one as following :-

/*
/*
----*/
*/

--------------------------------------


If you notice above lines you ll see comments are being embedded inside parent /* -----*/ comments.  So I got rid of the internal comments like this

/*abc


-----
----The logic of the stored procedure is ...




*/

alter proc abcd @i int
as
select 1

and it saved my day.

I tried to simulate similar problem in SQL SERVER 2014, however could not do that. So I think it can only be found with SQL SERVER 2005 version.







No comments:

Post a Comment

SSIS Issues : A Day of Learning

Seldom are the days when you run into complex issues but resolve them in the shortest interval of time. Thanks to Larry Page and my fello...