比如窗体提供txtA, txtB, txtC, txtD输入框(对应表字段fieldA, fieldB, fieldC, fieldD)，[email protected]， @B， @C， @D传递给存储过程，如何在存储过程中形成动态的WHERE条件，[email protected]，@C不为空，@[email protected]，则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 [email protected])
and (@b is null or [email protected])
and (@c is null or [email protected])
and (@d is null or [email protected])
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())
------解决方案--------------------