self-mod stored proc

  • Anyone uses self-modifying code in stored procs ?

    Something like:

    create sp_selfmod

    as

    begin

    declare @b-2 nvarchar(4000)

    ...some_code...

    select @b-2 = c.text

    from

    dbo.syscomments c,

    dbo.sysobjects o

    where

    o.id = c.id and

    c.id = object_id('self_mod')

    select @b-2 = replace(@b,'orig_piece_of_code','new_piece_of_code')

    select @b-2 = 'alter '+substring(@b,8,len(@b))

    execute sp_executesql @b-2

    end

    I'm just playing with it, just wondering if anyone found a practical use for it.

    And don't tell me that self-modifying code is a bad programming practice 😉

    ps

  • G'day,

    I use code similar to what you are describing every day.

    An example is where you want to apply the same select to a dynamic set of databases.  Write a dynamic query.  Maybe something like

    SET @MySqlStr = 'SELECT COUNT(*) FROM <DB>.dbo.SomeTableName'

    Then in a loop, use replace to modify <DB> with the target DB name or names.

    I have trivialized this example, but the approach works well.  I would not recommend using this technique in normal, transactional processing.  I currently use it for deploying code updates across a series of DBs based on DB names stored in a table and marked for update.

    Wayne

  • I'm using your kind of example daily. I'm talking about code modifications.

  • Why you need to store your code as SP if you can execute it immediately when you need it?

    Are you trying to allow users to create SP from front end application???

    Modify SP from an application is a task for hackers, not DBA.

    If you want just different options of code to be executed depending on parameter supplied use groupped SP:

    CREATE PROCEDURE MyProc ;1

    AS ....

    CREATE PROCEDURE MyProc ;2

    AS ....

    CREATE PROCEDURE MyProc ;3

    AS ....

    and call one of the options depending on parameter:

    IF @P = 1 execute MyProc ;1

    ELSE IF @P = 2 execute MyProc ;2

    ELSE IF @P = 3 execute MyProc ;3

     

    _____________
    Code for TallyGenerator

  • thank you einstein...

Viewing 5 posts - 1 through 4 (of 4 total)

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