Mombu the Microsoft Forum sponsored links

Go Back   Mombu the Microsoft Forum > Microsoft > SQL SERVER (TECHNET) > filtering data
User Name
Password
REGISTER NOW! Mark Forums Read

sponsored links


Reply
 
1 25th July 07:27
malgorzata ndreu
External User
 
Posts: 1
Default filtering data


Hi, all
I have one big table with 190 columns (!), there are user's data stored in
that table.
Now, there is a form on which i can select criteria (around 30) for
filtering users, not all criteria must be selected, so never know how many
and what criterions are chosen.
What would be the best way to implement the selection of users from my
table?
We have some ideas:
1) a.create one temporary table and insert there all users
b.for every criterion available from application form create one stored
procedure which would choose users not satisfying that criterion and remove
them from temp table, the procedure would be run only if criterion is
selected on the form
but this idea has some bad points: the table should not probably has an
index it would be slow to find records in such table and the problem with
defragmentation would appear here probably
2) create a procedure which would generate dinamically a where clause
depends on selected criteria passed to the procedure as parameters
but what happens if dinamically created statement exxceeds 8000 characters?

And which one would be faster?
Please advise, or maybe somebody would have some other ideas how this could
be done
mndr
  Reply With Quote


  sponsored links


2 25th July 07:28
malgorzata ndreu
External User
 
Posts: 1
Default filtering data


Hi, all
I have one big table with 190 columns (!), there are user's data stored in
that table.
Now, there is a form on which i can select criteria (around 30) for
filtering users, not all criteria must be selected, so never know how many
and what criterions are chosen.
What would be the best way to implement the selection of users from my
table?
We have some ideas:
1) a.create one temporary table and insert there all users
b.for every criterion available from application form create one stored
procedure which would choose users not satisfying that criterion and remove
them from temp table, the procedure would be run only if criterion is
selected on the form
but this idea has some bad points: the table should not probably has an
index it would be slow to find records in such table and the problem with
defragmentation would appear here probably
2) create a procedure which would generate dinamically a where clause
depends on selected criteria passed to the procedure as parameters
but what happens if dinamically created statement exxceeds 8000 characters?

And which one would be faster?
Please advise, or maybe somebody would have some other ideas how this could
be done
mndr
  Reply With Quote
3 25th July 07:30
david portas
External User
 
Posts: 1
Default filtering data


http://www.sommarskog.se/dyn-search.html

"one big table with 190 columns" sounds suspiciously like the design is in
need of a review and some normalization.

--
David Portas
SQL Server MVP
--
  Reply With Quote
4 25th July 07:31
malgorzata ndreu
External User
 
Posts: 1
Default filtering data


I agree 100 percent
thank you so much for your response
mndr
  Reply With Quote


  sponsored links


Reply


Thread Tools
Display Modes




Copyright 2006 SmartyDevil.com - Dies Mies Jeschet Boenedoesef Douvema Enitemaus -
666