Post reply

how to stroe the text of an sp into a variable

  • May 15, 2008 at 1:07 am

    #71268

    i want to store the codes with in an sp to a variable

    i tried this

    declare @v-2 text(8000)

    set @v-2=sp_helptext GET_TRANSTYPE

    but getting some error

    can anyone give a solution for this

    regards

    navadeep

  • May 15, 2008 at 2:02 am

    #815683

    You can this:

    declare @input varchar(100),

    @id int,

    @helpText varchar(max)

    set @input = 'Your Stored Proc Name'

    set @id =(select top 1 object_id

    from sys.objects

    where [name] = @input

    and type_desc = 'SQL_STORED_PROCEDURE')

    set @helpText = (select [text] from sys.syscomments where id = @id)

    select @helptext

    EDIT: The above is not 100%, but is a step in the right direction. Depending on the length of the sp, there can be more than one record for it's definition.

  • May 15, 2008 at 7:46 am

    #815872

    That only works for SQL 2000. For SQL 2005 use sys.sql_modules

    select definition

    from

    sys.sql_modules where object_id = object_id ( )

    DAB

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply