create proc P_test @valuea varchar(10), @valueb varchar(10), @valuec varchar(10), @valued varchar(10) as select * from T where A = @valuea and B = @valueb and C = @valuec and D = @valued 这意思?
可以试一下: select * from T where A = value or B = value or C = value or D = value
可以用动态sql试试看declare @sql nvarchar(1024) set @sql = 'select * from T where 1=1 ' if( @value1 <> '') set @sql = @sql + ' and A=' + @value1 if( @value2 <> '') set @sql = @sql + ' and B=' + @value2 if( @value3 <> '') set @sql = @sql + ' and C=' + @value3 if( @value4 <> '') set @sql = @sql + ' and D=' + @value4exec sp_executesql @sql
create proc P_test @valuea varchar(10) = null, @valueb varchar(10) = null, @valuec varchar(10) = null, @valued varchar(10) = null as begin if (@valuea is null and @valueb is null and @valuec is null and @valued is null) begin raiserror('************, 16, 1) return -1 end else begin select * from T where A = @valuea and B = @valueb and C = @valuec and D = @valued end return 0 end
--试下这个语句: select * from T where (A like isnull(value,'%%') and B like isnull(value,'%%') and C like isnull(value,'%%') and D like isnull(value,'%%') --不过如果在A,B,C或D上有索引的会用不到索引.可以通过用动态SQL组成条件来查询
create proc p_test(@value1 varchar(10) null,@value2 varchar(10) null,@value3 varchar(10) null,@value4 varchar(10) null) as select * from T where (A like isnull(@value1,'%%') and B like isnull(@value2,'%%') and C like isnull(@value3,'%%') and D like isnull(@value4,'%%')
@valuea varchar(10),
@valueb varchar(10),
@valuec varchar(10),
@valued varchar(10)
as
select * from T where A = @valuea and B = @valueb and C = @valuec and D = @valued 这意思?
select * from T where A = value or B = value or C = value or D = value
set @sql = 'select * from T where 1=1 '
if( @value1 <> '')
set @sql = @sql + ' and A=' + @value1
if( @value2 <> '')
set @sql = @sql + ' and B=' + @value2
if( @value3 <> '')
set @sql = @sql + ' and C=' + @value3
if( @value4 <> '')
set @sql = @sql + ' and D=' + @value4exec sp_executesql @sql
@valuea varchar(10) = null,
@valueb varchar(10) = null,
@valuec varchar(10) = null,
@valued varchar(10) = null
as
begin
if (@valuea is null and @valueb is null and @valuec is null and @valued is null)
begin
raiserror('************, 16, 1)
return -1
end
else
begin
select * from T where A = @valuea and B = @valueb and C = @valuec and D = @valued
end return 0
end
select * from T
where (A like isnull(value,'%%')
and B like isnull(value,'%%')
and C like isnull(value,'%%')
and D like isnull(value,'%%') --不过如果在A,B,C或D上有索引的会用不到索引.可以通过用动态SQL组成条件来查询
create proc p_test(@value1 varchar(10) null,@value2 varchar(10) null,@value3 varchar(10) null,@value4 varchar(10) null)
as
select * from T
where (A like isnull(@value1,'%%')
and B like isnull(@value2,'%%')
and C like isnull(@value3,'%%')
and D like isnull(@value4,'%%')