如何形成动态的WHERE条件
比如窗体提供txtA, txtB, txtC, txtD输入框(对应表字段fieldA, fieldB, fieldC, fieldD)，根据输入内容形成参数@A， @B， @C， @D传递给存储过程，如何在存储过程中形成动态的WHERE条件，如@A，@C不为空，@B、@D为空，则WHERE条件为： WHERE fieldA = @A AND fieldC = @C
[解决办法]
如:
create proc sp_test (@v1 int,@v2 int)
as
declare @sql varchar(1000)
set @sql = 'select * from 表名 where 1=1 '
if @v1 is not null
set @sql = @sql + ' and 字段1= ' + cast(@v1 as varchar(100))
if @v2 is not null
set @sql = @sql + ' and 字段2= ' + cast(@v2 as varchar(100))
exec(@sql)
go
[解决办法]
--假設全部是字符型
Declare @S Varchar(8000)
Select @S = 'Select * From 表 Where 1= 1 '
If(IsNull(@A, ' ') != ' ')
Select @S = @S + ' fieldA = ' ' ' + @A + ' ' ' '
If(IsNull(@B, ' ') != ' ')
Select @S = @S + ' fieldB = ' ' ' + @B + ' ' ' '
If(IsNull(@C, ' ') != ' ')
Select @S = @S + ' fieldC = ' ' ' + @C + ' ' ' '
If(IsNull(@D, ' ') != ' ')
Select @S = @S + ' fieldD = ' ' ' + @D + ' ' ' '
EXEC(@S)
[解决办法]
不用动态sql,直接这样写
select * from tab
WHERE fieldA = isnull(@A,fieldA )
AND fieldB = isnull(@B,fieldB )
AND fieldC = isnull(@C,fieldC )
AND fieldD = isnull(@D,fieldD )
[解决办法]
--如果一定要用一个SQL处理，可以如下处理：
create proc sp_test (@a int,@b int,@c int,@d int)
as
select *
from 表
where (@a is null or 字段1=@a)
and (@b is null or 字段2=@b)
and (@c is null or 字段3=@c)
and (@d is null or 字都4=@d)
go
[解决办法]
我用case when then else end 感觉很爽
WHERE fieldA =(case when @A= ' ' then @a else fieldA end) AND fieldC = (case when @C= ' ' then @c else fieldC end)...其它类
[解决办法]
select * from tab
WHERE fieldA = isnull(@A,fieldA )
AND fieldB = isnull(@B,fieldB )
AND fieldC = isnull(@C,fieldC )
AND fieldD = isnull(@D,fieldD )
这个方法看起来更加简洁。。。。。。。。。
[解决办法]
学习mengmou()mengmou()
SELECT @EndValue = ISNULL(@EndValue, GetDate())
SELECT fieldA, fieldB, fieldC, fieldD
FROM tableA
WHERE fieldA = isnull(@A,fieldA )
AND fieldB = isnull(@B,fieldB )
AND fieldC = isnull(@C,fieldC )
AND fieldD BETWEEN ISNULL(@BeginValue, '2007-01-01 ') AND ISNULL(@EndValue,GetDate())
[解决办法]
刚才多复制了一行
学习mengmou()mengmou():
SELECT fieldA, fieldB, fieldC, fieldD
FROM tableA
WHERE fieldA = isnull(@A,fieldA )
AND fieldB = isnull(@B,fieldB )
AND fieldC = isnull(@C,fieldC )
AND fieldD BETWEEN ISNULL(@BeginValue, '2007-01-01 ') AND ISNULL(@EndValue,GetDate())
------解决方案--------------------
--直接这样写就可以了
SELECT @BeginValue = ISNULL(@BeginValue, '1900-01-01 ')
SELECT @EndValue = ISNULL(@EndValue, GetDate())
SELECT fieldA, fieldB, fieldC, fieldD
FROM tableA
WHERE fieldA = isnull(@A,fieldA )
AND fieldB = isnull(@B,fieldB )
AND fieldC = isnull(@C,fieldC )
AND fieldD BETWEEN @BeginValue AND @EndValue
[解决办法]
不用动态sql,直接这样写
select * from tab
WHERE fieldA = isnull(@A,fieldA )
AND fieldB = isnull(@B,fieldB )
AND fieldC = isnull(@C,fieldC )
AND fieldD = isnull(@D,fieldD )
------------------
这种代码太好了，学习中......
