SQL & ASP: custom queries for the user (a theoretical post)

Tweet

This post was conceived as a theoretical experiment on dynamic queries. The original idea was to let the user create custom SQL queries against a database.
 The whole thing is not so crazy, and it kept forming in my mind while I was thinking about it.

Imagine a situation where a user can select a table from the list of tables in a SQL Server database. That is not a problem; infact we can retrieve table names with:

SELECT name
FROM sys.Tables

After that, the user can select the columns in the chosen table from which to perform the query. We can list the columns of a table with:

SELECT name
FROM syscolumns
WHERE [id] = OBJECT_ID('tablename')

where tablename is the name of the chosen table.

At this point, the user should be able to add WHERE clauses. Again that is not really a great problem: the user could be able to insert the data directly or in some way be guided through the process.

Now, if you imagine all the above ideas built inside a structured form on a web page, I believe that it would be quite a piece of cake.

The query will be built and store, and then used in a results page.

The things can get harder, when we need to allow the user to build complex queries like union queries, or joint queries.
The possibility of adding more than one table could be easily solvable, however the idea of developing a complete query project manager could become complicated.

As said, I started thinking about the above before falling asleep a few nights ago. I don't really have the necessity to create such a solution at the moment, but it could be great if you all share your ideas and experience here on the web thought.
The comments section below is your place.