动态SQL查询

Question

假设我有一个房地产网站，在默认页面中，我想让用户获得符合其参数的帖子数量

我添加了 a link to an example image

计数器应该对每个更改做出反应，并自动向用户显示数字(这将在ajax jquery中实现)

我写了一个查询，但它只有在所有参数都填好的情况下才能工作，并且我想启用一些参数，同时仍能使计数器工作

下面是查询

ALTER PROCEDURE shahar30_nadlan.CountPropertyResultsByquery 


@AreaID         int,
@DealID        int,
@PropertyTypeId int,
@FieldMax      int,
@FieldMin      int,
@RoomsMax      decimal,
@RoomsMin      decimal,
@PriceMax      int,
@PriceMin      int,
@ParkingSpace  bit,
@Elevator      bit,
@Aircondition  bit,
@Furniture     bit,
@Warehouse     bit,
@Balkony       bit,
@handicapped  bit, 
@Immediate    bit,
@Description  NVARCHAR(MAX)

AS
BEGIN


select count(*) from Tbl_Property
where AreaID        = @AreaID         and 
  DealID        = @DealID         and
  PropertyTypeId= @PropertyTypeId and
  Field  <= @FieldMax        and
  Field  >= @FieldMin        and
  Rooms  <= @RoomsMax        and
  Rooms  >= @RoomsMin        and
  Price  <= @PriceMax        and
  Price  >= @PriceMin       and
  ParkingSpace = @ParkingSpace   and
  Elevator = @Elevator       and
  Aircondition = @Aircondition   and
  Furniture = @Furniture      and
  Warehouse = @Warehouse      and
  Balkony = @Balkony        and
  handicapped = @handicapped    and
  [Immediate] = @Immediate      and
  Description like  '%' + @Description + '%' 

END

谢谢你沙哈尔·纳迪亚

Answer 1

not所有参数都应填写

如果没有提供一个值，所以它是null (除非它有一个默认的val)，然后：

您应该使用的模式是：

select * from myTable where 

(  @companyId  IS NULL  OR ( companyId = @companyId ))
and
 ( @Name IS NULL  OR ( Name  = @Name ))
and
...

或

 (   companyId  = isnull(@companyId,companyId ))
      and
  (    Name = isnull(@Name ,Name ))
      and

    ...

coalesce也可以在这里使用--但速度较慢。( than is null)

Answer 2

你有没有尝试过可选参数？下面是一个示例：

@PriceMin      int = 1,

您可以像这样设置默认值，然后只传递所需的参数，让其余的参数采用默认值。