Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


store output of sp_helptext to local variable


store output of sp_helptext to local variable

Author
Message
johnsql-193053
johnsql-193053
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 148
Hi,
I have a stored procedure mySP and I'd like to use a local variable to store the ouput from sp_helptext to that variable. However, I got the error "... Incorrect syntax near "mySP'."



declare @v varchar(8000)

set @v = sp_helptext mySP





Is there is better way to do such and to avoid the error?

Thank in advance.
Ralph Curry
Ralph Curry
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 31
It's not pretty, but you might try something like this:
declare @t table(line varchar(8000))
insert @t exec sp_helptext 'myProc'

declare @ddl varchar(8000)
select @ddl = ''
select @ddl = @ddl + line
from @t

print @ddl


RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
For some reason, this does not work for me. Keeps telling me that I have to "declare the scalar variable @ddl"

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
duh. Its a typo, should be @ddl on the select line.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Ralph Curry
Ralph Curry
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 31
sorry about that. I've edited the original post to fix the error.
RBarryYoung
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: General Forum Members
Points: 9438 Visits: 9517
Thanks for the example Ralph. I was just mad at myself for not noticing the typo before I posted my reply...

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
robert.mcleod
robert.mcleod
Old Hand
Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)Old Hand (338 reputation)

Group: General Forum Members
Points: 338 Visits: 440
I realize this post is old, but I found it handy for a problem I was working today.
One thing I added was to change the CREATE to an ALTER. This was to facilitate backing up an existing object.
Here is what my finished code looks like, should it help someone else.


SET NOCOUNT ON;
USE <DATABASENAME>;

DECLARE
@objName varchar(128);
SET @objName = <OBJECT NAME>;
/* Script out objects if they exist*/
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id
=
OBJECT_ID(@objName)
AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT',N'P'))
BEGIN
DECLARE
@t TABLE(line varchar(8000))
END;
INSERT INTO @t
EXEC sp_helptext @objName;
DECLARE
@ddl varchar(8000);
SELECT @ddl = '';
/* Change CREATE to ALTER because this object all ready exists */
SELECT @ddl = @ddl + REPLACE(line, 'CREATE procedure', 'ALTER PROCEDURE')
FROM @t;
PRINT @ddl;


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search