Greetings all!

We are porting some search functionality from an existing VB6 app to a web-based app. There is a form to allow the user to perform an advanced search on the data. In this case, there are 20+ different criteria elements that can be part of the query.

In the current application (VB6), this query is built dynamically in VB.

In the new application, the first pass was to create a stored procedure that accepted all of the parameters and then use the following shortcut for determining which criteria were used:

...
( @p_LastName IS NULL or v.Last_Name like @p_LastName + '%') AND
...

We also experimented with the COALESCE() method but were coming up with different results due to the way nulls were implemented in the table. Both of these methods are fairly common shortcuts for handling a bunch of parameters.

However, another project ran into performance issues with the first approach due to the fact that the execution plan is cached based on the first set of parameters that come in. This is also mentioned in the comments in the great post "Stored procedures are bad, m'kay?" - http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx post by Frans way back. Search for "with recompile" on the article.

To avoid those issues, as well as some slight result differences that we were experiencing, the direction given was to use the same logic from the VB6 app to dynamically build the SQL in the new app.

What was actually implemented was to dynamically build the SQL statement inside the stored procedure. Now, I've done dynamic SQL in my apps, used plenty of stored procedures, and also implemented solutions with LLBL. I have never built SQL inside a stored procedure. Asking around a little, there are a number of people on the team who have done it this way in the past.

My question(s): Is this a valid way of doing things? What are the ramifications? Pros? Cons? It seems to only allow you to explicitly know how the query will work (vs. coalesce and null compares) but you would still have potential performance issues because you still have a variable number of parameters going into the stored procedure. Plus, it seems like such a pain to code that kind of logic in SQL versus C#.

I'm hoping that the reason I haven't encountered it before is because it is a not so good practice that should be avoided.

Thoughts?

This article addresses what I'm talking about and seems to give approval (with conditions) for building SQL in stored procs: http://www.sqlservercentral.com/columnists/awarren/dynamicsqlorstoredprocedure.asp

I guess I've been living under a rock - I've found a number of resources online that talk about doing dynamic SQL in stored procs. This article on 4Guys was good: http://www.4guysfromrolla.com/webtech/102300-1.shtml

Still not sure that just because it can be done that way that it is a good practice.

Hey, Matt. I have to ask: Why are you dropping to stored procs in the first place? If you're simply parameterizing a query, why not parameterize a BL or DAL function that generates a custom LLBLGen PredicateExpression?

Ideally even, the query is generated on the fly by composing different IPredicates based on user selection without having to create a function that could potential grow nasty with ever-increasing parameters, special cases, if-thens, etc.

Personally, I try to avoid using stored procedures as a custom query solution where possible. I've found them to be inflexible, difficult to maintain, and ultimately limiting. That said, there's always the potential for a SQL CLR-based option for a little more maintainability, but that has it's own issues...

Anyway, without knowing more, those are my first questions...

Jeff...

jeffreygg wrote:

Hey, Matt. I have to ask: Why are you dropping to stored procs in the first place? If you're simply parameterizing a query, why not parameterize a BL or DAL function that generates a custom LLBLGen PredicateExpression?

Ideally even, the query is generated on the fly by composing different IPredicates based on user selection without having to create a function that could potential grow nasty with ever-increasing parameters, special cases, if-thens, etc.

Personally, I try to avoid using stored procedures as a custom query solution where possible. I've found them to be inflexible, difficult to maintain, and ultimately limiting. That said, there's always the potential for a SQL CLR-based option for a little more maintainability, but that has it's own issues...

Anyway, without knowing more, those are my first questions...

Jeff...

Hey Jeff! Good to hear from you. Thanks for responding.

This is for my day job and we are not using LLBL. That is where I needed you as an outside consultant to come in and recommend it

This project follows the practices of another team. In this particular case, data access is handled through .Net 2.0 typed datasets which are populated from stored procs. Oh what fun! Sure, they're great to get going with but they're a bitch to manage changes, etc. I much prefer the LLBL approach.

Hey. Weeellll, I would still avoid a DML-based solution if I could. If you're absolutely confined to sprocs and you have the ability, I would even consider a CLR-based solution (assuming you're on Sql 2005, yada yada) simply for maintainability purposes.

You can also mimic Frans' predicate system with a simple object-oriented representation of Sql predicate clauses. It wouldn't be too hard to build, would be much, much more maintainable and would give you quite a bit more flexibility. That said, it would probably be more work up front than a stored procedure/ExecuteSQL() approach so I couldn't say whether it would be worth it; it might be overkill depending on your needs.

Just the thought of all that single-quoted, concatenated Sql gives me the shivers. But I do have plenty of it myself, so I do understand...

Jeff...