Remove Single Line Comments from SQL Server Procedure

If you follow this blog regularly, you would have noticed that I have written logic as well as code to remove multi-line comments from SQL Server Procedure. You also can follow the link to get to that. Here, my concern is to remove single line comment from SQL Server procedure. It's a much simple compared to removing multi-line comment.

 

Single line comment in SQL Server is preceded by double dash like "—". So, you just consider following two points.

1.      You need to remove the lines beginning with double dash.

2.      If a line contains double dash at the middle, then remove part of line after the double dash.

 

An example will clarify more on this. First, I created a test procedure with name sp_helptext.

 

create proc sp_proc1  

as   

begin   

 create table #tt  

 (  

  col1 varchar(200)  

 )  

 

-- insert into ##tt values ('test data 1')

insert into #tt values ('test data 2')  -- test comments

-- insert into ##tt values ('test data 3')

  -- test comments

 

select *

from #tt 

end   

 

Then, I created a temporary table with name #tbl_sp_text to populate the help text of procedure on that. Finally, I applied above mentioned logic to retrieve the lines without any single line comments. Here is the complete code.

 

create table #tbl_sp_text ( id int identity(1,1), sp_text varchar(8000))

insert into #tbl_sp_text

exec sp_helptext  sp_proc1 /* procedure name */

 

 

select      case when charindex('--',sp_text) > 0 then substring(sp_text,0,charindex('--',sp_text))

      else sp_text end sp_text

from  #tbl_sp_text