I have been thinking for a good solution on how to avoid the dynamic SQL in stored procedure for a while and I am happy that I got one. In my opinion, dynamic SQL statements are ugly, error-prone, difficult to maintain and hard to read especially if you have tons of conditional statements.
For example, I have stored procedure that accept two parameters, the parameter with a value will only include in WHERE statement. For this scenario I need to dynamically build the WHERE statement of my query. Every one in my office suggests doing an execute on the dynamic SQL statement.
CREATE PROCEDURE MySample
(
@supplierName varchar(100)
@categoryName varchar(100)
)
AS
DECLARE @sql varchar(8000)
DECLARE @hasWhere bit
SET @hasWhere = 0
SET @sql = 'SELECT S.CompanyName AS SupplierName,
C.CategoryName,
P.ProductName,
P.UnitPrice,
P.UnitsInStock,
P.QuantityPerUnit
FROM Suppliers AS S INNER JOIN
Products AS P ON S.SupplierID = P.SupplierID INNER JOIN
Categories AS C ON P.CategoryID = C.CategoryID'
IF LEN(@supplierName) > 0
BEGIN
SET @sql = @sql + ' WHERE S.CompanyName LIKE ''' + @supplierName + '%'''
SET @hasWhere = 1
END
IF LEN(@categoryName) > 0
BEGIN
IF @hasWhere = 0
SET @sql = @sql + ' WHERE '
ELSE
SET @sql = @sql + ' AND '
SET @sql = @sql + 'C.CategoryName LIKE ''' + @categoryName + '%'''
END
SET @sql = @sql + ' ORDER BY S.CompanyName, C.CategoryName, P.ProductName'
EXEC(@sql)
It looks messy, right? There are lots of IF statement (take note, this will also depends on the number of conditional statements) just to build the WHERE clause. Well, here's my solution.
CREATE PROCEDURE MySample
(
@supplierName varchar(100)
@categoryName varchar(100)
)
AS
SELECT S.CompanyName AS SupplierName,
C.CategoryName,
P.ProductName,
P.UnitPrice,
P.UnitsInStock,
P.QuantityPerUnit
FROM Suppliers AS S INNER JOIN
Products AS P ON S.SupplierID = P.SupplierID INNER JOIN
Categories AS C ON P.CategoryID = C.CategoryID
WHERE ((LEN(@supplierName) > 0 AND S.CompanyName LIKE @supplierName + '%') OR LEN(@supplierName) = 0) AND
((LEN(@categoryName) > 0 AND C.CategoryName LIKE @categoryName + '%') OR LEN(@categoryName) = 0)
ORDER BY S.CompanyName, C.CategoryName, P.ProductName
First, I checked the parameter value length, if it is greater than zero it will proceed to original condition, if not, you can do what ever you want (ex. 1 = 1, 0 = 0) as long as it return true.
You can avoid the number of times getting the parameter value length by this way:
CREATE PROCEDURE MySample
(
@supplierName varchar(100)
@categoryName varchar(100)
)
AS
DECLARE @supplierLength int
DECLARE @categoryLength int
SET @supplierLength = LEN(@supplierName)
SET @categoryLength = LEN(@categoryName)
SELECT S.CompanyName AS SupplierName,
C.CategoryName,
P.ProductName,
P.UnitPrice,
P.UnitsInStock,
P.QuantityPerUnit
FROM Suppliers AS S INNER JOIN
Products AS P ON S.SupplierID = P.SupplierID INNER JOIN
Categories AS C ON P.CategoryID = C.CategoryID
WHERE ((@supplierLength > 0 AND S.CompanyName LIKE @supplierName + '%') OR @supplierLength = 0) AND
((@categoryLength > 0 AND C.CategoryName LIKE @categoryName + '%') OR @categoryLength = 0)
ORDER BY S.CompanyName, C.CategoryName, P.ProductName
In the most basic case, the Hashtable class is used to do mapping of key/value pairs of objects.
For example, assume that you have User ID mapped to User's Fullname:
Hashtable users =
new Hashtable();
users["001"] = "Marasigan, Raimund";
users["002"] = "Marasigan, Myrene";
users["003"] = "Alcaras, Mong";
users["004"] = "Castro, Diego";
users["005"] = "Dizon, Mike";
Hashtable is specifically designed to handle large amount of collections. Unfortunately, there are some inefficiencies in Hashtable that can impede performance for small collections (10 items or less). That is where the ListDictionary comes in. It is implemented as a simple array of items underneath the hood, so it is very efficient for small collections. However, if you use ListDictionary for larger lists it is not efficient at all. So if you know your collection is small, use ListDictionary; if your collection is large, use Hashtable.
Okay. But what if you do not know how large your collection? The solution for this is to use HybridDictionary. Surprisingly, by default, it is implemented as a ListDictionary and when the collection becomes too large it automatically convert itself into a Hashtable.
The HybridDictionary is best used in situation where some collections are small and others are very large.
Cheers!
When I start developing my first application using Visual Basic 6 the first thing that I consider was the performance. But then I realized that performance shouldn't be the first to consider. My idea is most of the time you should focus on writing maintainable code. Then replace only those slow moving parts with faster but less clear code.
The main reason I do this is because it's much easier to improve the performance of well maintainable code.