sp_helptext

• singhster

  Ten Centuries

  Points: 1387

  More actions

  March 25, 2003 at 3:09 am

  #105635

  I'm having a bit of trouble using sp_helptext within a stored procedure. I'm trying to assign the output of it into a variable, but get a syntax error.

  exec sp_helptext @SPNameText -- this works

  select @SPText = exec sp_helptext @SPNameText -- doesn't work

  Can anyone help?

• Chris Hedgate

  One Orange Chip

  Points: 25041

  More actions

  March 25, 2003 at 3:33 am

  #452013

  For a procedure that returns a value that you want to store in a variable you would execute it like this:

  EXEC @variable = sp_whatever @parameter

  However, sp_helptext does not return any value. It does however 'return' a result set containg of one or more rows of 255 characters each, with the text of the object DDL. So what you can do is this:

  CREATE TABLE #helptext (rownum int identity(1,1), rowtext varchar(255))

  INSERT INTO #helptext (rowtext) EXEC sp_helptext 'procname'

  SELECT * FROM #helptext ORDER BY rownum

  --

  Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

  http://www.sql.nu

  --
  Chris Hedgate http://www.hedgate.net/
  Contributor to the Best of SQL Server Central volumes
  Articles: http://www.sqlservercentral.com/columnists/chedgate/

• singhster

  Ten Centuries

  Points: 1387

  More actions

  March 27, 2003 at 3:59 am

  #452311

  Thanks Chris, this has worked a treat!