不动态SQL,用ISNULL select * from table where f1=isnull(@m1,f1) and f2=isnull(@m1,f2) and f3=isnull(@m1,f3) and f4=isnull(@m1,f4) and f5=isnull(@m1,f5)
上面错了 select * from table where f1=isnull(@m1,f1) and f2=isnull(@m2,f2) and f3=isnull(@m3,f3) and f4=isnull(@m4,f4) and f5=isnull(@m5,f5)
j9988(j9988) 的方法更简练,但各抒己见,我的是 CREATE PROCEDURE dbo.ddd @m1 char(4), @m2 char ... AS declare @l_sql varchar(1000) select @l_sql='' if @m1 is not null begin if @l_sql='' select @l_sql='f1='''+@m1+'''' else select @l_sql=@l_sql+' and '+'f1='+@m1 end if @m2 is not null begin if @l_sql='' select @l_sql='f2='''+@m2+'''' else select @l_sql=@l_sql+' and '+'f2='+@m2 end...if @l_sql='' select @l_sql='select * from tb' else select @l_sql='select * from tb where '+@l_sql exec(@l_sql) GO
错了一点,应该是CREATE PROCEDURE dbo.ddd @m1 char(4), @m2 char ... AS declare @l_sql varchar(1000) select @l_sql='' if @m1 is not null begin if @l_sql='' select @l_sql='f1='''+@m1+'''' else select @l_sql=@l_sql+' and '+'f1='''+@m1+'''' end if @m2 is not null begin if @l_sql='' select @l_sql='f2='''+@m2+'''' else select @l_sql=@l_sql+' and '+'f2='''+@m2+'''' end...if @l_sql='' select @l_sql='select * from tb' else select @l_sql='select * from tb where '+@l_sql exec(@l_sql) GO
select * from table where
f1=isnull(@m1,f1) and
f2=isnull(@m1,f2) and
f3=isnull(@m1,f3) and
f4=isnull(@m1,f4) and
f5=isnull(@m1,f5)
select * from table where
f1=isnull(@m1,f1) and
f2=isnull(@m2,f2) and
f3=isnull(@m3,f3) and
f4=isnull(@m4,f4) and
f5=isnull(@m5,f5)
CREATE PROCEDURE dbo.ddd
@m1 char(4),
@m2 char
...
AS
declare @l_sql varchar(1000)
select @l_sql=''
if @m1 is not null
begin
if @l_sql=''
select @l_sql='f1='''+@m1+''''
else
select @l_sql=@l_sql+' and '+'f1='+@m1
end
if @m2 is not null
begin
if @l_sql=''
select @l_sql='f2='''+@m2+''''
else
select @l_sql=@l_sql+' and '+'f2='+@m2
end...if @l_sql=''
select @l_sql='select * from tb'
else
select @l_sql='select * from tb where '+@l_sql
exec(@l_sql)
GO
@m1 char(4),
@m2 char
...
AS
declare @l_sql varchar(1000)
select @l_sql=''
if @m1 is not null
begin
if @l_sql=''
select @l_sql='f1='''+@m1+''''
else
select @l_sql=@l_sql+' and '+'f1='''+@m1+''''
end
if @m2 is not null
begin
if @l_sql=''
select @l_sql='f2='''+@m2+''''
else
select @l_sql=@l_sql+' and '+'f2='''+@m2+''''
end...if @l_sql=''
select @l_sql='select * from tb'
else
select @l_sql='select * from tb where '+@l_sql
exec(@l_sql)
GO