Post reply

How to create procedure from definition of another procedure

  • November 29, 2010 at 12:34 am

    #227661

    Hello everyone!!!

    I need help to create procedure from definition of another procedure(may be from sp_helptext)

    please help

  • November 29, 2010 at 1:00 am

    #1256131

    I am not sure why you need to re-create sp with other name that may not be best practice

    but here is the way how you can do that

    declare @sql varchar(4000)

    select @sql=routine_definition from information_schema.routines where routine_name='usp_ExistingSPName'

    set @sql=replace(@sql,'usp_ExistingSPName','usp_NewSpName')

    print @sql

    exec(@sql)

  • November 29, 2010 at 3:05 am

    #1256155

    Thanks...

    I do this in production for save back up's before altering procedure

  • November 29, 2010 at 6:35 am

    #1256218

    roman.goldenberg (11/29/2010)

    Thanks...

    I do this in production for save back up's before altering procedure

    As a side bar, backups of this nature are fine but you may want to look into some form of source control.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 29, 2010 at 6:51 am

    #1256224

    Information_Schema.Routines will only return the first 4000 characters of your stored procedure. If there is any chance at all that your stored proc is longer than that, you'll probably want to use sp_helptext instead.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • November 29, 2010 at 10:16 am

    #1256333

    I'd suggest using OBJECT_DEFINITION rather than the other methods mentioned.

    Scott Pletcher, SQL Server MVP 2008-2010

  • November 29, 2010 at 4:35 pm

    #1256543

    I agree with Scott... the OBJECT_DEFINITION function is documented and easy to use for just about everything except a table. Take a look in Books Online for the details.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 30, 2010 at 1:07 am

    #1256635

    and what about length?

  • November 30, 2010 at 6:51 am

    #1256758

    roman.goldenberg (11/30/2010)

    and what about length?

    The only time length is a problem is if you try to view the output of the function in SSMS... which limits things to 8K.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 30, 2010 at 8:20 am

    #1256814

    Since OBJECT_DEFINITION returns an NVARCHAR(MAX), I would expect it to be able to return up to 2G - 1 (or roughly 1 billion visible characters).

    I've never had any issue with the return value from it not containing the full text of a procedure ... although actually viewing the entire text at once can be tricky, because of the limitations in Mgmt Studio and some other viewing software.

    You may want to write a quick loop to chop it into pieces to PRINT / SELECT the results to view in MS.

    SELECT displays more characters than PRINT, but both have limitations.

    Scott Pletcher, SQL Server MVP 2008-2010

  • November 30, 2010 at 9:10 am

    #1256848

    scott.pletcher (11/30/2010)

    Since OBJECT_DEFINITION returns an NVARCHAR(MAX), I would expect it to be able to return up to 2G - 1 (or roughly 1 billion visible characters).

    I've never had any issue with the return value from it not containing the full text of a procedure ... although actually viewing the entire text at once can be tricky, because of the limitations in Mgmt Studio and some other viewing software.

    You may want to write a quick loop to chop it into pieces to PRINT / SELECT the results to view in MS.

    SELECT displays more characters than PRINT, but both have limitations.

    If you want to view it, I'd suggest running sp_helptext instead. (Oh, wait. I already did suggest that.)

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • November 30, 2010 at 9:17 am

    #1256852

    Actually, I think the original q dealt with duplicating the stored proc code. For that, I still think OBJECT_DEFINITION works much better than sp_helptext.

    Scott Pletcher, SQL Server MVP 2008-2010

  • November 30, 2010 at 11:45 pm

    #1257169

    Thanks all for the help

Viewing 13 posts - 1 through 12 (of 12 total)

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