--一般这种写法用于动态条件的SQL语句中--示例(要求@name,@sex不输入的话,则不加入条件中)
create proc p_qry
@name varchar(10)='',
@sex varchar(10)=''
as
declare @tj varchar(8000)
set @tj=case @name when '' then '' else ' and name='''+@name+'''' end
+case @sex when '' then '' else ' and sex='''+@sex+'''' end
exec('select * from 表 where 1=1 '+@tj)
go
create proc p_qry
@name varchar(10)='',
@sex varchar(10)=''
as
declare @tj varchar(8000)
set @tj=case @name when '' then '' else ' and name='''+@name+'''' end
+case @sex when '' then '' else ' and sex='''+@sex+'''' end
exec('select * from 表 where 1=1 '+@tj)
go
create proc p_qry
@name varchar(10)='',
@sex varchar(10)=''
as
declare @tj varchar(8000)
set @tj=case @name when '' then '' else ' and name='''+@name+'''' end
+case @sex when '' then '' else ' and sex='''+@sex+'''' end
print('select * from 表 where 1=1 '+@tj)
go--调用
exec p_qry --结果: select * from 表 where 1=1
exec p_qry 'a'--结果:select * from 表 where 1=1 and name='a'
exec p_qry '','b'--结果:select * from 表 where 1=1 and sex='b'
exec p_qry 'a','b'--结果:select * from 表 where 1=1 and name='a' and sex='b'
go--删除测试
drop proc p_qry
create procedure test
@uName varchar(100) ,
@uEmail varchar(100),
@uAddress varchar(100)
as
declare @sqlstr varchar(8000)
set @sqlstr='select * from mytable where 1=1'
if @uName<>''
set @sqlstr=@sqlstr + ' and uName='''+@uName+''''
if @uEmail<>''
set @sqlstr=@sqlstr + ' and uEmail='''+@uEmail+''''
if @uAddress<>''
set @sqlstr=@sqlstr + ' and uAddress='''+@uAddress+''''
exec (@sqlstr)