User-defined Select list

SQL Apprentice Question
I have a stored procedure that selects from a table, the details of the
query or table aren't that important (it's quite simple). Right now,
the columns it selects are specified explicitly. I'd like for the user
to be able to specify which columns they want to select (There are 2 or
3 columns that must always be selected, but the user won't have the
option to deselect them).

What I'd like to do is something like this:


"CREATE PROCEDURE testProcedure
@selectList
AS
SELECT @selectList FROM SomeTable....
...
GO


--calling it
EXEC testProcedure @selectList='SomeTableID, Column1, Column2'"


...Where @selectList is a list of whatever columns the user wants to
see, and passed to the stored procedure from the program. I don't know
if this is possible, or how to even start searching for a solution.



Celko Answers
>> I'd like for the user to be able to specify which columns they want to select (There are 2 or 3 columns that must always be selected, but the user won't have the option to deselect them). <<


Why not filter it in the front end, where display work is supposed to
be done?



SQL Apprentice Question
> >> I'd like for the user to be able to specify which columns they want to select (There are 2 or 3 columns that must always be selected, but the user won't have the option to deselect them). <<

> Why not filter it in the front end, where display work is supposed to
> be done?



I started off with something like that

The table in question has just over 80 columns (depending on how the
next meeting with the client goes, this could increase, on their whim).
The way they want the interface to work, it could retreive between a
couple of hundred and a couple of thousand rows from this table,
depending on what options they select. I found that when they ran their
largest searches (got back a few thousand rows, and by they way I'm
working with a small test set of data, not the FULL set), things just
took too long because it was getting back data for all 80 columns
(well, about 60 at the time, it's grown).


Earlier, I tried to create a number of views: A view for the front
desk, a view for the accountant, a view for the manager, a view for the
recruite, etc... so that someone logs in as accountant and only sees
the data that they NEED to see. The client was insistent that all users
be able to choose from any to all (though I think "all" would be pretty
rare) columns (I think part of the problem is the way the run their
business - everyone can do everyone else's job and often DOES - but
there's not much I can do about that ;) ).


Someone at work suggested a FlexGrid control. For the time to implement
it and get it working, it didn't seem worth the effort.


Basically, any user must be able to select any columns from the large
table. I decided to go with this dynamic SQL because it's simple and it
seems to run nice and quickly.
I am by no measure an SQL expert, so if you have another suggestion for
this sort of problem, I'm open to it.



Celko Answers

>> The table in question has just over 80 columns (depending on how the next meeting with the client goes, this could increase, on their whim). <<


The only tables I have seen with that kind of width are for medical
research where they conduct 100's of tests on each subject. And whim
is a poor way to design a system.


>> Earlier, I tried to create a number of views: A view for the front desk, a view for the accountant, a view for the manager, a view for the recruiter, etc... so that someone logs in as accountant and only sees the data that they NEED to see. <<


Without any further information, I would guess that these should be
tables in their own right. People with a spreadsheet background tend
to think this way.


>> The client was insistent that all users be able to choose from any to all (though I think "all" would be pretty rare) columns (I think part of the problem is the way the run their business - everyone can do everyone else's job and often DOES - but there's not much I can do about that ;) ). <<


Does his auditor know about this practice? What you can do is try to
get some legal protection on yourself when the audits come. Get them
to sign off on the design and note that you gave them constructive
knowledge.

Decades ago, I was volunteered by my wife to work on a UNIX/SQL box at
a New Age/Hippie Co-op. The guy that set up their inventory system was
the worst programmer I had ever met. He was so proud that "It's an SQL
database and anyone can change anything any way they want!" So anyone
DID change things; actuially, it was more like EVERYONE changed things.
When they went out of business in a few months, the inventory was a
total mess. They showed 5000 cases of hot sauce from a commune in
California that had never made that much product, etc.