Assigning result of SP_HELPTEXT to local variable

[davearia]

Hi,

 

I am trying to use the SQL Server stored procedure SP_HELPTEXT. The idea is to get the result from the execute and assign it to the variable. The SQL I am trying is this:

DECLARE @text text
SET @text = EXECUTE SP_HELPTEXT SP_NAME
PRINT @text

 

But this just errors.

 

Please help if you can, Dave.

[Nerseus]

The syntax you're looking for is:

EXECUTE @text = SP_HELPTEXT...

 

But a few problems.

1. You can't create a local "text" variable. You can replace "DECLARE @text text" with "DECLARE @text varchar(max)". NOTE the (max) syntax is SQL 2005 specific. Doesn't matter, it won't work :)

 

2. The assignment usage you're trying only takes the RETURN value and puts it into a variable. The sp_helptext proc returns the text via a SELECT statement. So, assuming the sp_helptext call works, it RETURNs a value of 0. So if you do #1 above, you'll simply get '0' in @text.

 

The sp_helptext proc really just looks at the system tables. I've used sp_helptext for a quick and dirty in a query window. But you may be better using the system tables.

 

Here's the query I generally use:

select text from syscomments where id = (select id from sysobjects where xtype='p' and name = 'procname')

 

I've hard-coded the xtype to 'p' for proc, in case you have a proc and table with the same name. I believe there's an object_id function that can be used to get the ID instead of my subselect on the sysobjects table, but I can never remember it (I had to look it up for this post) :)

 

One last note. In SQL 2005 the system tables are "better" accessed though some new system views, such as sys.objects. I don't really know the difference, but if this is production quality code or you're just a pureist, you may want to investigate those.

 

-ner

[davearia]

Thank Nerseus,

 

I'll try using your recommended approach and see what happens!

 

Thanks again, Dave.