多条件的存储过程,这样写有意义吗?????

[ligh820]

CREATE PROCEDURE web_Search
@Brand varchar(50), ---品牌
@Catena varchar(50), ---系列
@CarType varchar(50), ---车型
@Byear int, ---车龄
@Mileage int, ---行驶里程数
@ShowOrder int ---排序方法,1表示

AS

Declare
@Sql nvarchar(1024),


Set @Sql = 'Select * from TCar Where bAuditing =1


if len(@Brand) > 0
begin
set @Sql=@Sql+' and '
set @Sql=@Sql+' vBrand=''' + @Brand+''''
end

if len(@Catena) > 0
begin
set @Sql=@Sql+' and '
set @Sql=@Sql+ ' vCatena=''' + @Catena +''''
end

if len(@CarType) >0
begin
set @Sql=@Sql+' and '
set @Sql=@Sql+ ' tCarType=''' + @CarType +''''
end

if @Byear >0 and @Byear is not null
begin
set @Sql=@Sql+' and '
set @Sql=@Sql+ ' nYear >'+CAST(year(getdate())-@Byear as varchar)
end

if @Mileage >0 and @Mileage is not null
begin
set @Sql=@Sql+' and'+
CASE @Mileage
WHEN 1 THEN ' nMileage <= 5000'
WHEN 2 THEN ' nMileage >= 5000 and nMileage <= 10000'
WHEN 3 THEN ' nMileage >= 10000 and nMileage <= 20000'
WHEN 4 THEN ' nMileage >= 20000 and nMileage <= 50000'
WHEN 5 THEN ' nMileage >= 50000 and nMileage <= 80000'
WHEN 6 THEN ' nMileage >= 80000 and nMileage <= 100000'
WHEN 7 THEN ' nMileage >= 100000 and nMileage <= 150000'
WHEN 8 THEN ' nMileage >= 150000 and nMileage <= 200000'
WHEN 9 THEN ' nMileage >= 200000'
END
end


----------------显示排序方法--------------
set @Sql=@Sql+
CASE @ShowOrder
WHEN 1 THEN ' order by nPrice asc'
WHEN 2 THEN ' order by nPrice desc'
WHEN 3 THEN ' order by dPromulgate asc'
WHEN 4 THEN ' order by dPromulgate desc'
WHEN 5 THEN ' order by hit asc'
WHEN 6 THEN ' order by hit desc'
ELSE ' order by dPromulgate desc '
END

EXEC sp_executeSql @Sql

WITH RECOMPILE
GO

[marco08]

有什麽問題嗎？

[ligh820]

上面的存储过程我还减少了很多,本来是很多条件的查询

原理就很简单了,就是通过if 条件来构造一条sql语句...

最后在执行这条sql语句,

EXEC sp_executeSql @Sql ----

这样有意义吗??????????是不是速度要快些???????

[ligh820]

说明,上面的存储过程执行没有问题,!~

我要的是执行的速度>>>是不是比以前直接执行要快>.....????

[hillhx]

可以考虑换个方法，比如

这样组合条件，当你不想用这个条件的时候传‘%’而不是用动态SQL
(name = @s_name or @s_name='%')

[zsl5305256]

条件查询判断是免不了的,应该是不会影响速度!

[hbtm]

up