Stored Procedure with variable parameters

[Pieter]

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

 

[Roji. P. Thomas]

Have a look at

http://www.sommarskog.se/dyn-search.html

 

[Uri Dimant]

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]

Thanks guys, really great articles! I guess this will be the solution with
the best performance, flexible, and safe...

Thanks!

Pieter

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 =
-- 2
@fromdate datetime =
-- 3
@todate datetime =
-- 4
@minprice money =
-- 5
@maxprice money =
-- 6
@custid nchar(5) =
-- 7
@custname nvarchar(40) =
-- 8
@city nvarchar(15) =
-- 9
@region nvarchar(15) =
-- 10
@country nvarchar(15) =
-- 11
@prodid int =
-- 12
@prodname nvarchar(40) =
-- 13
@debug bit = 0
-- 14
--
15
DECLARE @sql
-- 16
@paramlist
-- 17
--
18
SELECT @sql
-- 19
'SELECT o.OrderID, o.OrderDate, od.UnitPrice,
ity, -- 20
c.CustomerID, c.CompanyName, c.Address, c.City,
.Region, -- 21
c.PostalCode, c.Country, c.Phone,
-- 22
p.ProductName, p.UnitsInStock,
r -- 23
FROM Orders
-- 24
JOIN [Order Details] od ON o.OrderID =
-- 25
JOIN Customers c ON o.CustomerID =
-- 26
JOIN Products p ON p.ProductID =
-- 27
WHERE 1 =
-- 28
--
29
IF @orderid IS NOT
-- 30
SELECT @sql = @sql + ' AND o.OrderID = @xorderid'
-- 31
' AND od.OrderID =
-- 32
--
33
IF @fromdate IS NOT
-- 34
SELECT @sql = @sql + ' AND o.OrderDate >=
-- 35
--
36
IF @todate IS NOT
-- 37
SELECT @sql = @sql + ' AND o.OrderDate <=
-- 38
--
39
IF @minprice IS NOT
-- 40
SELECT @sql = @sql + ' AND od.UnitPrice >=
-- 41
--
42
IF @maxprice IS NOT
-- 43
SELECT @sql = @sql + ' AND od.UnitPrice <=
-- 44
--
45
IF @custid IS NOT
-- 46
SELECT @sql = @sql + ' AND o.CustomerID = @xcustid'
-- 47
' AND c.CustomerID =
-- 48
--
49
IF @custname IS NOT
-- 50
SELECT @sql = @sql + ' AND c.CompanyName LIKE @xcustname +
%''' -- 51
--
52
IF @city IS NOT
-- 53
SELECT @sql = @sql + ' AND c.City =
-- 54
--
55
IF @region IS NOT
-- 56
SELECT @sql = @sql + ' AND c.Region =
-- 57
--
58
IF @country IS NOT
-- 59
SELECT @sql = @sql + ' AND c.Country =
-- 60
--
61
IF @prodid IS NOT
-- 62
SELECT @sql = @sql + ' AND od.ProductID = @xprodid'
-- 63
' AND p.ProductID =
-- 64
--
65
IF @prodname IS NOT
-- 66
SELECT @sql = @sql + ' AND p.ProductName LIKE @xprodname +
%''' -- 67
--
68
SELECT @sql = @sql + ' ORDER BY
-- 69
--
70
IF @debug =
-- 71
PRINT
-- 72
--
73
SELECT @paramlist = '@xorderid
-- 74
@xfromdate
-- 75
@xtodate
-- 76
@xminprice
-- 77
@xmaxprice
-- 78
@xcustid
-- 79
@xcustname
-- 80
@xcity
-- 81
@xregion
-- 82
@xcountry
-- 83
@xprodid
-- 84
@xprodname
-- 85
--
86
EXEC sp_executesql @sql,
-- 87
@orderid, @fromdate, @todate, @minprice,
@maxprice, -- 88
@custid, @custname, @city, @region,
ry, -- 89
@prodid,
-- 90

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