Sql statement with user provided filters

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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?

C#

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

Posted 16-Aug-16 18:49pm

Member 10379103

Updated 18-Aug-16 21:02pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Naga Sindhura · Accepted Answer · 2016-08-18T21:02:00

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.

SQL

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