Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

store output of sp_helptext to local variable Expand / Collapse
Author
Message
Posted Thursday, March 20, 2008 10:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, March 27, 2011 6:34 AM
Points: 80, Visits: 139
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.
Post #472376
Posted Thursday, March 20, 2008 3:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 17, 2009 3:34 PM
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

Post #472581
Posted Thursday, March 20, 2008 3:13 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
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."
Post #472589
Posted Thursday, March 20, 2008 3:17 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
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."
Post #472591
Posted Thursday, March 20, 2008 3:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 17, 2009 3:34 PM
Points: 9, Visits: 31
sorry about that. I've edited the original post to fix the error.
Post #472593
Posted Thursday, March 20, 2008 3:48 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
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."
Post #472611
Posted Monday, August 25, 2014 1:24 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:48 PM
Points: 208, Visits: 335
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;

Post #1607214
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse