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