如何形成动态的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())

------解决方案--------------------