使用动态SQL,根据条件组成查询语句
create proc query_test
@a varchar(200)='1=1',
@b varchar(200)='1=1',
@c varchar(200)='1=1',
@d varchar(200)='1=1',
@e varchar(200)='1=1',
@f varchar(200)='1=1'
asdeclare @sql varchar(8000)
set @sql='select * from test where ' + @a + ' and '+ @b + ' and ' + @c + ' and ' + @d + ' and ' + @e + ' and ' + @fexec (@sql)
调用:1、exec query_test 'a=1','b=''2''','c>3','d<8','e like ''e%''','f>''2003-1-1'''
2、exec query_test 'a=1','b=''2''','c>3','d<8','e like ''e%'''
3、exec query_test 'a=1'
4、exec query_test ---get all
create proc query_test
@a varchar(200)='1=1',
@b varchar(200)='1=1',
@c varchar(200)='1=1',
@d varchar(200)='1=1',
@e varchar(200)='1=1',
@f varchar(200)='1=1'
asdeclare @sql varchar(8000)
set @sql='select * from test where ' + @a + ' and '+ @b + ' and ' + @c + ' and ' + @d + ' and ' + @e + ' and ' + @fexec (@sql)
调用:1、exec query_test 'a=1','b=''2''','c>3','d<8','e like ''e%''','f>''2003-1-1'''
2、exec query_test 'a=1','b=''2''','c>3','d<8','e like ''e%'''
3、exec query_test 'a=1'
4、exec query_test ---get all
create proc query_test
@a varchar(200)='1=1',
@b varchar(200)='1=1',
@c varchar(200)='1=1',
@d varchar(200)='1=1',
@e varchar(200)='1=1',
@f varchar(200)='1=1'
asdeclare @sql varchar(8000)
set @sql='select * from test where ' + @a + ' and '+ @b + ' and ' + @c + ' and ' + @d + ' and ' + @e + ' and ' + @fexec (@sql)
调用:1、exec query_test 'a=1','b=''2''','c>3','d<8','e like ''e%''','f>''2003-1-1'''
2、exec query_test 'a=1','b=''2''','c>3','d<8','e like ''e%'''
3、exec query_test 'a=1'
4、exec query_test ---get all