create proc test @table varchar(100),@条件1 varchar(8000),@条件2 varchar(8000),@条件3 varchar(8000) as declare @sql varchar(2000) set @sql = 'select * from ' + @table + ' where 1 = 1 ' exec(@sql + isnull(@条件1,'') + ' '+isnull(@条件2,'') + ' ' +isnull(@条件3,'')) go --调用 exec test 'tablename','and col1 > 0','and col2 > 0','and col3 > 0' @条件写成: and ......
--组合条件嘛.程序中写(VB为例): dim sql$ sql=iif(text1="",""," and 项目单编号='" & text1 & "'") sql=sql & iif(text2="",""," and 材料名称='" & text2 & "'") sql=sql & iif(combo1="",""," and 单位='" & combo1 & "'") sql=mid(sql,5) rs.open sql
--或者用存储过程:create proc p_qry @项目编号 varchar(10), @材料名称 varchar(100), @单位 varchar(10) as declare @sql varchar(8000) set @sql=case isnull(@项目编号,'') when '' then '' else ' and 项目编号='''+@项目编号+'''' end +case isnull(@项目编号,'') when '' then '' else ' and 项目编号='''+@项目编号+'''' end +case isnull(@项目编号,'') when '' then '' else ' and 项目编号='''+@项目编号+'''' end if @sql<>'' set @sql='where '+substring(@sql,5,8000) exec('select * from 表 '+@sql) go
前台处理(VB): dim strSQL as stirng dim strWhere as stringstrSQL="select * from table1 " if a<>"" then strWhere=strWhere & "and fieldA='" & a & "'" if b<>"" then strWhere=strWhere & "and fieldB='" & b & "'" if c<>"" then strWhere=strWhere & "and fieldC='" & c & "'"if strWhere<>"" then strWhere=mid(strWhere ,4) strSQL=strSQL & " where " & strWhere end ifrs.open strSQL '当然也可以做成类似的存储过程。
其实你可以用通配符,我感觉 select .... from .. where 字段1 like '%a.value%' and 字段2 like '%b.value%' and 字段3 like '%c.value%',如果为空,就是全选
as
declare @sql varchar(2000)
set @sql = 'select * from ' + @table + ' where 1 = 1 '
exec(@sql + isnull(@条件1,'') + ' '+isnull(@条件2,'') + ' ' +isnull(@条件3,''))
go
--调用
exec test 'tablename','and col1 > 0','and col2 > 0','and col3 > 0'
@条件写成: and ......
dim sql$
sql=iif(text1="",""," and 项目单编号='" & text1 & "'")
sql=sql & iif(text2="",""," and 材料名称='" & text2 & "'")
sql=sql & iif(combo1="",""," and 单位='" & combo1 & "'")
sql=mid(sql,5)
rs.open sql
@项目编号 varchar(10),
@材料名称 varchar(100),
@单位 varchar(10)
as
declare @sql varchar(8000)
set @sql=case isnull(@项目编号,'') when '' then '' else ' and 项目编号='''+@项目编号+'''' end
+case isnull(@项目编号,'') when '' then '' else ' and 项目编号='''+@项目编号+'''' end
+case isnull(@项目编号,'') when '' then '' else ' and 项目编号='''+@项目编号+'''' end
if @sql<>'' set @sql='where '+substring(@sql,5,8000)
exec('select * from 表 '+@sql)
go
dim strSQL as stirng
dim strWhere as stringstrSQL="select * from table1 "
if a<>"" then strWhere=strWhere & "and fieldA='" & a & "'"
if b<>"" then strWhere=strWhere & "and fieldB='" & b & "'"
if c<>"" then strWhere=strWhere & "and fieldC='" & c & "'"if strWhere<>"" then
strWhere=mid(strWhere ,4)
strSQL=strSQL & " where " & strWhere
end ifrs.open strSQL
'当然也可以做成类似的存储过程。
select .... from .. where 字段1 like '%a.value%' and 字段2 like '%b.value%' and 字段3 like '%c.value%',如果为空,就是全选