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