create proc return_tab (@id varchar(100)=null,@user varchar(100)=null,@phone varchar(200)=null,@address varchar(1000)=null)
as
begin
select @id=isnull( 'and id like ''%'+@id+'%'' ',' ')
select @user=isnull( 'and [user] like ''%'+@user+'%'' ',' ')
select @phone=isnull( 'and phone like ''%'+@phone+'%'' ',' ')
select @address=isnull( 'and [address] like ''%'+@address+'%'' ',' ') declare @str varchar(1000)
select @str=''
select @str='select * from tab where '+stuff(@id+@user+@phone+@address,1,charindex('and',@id+@user+@phone+@address)+2,'')
exec(@str)
endexec return_tab @id='1',@phone='1213654'
看了小虫的才知道楼主的意思:
create proc return_tab
id varchar(100)=null,
@user varchar(100)=null,
@phone varchar(200)=null,
@address varchar(1000)=null
as
begin
declare @where varchar(1000)
set @where =isnull(' and id like ''%'+@id+'%''',' ')
set @where =@where + isnull( ' and [user] like ''%'+@user+'%'' ',' ')
set @where =@where + isnull( ' and phone like ''%'+@phone+'%'' ',' ')
set @where =@where + isnull( ' and [address] like ''%'+@address+'%'' ',' ')
exec('select * from 表名 where 1=1 '+ @where)
end
掉了一个@