用存储过程实现多条件查询的方法
1. 拼接SQL代码,然后通过EXEC (@SQLString)得到结果。可读性不好,而且如果碰到SQL太长可能会溢出。
2. 今天想到个更好的取巧方法:设置参数的缺省值,然后将判断参数与缺省值是否相等 和 与此参数相关的查询条件 用OR连接 组成一个查询条件:
SET @startDate = '2007-01-01 00:00:00'
SET @officeId = NULL
SET @invoicedStatus = 0
WHERE
ejh.EnquiryDeleted = 0
AND (@startDate IS NULL OR ejh.DateTimeTaken >= @startDate )
AND (@officeId IS NULL OR cp.OfficeId = @officeId)
AND (@invoicedStatus = 0 OR CASE WHEN t.InvoiceNo IS NOT NULL THEN 1 ELSE 2 END = @invoicedStatus)
2. 今天想到个更好的取巧方法:设置参数的缺省值,然后将判断参数与缺省值是否相等 和 与此参数相关的查询条件 用OR连接 组成一个查询条件:
SET @startDate = '2007-01-01 00:00:00'
SET @officeId = NULL
SET @invoicedStatus = 0
WHERE
ejh.EnquiryDeleted = 0
AND (@startDate IS NULL OR ejh.DateTimeTaken >= @startDate )
AND (@officeId IS NULL OR cp.OfficeId = @officeId)
AND (@invoicedStatus = 0 OR CASE WHEN t.InvoiceNo IS NOT NULL THEN 1 ELSE 2 END = @invoicedStatus)