SQL Stringbuilder in Stored Procedure

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)

SQL Stringbuilder in Stored Procedure

Author

Topic

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2003-07-21 : 03:57:40

I'm making a Workflow Management Application. I want to store the intelligence in the database itself.

My 'system' has a scheduled Stored Procedure which makes tasks for users based on a legacy system.

I have a table with 'Actions' on it. This is a description of what to do, but also a SQL String (INSERT INTO).
My stored procedure is getting the SQL string column from the Action table and executes it.

My question is (since I don't have a lot of records yet): What would be better practice? Write the stored procedures hard coded, or leave the SQL Strings in the Action table and execute it?

Or is there a third way? If I'm not clear I can give some code examples.

Henri

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

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-07-21 : 04:05:35

Please give some examples. How many "actions" are there, and how many different types can there be etc??

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2003-07-21 : 05:04:06

Kalle,

It is dutch, But you'll get the drift.

This is a hard coded part of my stored procedure (there're 50 actions like this with 50 different INSERT INTO's).


-- Actie 1 Inmeten
INSERT INTO Workflow.dbo.tblTaak (Invoerder, ReferentieProces, Actie, Titel, Inhoud, Prioriteit, Rol, DagenOpen, Doorlooptijd)
SELECT 'SYS', rp.[Id], 1 -- Actie 1 --> Inmeten versturen
, actie.Naam   -- Titel van de actie
, actie.Inhoud -- Inhoud
,30 -- Prioriteit normaal
, actie.Rol -- Rol montage afdeling
--, DateDiff (d, po.InvoerDatum, GetDate()) -- Aantal dagen open
,(SELECT COUNT(*) FROM Oresys.dbo.tblDatumPeriode WHERE Datum BETWEEN po.InvoerDatum AND GetDate() AND Werkdag = 1) -- Werkdagen open
, actie.Doorlooptijd -- Doorlooptijd

FROM Workflow.dbo.tblProcesOrder po
JOIN Workflow.dbo.tblReferentieProces rp ON po.ReferentieProces = rp.[Id]
JOIN Workflow.dbo.tblReferentie r ON r.[Id] = rp.Referentie
JOIN KIMS.dbo.Kimslnk k ON k.Refnr = r.ReferentieNummer
JOIN Oresys.dbo.tblVestiging v ON v.VestigingCode = k.Vest AND v.Actief = 1
JOIN Oresys.dbo.tblAttribuut a ON r.ReferentieNummer = a.ReferentieNummer
JOIN Workflow.dbo.tblActie actie ON Actie.[Id] = 1 AND Actie.Actief = 1 -- LET OP, ook hier actie goed invullen !!
WHERE VerstuurdInmeten IS NULL -- Er is nog niet ingemeten
AND NietInmeten IS NULL -- Er is ook niet gezegd dat er niet ingemeten hoeft te worden

I want to store these insert as TEXT fields in the tblAction.

and execute it in a stored procedure like
DECLARE @InsertQuery AS VARCHAR (2000)

SELECT @InsertQuery = ActionText FROM tblAction WHERE ActionId = 3

EXEC (@InsertQuery)

The proces table has some 50.000 records (so every tblAction query is over this 50.000 records).

But is there a huge performance penalty?

Henri

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

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2003-07-21 : 05:30:29

Does you action table hold the complete statement or does the table contain other columns with which to build you SQL statement?

My opinion is that you are better off hard coding your INSERT statements. Somehow I can not see it performing any better when it has to query a table of 50000 records for the correct statement and then run it dynamically.

---------------
Shadow to Light

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2003-07-21 : 07:54:37

As it is now, the whole statement is in one TEXT column. But I can inmagine it will change. I will add for example an ##Department## in my string as sort of a parameter and I will REPLACE this with a Parameter value:

DECLARE @InsertQuery AS VARCHAR (2000)
SELECT @InsertQuery = ActionText FROM tblAction WHERE ActionId = 3

SET @InsertQuery = REPLACE (@InsertQuery, '##Department##', @CurrentDepartment)

EXEC (@InsertQuery)

I don't want it hard coded if I can avoid it. Because then I can make an (admin) form with the possibility to change the code dynamicaly without the neccessity of a programmer. But I want to now if this comes at a high price performance wise.

Henri

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

Edited by - henrikop on 07/21/2003 07:56:48

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-07-21 : 08:39:55

It should work. Just make sure the users have the correct permissions. Remember, they could also put "delete from tablex" and stuff like that. But you could parse the strings I guess, to make sure nothing is run which is dangerous.

Also, they might write regular statements which cause blocking in the database, long-running queries etc.

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-21 : 09:19:36

MOO

"Hard Code" the code...

Save yourself the hearth ache....

So you have 50 sprocs...so what...they're compiled and the optimizer will know what to do with them...

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources