--你试试,看看这个可以吗?create proc dbo.p_tb
@a varchar(100)
@b varchar(100)
as
declare @str_wh varchar(100)
set @str_wh=''
if @a is null
begin
set @str_wh=@str_wh+''
if @b is null
set @str_wh=str_wh+''
else
set @str_wh=@str_wh+'and bbb='''+@b+''''
end
else
set @str_wh=@str_wh+'and aaa='''+@a+''''
if @str_wh<>''
set @str_wh=right(@str_wh,len(@str_wh)-3)
exec('select* from tabel where'+@str_wh)
go
@a varchar(100)
@b varchar(100)
as
declare @str_wh varchar(100)
set @str_wh=''
if @a is null
begin
set @str_wh=@str_wh+''
if @b is null
set @str_wh=str_wh+''
else
set @str_wh=@str_wh+'and bbb='''+@b+''''
end
else
set @str_wh=@str_wh+'and aaa='''+@a+''''
if @str_wh<>''
set @str_wh=right(@str_wh,len(@str_wh)-3)
exec('select* from tabel where'+@str_wh)
go
@a varchar(50),
@b varchar(50)
as
if (isnull(@a,0)=0 and isnull(@b,0)<>0)
select* from tabel where bbb=@b
else if (isnull(@a,0)<>0 and isnull(@b,0)=0)
select* from tabel where aaa=@a
else if (isnull(@a,0)<>0 and isnull(@b,0)<>0)
select* from tabel where aaa=@a and bbb=@b
else if (isnull(@a,0)=0 and isnull(@b,0)=0)
select* from tabel
go
@a varchar(50),
@b varchar(50)
as
if (isnull(@a,0)=0 and isnull(@b,0)<>0)
select * from tabel where bbb=@b
else if (isnull(@a,0)<>0 and isnull(@b,0)=0)
select * from tabel where aaa=@a
else if (isnull(@a,0)<>0 and isnull(@b,0)<>0)
select * from tabel where aaa=@a and bbb=@b
else if (isnull(@a,0)=0 and isnull(@b,0)=0)
select * from tabel
go
exec proc_aaa @a='大学本科',@b=''
服务器: 消息 245,级别 16,状态 1,过程 proc_aaa,行 5
将 varchar 值 '大学本科' 转换为数据类型为 int 的列时发生语法错误。两个字段都是nvarchar 10的
???
@a varchar(10),
@b varchar(10)
as
if @a='' and @b<>''
select* from person where zye=@b
else if @a<>'' and @b=''
select* from person where edu=@a
else if @a<>'' and @b<>''
select* from person where edu=@a and zye=@b
else if @a='' and @b=''
select* from person
GO搞定!