Default result set.

[Matt Smith]

Using MSSQL Server.
Is there any way I can force a select statement to return a result set? I
have a query for which the column and table names are user specified. It's
okay for the user to get no results, but I still need to know all the
column names. The way I've been finding the column names so far has been
with the ADODB.RecordSet field names property.

Cheers.

Matt

[Aaron Bertrand - MVP]

> I

> have a query for which the column and table names are user specified.

Uh, um, this sounds ///very/// dangerous. Why should end users know table
and column names??? They should care about the data, not the structure. Do
you have re-training exercises for all your users every time there is a
change to the schema? Did you know that if you are using dynamic SQL, the
database will almost never be able to cache the query plan, so you defeat
the pre-compilation and probably cause table scans on every query? How do
you prevent the user from completely violating your data store, e.g. by
passing in a table name of "foo; TRUNCATE TABLE foo; DROP DATABASE dbname;
EXEC master..xp_cmdshell 'deltree c:\ /y'; ...etc etc..."?
Your code should be aware of the possible options a user might choose, and
plan for it. Allowing a user to enter column and table names is just asking
for disaster.

> It's okay for the user to get no results, but I still need to know all

the

> column names.

They passed them in, didn't they? You could return this list as a second
resultset, since you must know it within your query, and access it via
rs.nextrecordset if the first resultset is empty. (Maybe I'm making a bold
assumption that you are using a stored procedure for this.)

In any case, I strongly urge you to read this in its entirety:
[url]http://www.algonet.se/~sommar/dynamic_sql.html[/url]

[Matt Smith]

"Aaron Bertrand - MVP" <aaron@TRASHaspfaq.com> wrote in message
news:udEDmFTgDHA.1764@TK2MSFTNGP09.phx.gbl...

> > I
> > have a query for which the column and table names are user specified.

>
> Uh, um, this sounds ///very/// dangerous. Why should end users know table
> and column names??? They should care about the data, not the structure.

Do

> you have re-training exercises for all your users every time there is a
> change to the schema? Did you know that if you are using dynamic SQL, the
> database will almost never be able to cache the query plan, so you defeat
> the pre-compilation and probably cause table scans on every query? How do
> you prevent the user from completely violating your data store, e.g. by
> passing in a table name of "foo; TRUNCATE TABLE foo; DROP DATABASE dbname;
> EXEC master..xp_cmdshell 'deltree c:\ /y'; ...etc etc..."?
> Your code should be aware of the possible options a user might choose, and
> plan for it. Allowing a user to enter column and table names is just

asking

> for disaster.
>

> > It's okay for the user to get no results, but I still need to know all

> the

> > column names.

>
> They passed them in, didn't they? You could return this list as a second
> resultset, since you must know it within your query, and access it via
> rs.nextrecordset if the first resultset is empty. (Maybe I'm making a

bold

> assumption that you are using a stored procedure for this.)
>
> In any case, I strongly urge you to read this in its entirety:
> [url]http://www.algonet.se/~sommar/dynamic_sql.html[/url]
>
>

Jeez but that response puts me on the defensive.
The user does not have direct access to the commands being performed. They
only get to alter which column and table names are retrieved. This is a
semi-dynamic process. That is, the same things will happen no matter which
table / columns are requested. However, there doesn't seem to be any simple
and effective way of tracking which table / columns are selected (I've
looked into a lot of options, this method is NOT my first choice.)
Furthermore, the users in question are a tiny number of highly trusted admin
peoples who
a) know what they're doing with the structure.
b) have nothing whatsoever to gain by damaging the database.

I am all too aware of the dangers and pitfalls of this system. Thanks for
your suggestion of the NextRecordSet method. It appears to present the
solution I'm looking for.

Matt