I have an idea for an application, but ideally it would rely on being able to write a sort of "sql rewriting" engine for MSSQL (much the same way that HTTP servers can have "URL Rewriting" modules) which could intersect sql statements before the server handles them and run some other code first.
Is this even possible with MSSQL or will I need to pursue an alternative method of achieving my goal?
The question is not "how" to make a rewriting but "where" you want rewriting to happen. You said you want something to be like in IIS, but this is not possible in MSSQL. In IIS you have request processing pipeline and there are many points where you can insert your custom code into this pipelene (i.e. create your own ISAPI extention, HTTPModule, events in Global.asax)
In SQL Server there is no SQL pipeline that is opened for developers. When SQL request came to SQL server, you cannot rewrite it. Triggers BEFORE or INSTEAD OF update/insert/delete will not solve the problem if you want to replace the table name in original query because if this table doesn't exists, you will get an exception prior you will be able to execute any custom business logic in triggers.
However you can "rewrite" SQL on the client (not on SQL server) by creating the custom data provider (if you have ADO.NET application). See MSDN - Implementing a .NET Framework Data Provider. This is not an easy task and will require a lot of efforts. In you code you may reuse most standard features of standard MSSQL Data Provider (System.Data.SqlClient) and override only functions where you want to parse SQL and replace certain commands.
Please also look on http://sqlfaultretryprovide.codeplex.com/ - this project was to build a custom .NET data provider to SQL Server that can provide automatically "retry on error" function. So this is not a SQL rewriter, but can be used as an example to build your own.
You can use triggers for INSERT, UPDATE, and DELETE, but there isn't an equivalent trigger for SELECT.
An alternative approach would be to write a program that intercepts TDS calls, modifies them, and passes them on to SQL server. So you would have your program listen on port 1433 or something and in your setup program change the SQL server port to listen on port 1434.
You're probably looking at creating a business layer into SQL. If you are trying to modify an existing app then you may have some trouble as the SQL adapters are all proprietary, but if your apps can be modified, you could probably write a business layer to sit on the DB, which would process the commands into the SQL server using a shared memory connection.
Another option is a magic CLR stored procedure which you feed the SQL to which then edits it and runs it against the SQL database (using a context connection which is even faster than shared memory).
Beware though that the SQL server is pretty well written, something like your trying to achieve would probably best be achieved at the client using an API so that the processing is spread by the number of clients and allowing the SQL server to optimise the actual SQL being run.
When you say "URL Rewriting" do you really mean "URL"? Because I wasn't aware that SQL Server was ever accessed by URL.
If you meant that as an analogy, could you please clarify?
You can write triggers that employ .net classes.
http://www.15seconds.com/issue/041006.htm
http://aspalliance.com/1273_CLR_Triggers_for_SQL_Server_2005.all
http://www.google.com/search?rlz=1C1_____enUS397US397&sourceid=chrome&ie=UTF-8&q=sql+.net+trigger
good luck.
