带有用户提供的过滤器的Sql语句 [英] Sql statement with user provided filters

问题描述

我的报告有几个字段。用户希望能够选择他们自己的各种条件。有时他们想使用AND，有时他们想使用OR，有时他们想要使用LIKE，BETWEEN或所有这些的组合。



下面是我建议如何做的一些示例代码。



有没有更好或更有效的方法来做到这一点？

I have a report that has several fields. The users want to be able to select their own variety of conditions. Sometimes they want to use AND, sometimes they want to use OR, sometimes they want to use LIKE, or BETWEEN, or a combination of all of these.

Below is some example code of how I propose to do this.

Is there a better or more efficient way to do this?

custname  nvarchar(40) = NULL,                   
                 @city      nvarchar(15) = NULL,                   
                 @region    nvarchar(15) = NULL,                   
                 @country   nvarchar(15) = NULL,                   
                 @prodid    int          = NULL,                   
                 @prodname  nvarchar(40) = NULL,              
                 @proddesc varchar(75) =NULL,
                 @prodprice int =NULL,
                 @netcost  int    =NULL,
                 @markup int =NULL,
                 @fromdate datetime =NULL,
                 @todate  datetime =NULL
                 @debug     bit          = 0 AS                    
                                                                   -
DECLARE @sql        nvarchar(4000),                                
        @paramlist  nvarchar(4000)                                 
                                                                   
SELECT @sql =                                                      
   SELECT OrderID, OrderDate, UnitPrice, Quantity,     
            CustomerID, CompanyName, Address, City,       
            Region,  PostalCode, Country, Phone,           
            ProductID, ProductName, UnitsInStock,            
            UnitsOnOrder                                         
     FROM   dbo.Orders
     WHERE  1 = 1                                        
                                                                   
IF @orderid IS NOT NULL                                            
   SELECT @sql = @sql + 'AND OrderID = @orderid
                        
                                                                   
IF @fromdate IS NOT NULL                                           
   SELECT @sql = @sql + ' AND @fromdate
                                                                  
IF @todate IS NOT NULL                                             
   SELECT @sql = @sql + 'AND @todate
                                                                   
IF @minprice IS NOT NULL                                           
   SELECT @sql = @sql + 'AND UnitPrice = @xminprice
                                                                 
IF @maxprice IS NOT NULL                                         
   SELECT @sql = @sql + 'AND UnitPrice  @xmaxprice
                                                                   
IF @custid IS NOT NULL                                            
   SELECT @sql = @sql + '
                        AND CustomerID = @custid
SELECT @sql = @sql + 'ORDER BY OrderID
                                                                  
IF @debug = 1                                                     
   PRINT @sql                                                    
                                                                  
SELECT @paramlist = @orderid   int,                             
                     @Fromdate  datetime,                        
                     @Todate    datetime,                        
                     @minprice  money,                           
                     @maxprice  money,                            
                     @custid    nchar(5),                        
                     @custname  nvarchar(40),                    
                     @city      nvarchar(15),                     
                     @region    nvarchar(15),                     
                     @Country   nvarchar(15),                     
                     @Prodid    int,                              
                     @Prodname  nvarchar(40)  
                                                                   
EXEC sp_executesql @sql, @paramlist,                               
                   @orderid, @fromdate, @todate, @minprice,        
                   @maxprice,  @custid, @custname, @city, @region, 
                   @country, @prodid, @prodname

What I have tried:

I have tried Google search and MSDN

推荐答案

不要编写多行代码，而是尝试引用以下查询。我提供了几种方法来优化您在问题中请求的查询并根据需要进行修改。

Hi,

Instead of writing the multiple lines of code, try to refer the following query. I have provided few ways of optimizing your query as you requested in the question and modify it according as required.

SELECT OrderID, OrderDate, UnitPrice, Quantity,
         CustomerID, CompanyName, Address, City,
         Region,  PostalCode, Country, Phone,
         ProductID, ProductName, UnitsInStock,
         UnitsOnOrder
  FROM   dbo.Orders
  WHERE @orderid IS NULL OR OrderID = @orderid)
  AND (@custid IS NULL OR AND CustomerID = @custid)
  AND (@custname IS NULL OR AND  LIKE CompanyName like '%'+@custname+'%')
  AND ( (@fromdate IS NULL OR OrderDate >= @fromdate)OR(@todate IS NULL OR OrderDate <= @todate)OR
        ((@fromdate IS NULL AND @todate IS NULL) OR OrderDate BETWEEN @fromdate AND @todate))
  AND ( (@minprice IS NULL OR UnitPrice >= @minprice)OR(@todate IS NULL OR UnitPrice <= @maxprice)OR
      ((@minprice IS NULL AND @maxprice IS NULL) OR UnitPrice BETWEEN @minprice AND @maxprice))
  ORDER BY OrderID

这篇关于带有用户提供的过滤器的Sql语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！