CJM
Look at below example written by Erland helps you build the query.
CREATE PROCEDURE search_orders_1 --
1
@orderid int = NULL, --
2
@fromdate datetime = NULL, --
3
@todate datetime = NULL, --
4
@minprice money = NULL, --
5
@maxprice money = NULL, --
6
@custid nchar(5) = NULL, --
7
@custname nvarchar(40) = NULL, --
8
@city nvarchar(15) = NULL, --
9
@region nvarchar(15) = NULL, --
10
@country nvarchar(15) = NULL, --
11
@prodid int = NULL, --
12
@prodname nvarchar(40) = NULL, --
13
@debug bit = 0 AS --
14
--
15
DECLARE @sql nvarchar(4000), --
16
@paramlist nvarchar(4000) --
17
--
18
SELECT @sql = --
19
'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity, --
20
c.CustomerID, c.CompanyName, c.Address, c.City, c.Region, --
21
c.PostalCode, c.Country, c.Phone, p.ProductID, --
22
p.ProductName, p.UnitsInStock, p.UnitsOnOrder --
23
FROM Orders o --
24
JOIN [Order Details] od ON o.OrderID = od.OrderID --
25
JOIN Customers c ON o.CustomerID = c.CustomerID --
26
JOIN Products p ON p.ProductID = od.ProductID --
27
WHERE 1 = 1' --
28
--
29
IF @orderid IS NOT NULL --
30
SELECT @sql = @sql + ' AND o.OrderID = @xorderid' + --
31
' AND od.OrderID = @xorderid' --
32
--
33
IF @fromdate IS NOT NULL --
34
SELECT @sql = @sql + ' AND o.OrderDate >= @xfromdate' --
35
--
36
IF @todate IS NOT NULL --
37
SELECT @sql = @sql + ' AND o.OrderDate <= @xtodate' --
38
--
39
IF @minprice IS NOT NULL --
40
SELECT @sql = @sql + ' AND od.UnitPrice >= @xminprice' --
41
--
42
IF @maxprice IS NOT NULL --
43
SELECT @sql = @sql + ' AND od.UnitPrice <= @xmaxprice' --
44
--
45
IF @custid IS NOT NULL --
46
SELECT @sql = @sql + ' AND o.CustomerID = @xcustid' + --
47
' AND c.CustomerID = @xcustid' --
48
--
49
IF @custname IS NOT NULL --
50
SELECT @sql = @sql + ' AND c.CompanyName LIKE @xcustname + ''%''' --
51
--
52
IF @city IS NOT NULL --
53
SELECT @sql = @sql + ' AND c.City = @xcity' --
54
--
55
IF @region IS NOT NULL --
56
SELECT @sql = @sql + ' AND c.Region = @xregion' --
57
--
58
IF @country IS NOT NULL --
59
SELECT @sql = @sql + ' AND c.Country = @xcountry' --
60
--
61
IF @prodid IS NOT NULL --
62
SELECT @sql = @sql + ' AND od.ProductID = @xprodid' + --
63
' AND p.ProductID = @xprodid' --
64
--
65
IF @prodname IS NOT NULL --
66
SELECT @sql = @sql + ' AND p.ProductName LIKE @xprodname + ''%''' --
67
--
68
SELECT @sql = @sql + ' ORDER BY o.OrderID' --
69
--
70
IF @debug = 1 --
71
PRINT @sql --
72
--
73
SELECT @paramlist = '@xorderid int, --
74
@xfromdate datetime, --
75
@xtodate datetime, --
76
@xminprice money, --
77
@xmaxprice money, --
78
@xcustid nchar(5), --
79
@xcustname nvarchar(40), --
80
@xcity nvarchar(15), --
81
@xregion nvarchar(15), --
82
@xcountry nvarchar(15), --
83
@xprodid int, --
84
@xprodname nvarchar(40)' --
85
--
86
EXEC sp_executesql @sql, @paramlist, --
87
@orderid, @fromdate, @todate, @minprice, @maxprice, --
88
@custid, @custname, @city, @region, @country, --
89
@prodid, @prodname --
90
"CJM" <cj*******@newsgroups.nospam> wrote in message
news:u$**************@TK2MSFTNGP09.phx.gbl...
I'm building a search function for one of my applications. The user has
the option to enter a number criteria of criteria, but none are compulsary. I
need to be able to build up a query string that includes only the right
criteria. The simplest way I have found is something like this:
sSQL = "Select field1, field2, etc form table where 1=1"
If Request.Form("Criteria1") <> "" then
sSQL = sSQL & " and criteria1 = " & Request.Form("Criteria1")
End If
If Request.Form("Criteria2") <> "" then
sSQL = sSQL & " and criteria2 = " & Request.Form("Criteria2")
End If
...or something similar.
This will work fine, but I was just wondering if anybody had any
improvements or had opted for a different solution??
Thanks
Chris
