proc formatting getting disrurbed

• S_Kumar_S

  SSCrazy Eights

  Points: 9037

  More actions

  July 16, 2010 at 12:17 am

  #97396

  I wanted to append a fix CATCH block at end of my many procs. If I use OBJECT_DEFINITION or Sys.syscomments, then I get definition in one line and all my formatting is gone.So don't was to use that(or is there a way around which may retain the formatting??).

  sp_helptext is another option. And I tried to do something like this:

  declare @str varchar(max)

  declare @t table(txt varchar(max))

  set @str= 'sp_helptext mytstproc'

  insert into @t

  exec(@str)

  select * from @t

  Union

  SELECT 'catch blah blah end catch'

  but here rows dont come in proper order. Any suggestions??

  Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

• S_Kumar_S

  SSCrazy Eights

  Points: 9037

  More actions

  July 16, 2010 at 12:28 am

  #1193899

  One alternative I found is to use Union All. Looking for more which makes use of sys.syscomments....

  Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.