leonidas blog

Grip. Stand. Throw.

This Blog

Syndication

Search

Tags

Community

Email Notifications

Archives

How to avoid Dynamic SQL in Stored Procedure

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

Published 08-15-2007 12:27 AM by leonidas

Filed under: ,

Comments

# re: How to avoid Dynamic SQL in Stored Procedure@ Wednesday, August 15, 2007 8:16 PM

For dynamic searches, I find this article very useful: [www.sommarskog.se/dyn-search.html]. The same author (an SQL Server MVP) also has an article about dynamic SQL. Visit that too. [jop]

jop

# re: How to avoid Dynamic SQL in Stored Procedure@ Wednesday, August 15, 2007 8:24 PM

Thanks jop. I will check that later.

leonidas

# re: How to avoid Dynamic SQL in Stored Procedure@ Wednesday, August 15, 2007 10:22 PM

like what jop suggested checkout sommarskog's article. leverage default parameters to stored proc(devpinoy.org/.../1708.aspx) so that instead of having this:

[code]

WHERE ((@supplierLength > 0 AND S.CompanyName LIKE @supplierName + '%') OR @supplierLength = 0) AND

           ((@categoryLength > 0 AND C.CategoryName LIKE @categoryName + '%') OR @categoryLength = 0)

[/code]

you can have instead:

WHERE (S.CompanyName LIKE @supplierName + '%' OR @supplierName IS NULL )

     AND (C.CategoryName LIKE @categoryName + '%' OR @categoryName IS NULL)

with the advantage of just calling your stored proc:

EXEC mySample @supplierName = 'ACME'

or

EXEC mySample @categoryName = 'TIME BOMB'

Having said that however, the irony of this is that the best way to do a dynamic search(imo) is to use dynamic t-sql not by using stored proc but parameterized dynamic sql passed by your client to sql. While the solution, even with sommarskog's is great and all it will be unwieldy once you have additional search parameters and will be PITA to maintain.

What I did using NH or any solution was to have a SearchCriteria class with all the search params as properties and every setter appends to the internal sql string which builds those parameters as either hQl or sql and execute it.

hth

bonskijr

# re: How to avoid Dynamic SQL in Stored Procedure@ Wednesday, August 15, 2007 10:59 PM

I used LIKE comparison just for my example only. It can be = or <> rather. And the parameter type can be other type and not varchar.

leonidas