ANSI Sql query to force return 0 records

I'm looking for an ANSI-SQL method to do a Select query without returning any record, but fill a TDataSet's Fields structure.

The method I found is by adding a "where 1=0" in any query, for example:

Select Id, name, province
from customers
where 1=0

This is a fairly trivial example, it turns a little more complicated when I have to work with queries ent开发者_运维知识库ered by the user, then parse them, remove the where clause if it already has one, and replace by "1=0".

If the last clause in the user-entered query is the where clause, then there's no problem at all, but what about more complicated queries like this:

select
  c.lastname,
  sum(cs.amount)
from customersales cs
join customers c on c.idcustomer=cs.idcustomer
/* where 1=0 */
group by c.idcustomer, c.lastname

By using the "where 1=0" method, the only way to insert it in the previous example is by having a rather powerful SQL parser (remember the user can enter complex queries, including Subqueries, and all that), who can understand where to include this string.

Does anyone knows a better way to do this? I cannot use "limit 1" because it must be in an ANSI way.

What about adding your own SELECT around the user's SELECT?

SELECT * FROM (
select
  c.lastname,
  sum(cs.amount)
from customersales cs
join customers c on c.idcustomer=cs.idcustomer
/* where 1=0 */
group by c.idcustomer, c.lastname
) x
WHERE 0=1

EDIT: ORDER BY would not work with that solution, but since you get no rows, you could try to remove that from the query when necessary.

For future reference in case people end up here with a different goal: Note that making the WHERE-clause a contradiction can cause the optimizer to decide to not execute the sub-plan at all. So if you need some side-effects of the query (be it warm a cache, execute a procedure, whatever), be advised. :-)

if your using MSSQL Server, then you can wrap your query around SET FMTONLY

SET FMTONLY ON SELECT * FROM tablename SET FMTONLY OFF

In Firebird you may 'prepare' the statement instead of 'execute' it. Preparing simply parses the statement and returns the field list.

Or use

CustomerSQL='SELECT <Fields> FROM <Table>';
MySQL=Replace(CustomerSQL,'SELECT ','SELECT TOP 0 ');

(perhaps with some sanity checking, but you get the idea - a SELECT TOP 0 will return only the meta data containing the record layout and no record data).