declare @V1 int, @V2 intselect * from table where ( @V1 is null or V1 = @V1 ) or ( @V2 is null or V2 = @V2 )------------------------------- 明白?
考虑到性能问题,一般使用命令参数,特别是存储过程实现的查询,这样数据库引擎可以根据SQL语句是否变化,而不需要重新编译SQL语句-- SQL Sample SELECT Field1, Field2, ... FROM tbl WHERE (@Param1 IS NULL OR Field1 = @Param1) AND (@Param2 IS NULL OR Field2 = @Param1) OR ... AND ...实在过于动态,还是拼接 SQL 吧 -- code snippets string whereClause = "WHERE 1=1"; whereClause += TextBox1.Text == "" ? "" : " AND Field1 = " + TextBox1.Text; whereClause += TextBox2.Text == "" ? "" : " AND Field2 LIKE %" + TextBox2.Text + "%"; whereClause += CheckBox1.Checked == "" ? "" : " AND Field3 = SomeValue"; // ....
In client, you can use javascript or asp.net validation to confine the page. In server, it's better to use stored procedure to communicate with database, at least use sql parameter to avoid common sql injection attack.It's very dangerous to append strings together for the hackers can add some snippet like ';delete from user' into the sql statement.Good luck!
string sqlwhere = "";
if(aaa.text!="")
sqlwhere += " xxx = '" + aaa.text + "' and";
if(bbb.text!="")
sqlwhere += " ccc = '" + bbb.text + "' and";..
if(sqlwhere!="")
//所sqlwhere最后3个字符去掉sql += " where "+ sqlwhere;
( @V1 is null or V1 = @V1 ) or
( @V2 is null or V2 = @V2 )-------------------------------
明白?
SELECT Field1, Field2, ...
FROM tbl
WHERE (@Param1 IS NULL OR Field1 = @Param1)
AND (@Param2 IS NULL OR Field2 = @Param1)
OR ... AND ...实在过于动态,还是拼接 SQL 吧
-- code snippets
string whereClause = "WHERE 1=1";
whereClause += TextBox1.Text == "" ? "" : " AND Field1 = " + TextBox1.Text;
whereClause += TextBox2.Text == "" ? "" : " AND Field2 LIKE %" + TextBox2.Text + "%";
whereClause += CheckBox1.Checked == "" ? "" : " AND Field3 = SomeValue";
// ....
In server, it's better to use stored procedure to communicate with database, at least use sql parameter to avoid common sql injection attack.It's very dangerous to append strings together for the hackers can add some snippet like ';delete from user' into the sql statement.Good luck!