--示例 create proc p_qry @uName varchar(10)='', @uEmail varchar(10)='', @uAddress varchar(10)='' as declare @tj varchar(8000) select @tj =case isnull(@uName,'') when '' then '' else ' and uName='''+@uName+'''' end +case isnull(@uEmail,'') when '' then '' else ' and uEmail='''+@uEmail+'''' end +case isnull(@uAddress,'') when '' then '' else ' and uAddress='''+@uAddress+'''' end ,@tj=case @tj when '' then '' else ' where '+@tj end exec('select * from 表 '+@tj) go
与一般查询没有什么特别的啊.写成EXEC()好了
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)
--示例
create proc p_qry
@uName varchar(10)='',
@uEmail varchar(10)='',
@uAddress varchar(10)=''
as
declare @tj varchar(8000)
select @tj
=case isnull(@uName,'') when '' then ''
else ' and uName='''+@uName+'''' end
+case isnull(@uEmail,'') when '' then ''
else ' and uEmail='''+@uEmail+'''' end
+case isnull(@uAddress,'') when '' then ''
else ' and uAddress='''+@uAddress+'''' end
,@tj=case @tj when '' then '' else ' where '+@tj end
exec('select * from 表 '+@tj)
go
@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)