November 3, 2005 at 9:13 am
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
November 3, 2005 at 10:59 am
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
November 3, 2005 at 12:19 pm
I'm using your kind of example daily. I'm talking about code modifications.
November 3, 2005 at 3:15 pm
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
November 4, 2005 at 12:27 pm
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