p*********a
发帖数: 61 | 1 My application has a form to collect users' inputs, which are passed to a
parameterized SQL query over a table. When a user skips some boxes in the
form, the query should have no predicates on the corresponding attributes.
An extreme case is if the user inputs nothing, the query should return the
entire table.
My question is if the parameterized query is prefixed, what values are
supposed to be used for the parameters whose boxes are empty?
I understand that there are some solutions to work around. For example, for
string fileds, I can use LIKE '@para' to process. @para is % when the box is
empty; otherwise, @para is what the user inputs. However, my concern is
that the processing of LIKE operation is slow, as the evaluation of LIKE '%'
would scan the table to match.
Are there any other solutions? or my concern is unnecessary? or should skip
the parameterized query?
many thanks, | B*****g
发帖数: 34098 | 2 1. 每次根据输入用不同的SQL
2. (@para IS NULL OR col = @para)
for
is
%'
skip
【在 p*********a 的大作中提到】
: My application has a form to collect users' inputs, which are passed to a
: parameterized SQL query over a table. When a user skips some boxes in the
: form, the query should have no predicates on the corresponding attributes.
: An extreme case is if the user inputs nothing, the query should return the
: entire table.
: My question is if the parameterized query is prefixed, what values are
: supposed to be used for the parameters whose boxes are empty?
: I understand that there are some solutions to work around. For example, for
: string fileds, I can use LIKE '@para' to process. @para is % when the box is
: empty; otherwise, @para is what the user inputs. However, my concern is
| a9
发帖数: 21638 | 3 .net?
DBValue.Null
for
is
%'
skip
【在 p*********a 的大作中提到】
: My application has a form to collect users' inputs, which are passed to a
: parameterized SQL query over a table. When a user skips some boxes in the
: form, the query should have no predicates on the corresponding attributes.
: An extreme case is if the user inputs nothing, the query should return the
: entire table.
: My question is if the parameterized query is prefixed, what values are
: supposed to be used for the parameters whose boxes are empty?
: I understand that there are some solutions to work around. For example, for
: string fileds, I can use LIKE '@para' to process. @para is % when the box is
: empty; otherwise, @para is what the user inputs. However, my concern is
| i****a
发帖数: 36252 | 4 you can also use CASE in the WHERE clause
for
is
【在 p*********a 的大作中提到】
: My application has a form to collect users' inputs, which are passed to a
: parameterized SQL query over a table. When a user skips some boxes in the
: form, the query should have no predicates on the corresponding attributes.
: An extreme case is if the user inputs nothing, the query should return the
: entire table.
: My question is if the parameterized query is prefixed, what values are
: supposed to be used for the parameters whose boxes are empty?
: I understand that there are some solutions to work around. For example, for
: string fileds, I can use LIKE '@para' to process. @para is % when the box is
: empty; otherwise, @para is what the user inputs. However, my concern is
| g***l
发帖数: 18555 | 5 STORED PROCEDURE不就是专门HANDLE这个的吗？ | L*******r
发帖数: 8961 | 6 这个很同意。
【在 g***l 的大作中提到】
: STORED PROCEDURE不就是专门HANDLE这个的吗？
|
|
