A question about looping

Hi All,

I would like to know the best way to approach the following requirement:

I have an ASP.net 2 web site which gets its data from SQL 2005.

I am trying to run a series of 'rules' which are SQL where statements stored in a table, against rows stored in another table. I open the 'Rules' table looping through all records. I copy each rule to a string and put it on the end of the SQL statement so that the rule will only be appended if it passes the rule... this may be a little confusing.

The rules process will fire when the details have been submitted to the database.

Table containg rules would contain something like:

ID, RuleSQL

1, (ClientAge >18)

2, (ClientIncome>10000)

3 Etc...

This a very simplified version of the table but gives the general idea.

I currently use ASP.NET 2 and sqlconnections/datareaders to do this. I would like to know if there is a way of doing the same thing server side using Transact SQL because that would (I believe) speed up the time taken to perform all the tests as i wouldn't need to rely on ASP to open all recordsets and append the data.

If the ASP route would be the standard way of doing it and is not likely to have a detremental effect on performance then i am fine to stick with it because i know it works.

any comments or suggestions would be welcomed.

Thanks,

Ian

Yes, you can definitely do what you are describing in the database!

FYI, if you need to embed single quotation marks in the sql statement string (that is surrounded by sinqle quotes, as in 'set status = 'done'', you have to use two sinqle quotes in a row to get one single quote in the sql statement output: 'set status = ''done'''. You keep the sinqle quotes on the outside of the string, and all single quotes inside the string need to be double single quotes. Don't use a single double quote. :)

declare @.sql_statement varchar(max)
declare @.table_name varchar(256)
declare @.rule_where_clause varchar(max)

declare rule_csr cursor FAST_FORWARD for
select table_name, rule_where_clause
from the_rules
order by table_name, rule_where_clause
for read only

open rule_csr

fetch nextfrom rule_csrinto @.table_name, @.rule_where_clause

while(@.@.fetch_status<>-1)
begin

set @.sql_statement = 'Update ' + @.table_name + ' set status = -1 where ' + @.rule_where_clause

exec (@.sql_statement)

fetch nextfrom rule_csrinto @.table_name, @.rule_where_clause

end

close rule_csr
deallocate rule_csr

|||

Forgot to add this warning.

When possible, avoid using cursors because they are relatively slow compared with re-structuring your sql commands to make proper use of set-based data manipulation (as opposed to row-based data manipulation). In this case, you are probably stuck with using a cursor.