Hi
I think you need something like that written by Erland
For more info , I suggest you to visit on his website
http://www.sommarskog.se/
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
"Pieter" <pi**********@hotmail.com> wrote in message
news:ui**************@tk2msftngp13.phx.gbl...
Hi,
I have a View which contains (of course) several columns. Now I want to
make a Stored Procedure that does a Select based on the parameters.
For exemple: One time I want to select all the reocrds with ColumnA =
'Value A', an other time all the reords with ColumnB = 'An other value', a
third time all records with "ColumnA = 'Value C' AND ColumnB = 'this
value'" etc etc.
So my paramters must be kind of flexible.
What is the best solution for this?
- Adding a paramter to my stored procedure for every column, evaluate them
and when not empty build a SQL-query dynamically and EXEC it?
- Building the WHERE-clause in my application (VB.NET 2005), and giving
this as 1 paramter to my stored procedure, and build the sql query and
EXEC it?
- Some nice SQL Server/VB.NET solution that handles this kind of cases in
a smooth way? :-)
Are there different kinds of performances with the different possible
solutions?
Thanks a lot in advance,
Pieter
