**Summary**

Is there an efficient way to run large numbers of Dynamic SQL (on SQL Server 2005)?

**Details**

Our system allows users to create "email alert" subscriptions - where new matches on the system are emailed to them on a daily basis.

The subscription allows for multiple options, including the use of search keywords. A parser written by myself outputs the appropriate SQL code, taking into account `and`, `or` and brackets `()`. *The parser will not allow anything through that could be used for SQL Injection.*

For example, the keywords might be entered by the user as `this (that or other)` and the resultant query would end up roughly as...

```
SELECT *
FROM [VW_EMAIL_ALERT]
WHERE ([SEARCH] LIKE '%this%' AND ([SEARCH] LIKE '%that%' OR [SEARCH] LIKE '%other%'))

```

Each night, all those subscriptions are processed individually, because each one is potentially unique. The result is that the batch processing has to run a cursor over every subscription and run the SQL through `sp_executesql`.

Obviously this is highly inefficient, and can cause serious overloading - leading in some cases to timeouts. The stored-procedure that runs this processing is coded to split the subscriptions into blocks, so they're not all being called at once.

**Is there a better/more efficient way to do this?**

*Note: Unfortunately we are currently stuck supporting a minimum of SQL Server 2005, as some of our clients still use that technology*