|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
User-defined Select listquery 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. Thanks in advance! You may be best served looking at dynamic SQL execution. Check out the
following: http://www.sommarskog.se/dynamic_sql.html -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous <lord.zol***@gmail.com> wrote in message news:1155839768.124795.231270@75g2000cwc.googlegroups.com... >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. > > Thanks in advance! > Arnie Rowland wrote:
> You may be best served looking at dynamic SQL execution. Check out the Yeah, that helped. Thanks!> following: > > http://www.sommarskog.se/dynamic_sql.html > > >> 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 tobe done? --CELKO-- wrote:
> >> 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). << I started off with something like that> > Why not filter it in the front end, where display work is supposed to > be done? 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. lord.zol***@gmail.com wrote:
Show quote > --CELKO-- wrote: A table with 80+ columns, where certain columns are useful to specific >>>> 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. > types of users (accountant, manager, etc) sounds like a design problem. Seems like you should be able to normalize this thing a little better, maybe move the "accountant" fields into their own table, the "manager" fields into a table, etc... Without knowing DDL, it's hard to be more specific. You're going to likely have performance problems with a table this wide, because it's going to be nearly impossible to create indexes that will support all combinations of fields that a user might select. I know these don't answer your original question, but I think you should step back and think about this design. >> 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 medicalresearch 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 betables 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 toget 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. > Why not filter it in the front end, where display work is supposed to The principal of client/server is to process where it is most efficient to > be done? do so. It is far better to minimise the amount of data sent back to clients otherwise you run into scalability problems really quickly. Consider the OP - your suggest would be to push down 80 columns to the client, what if that client is over a slow link or if there where a a couple of hundred clients pulling the data off - do you not think that there would be a resource problem or do you think something magical is happening? Network bandwidth is finite. -- Show quoteTony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1155843952.280882.122290@m79g2000cwm.googlegroups.com... >>> 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? >  |
|||||||||||||||||||||||
Other interesting topics