How is this for practice? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How is this for practice?

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2003-08-27 : 05:20:06

I have a table with a column which contains (multiple) SQL statements. These are businessrules. Every day these businessrules run in the morning. I didn't put them in stored procedures because some users need to update them, and they change overtime.

This is what I did:


DECLARE @sql AS VARCHAR(8000)
DECLARE rs CURSOR FOR SELECT br.Inhoud FROM Workflow.dbo.tblBusinessRules br WHERE br.Actief = 1 ORDER BY [Id] 
OPEN rs
FETCH NEXT FROM rs INTO @sql
WHILE @@FETCH_STATUS = 0
	BEGIN
		EXEC (@sql)
		FETCH NEXT FROM rs INTO @sql
	END
CLOSE rs
DEALLOCATE rs

I know there's security involved. But is this very bad practice?

Henri

~~~
Success is the ability to go from one failure to another with no loss of enthusiasm

mr_mist
Grunnio

1870 Posts

Posted - 2003-08-27 : 05:39:24

It looks bad to me. I don't like dynamic SQL anyway, but even if you could do this without dynamic SQL, it seems to me that by simply having a task that runs every day executing whatever statements happen to be in tblBusinessRules you are exposing your database to an unneccessarily high level of risk. Also, where is your audit trail if things go wrong? How do you keep a track on what changes have been made? Do your business rules really change so often that you can't set up some stored procedures and run them there?

Lastly, I assume that, since there are a number of rules, this process gets run as "sa" or similar. In which case, what happens if someone ends up adding the rule "DROP yourreallimportanttable" ?

-------
Moo. :)

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2003-08-27 : 05:51:11

Haha, this is exaclty my thought. However I'm logging all changes in a history table. And security worries me. My dilemma is that I made an application which I can't properly finish and if they need my help, it will be hard to do that over the phone.

To make things worse, all my SQL instructions of the application are in the database itself (a table called tblSQL

. Dynamic SQL is my middle name. I even put my own scriptlanguage in it

and my manual is dynamic too).

The good thing though is IIS intergrated security. I'm one of few who can change the code.

The reason I placed the topic is (like you do) help me with things I didn't think of in this construction.

Thx anyway

Henri

~~~
Success is the ability to go from one failure to another with no loss of enthusiasm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources