Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Should they be in two separate stored procedures, or just a single one which creates the statements dynamically using sp_executesql?

If they are in two stored procedures, then I will need to modify both procedures if I ever want to add or remove a column in the SELECT statement. And if I use dynamic SQL then presumably I am sacrificing a small amount of performance.

Is this a case where maintainability and adherence to the DRY principle (don't repeat yourself) by using dynamic SQL would take precedence over the performance gain of a stored procedure?

2 Answers
2

I've seen DRY is used as justification to create views for "re-use". Then we have views joining views etc and piss poor performance.

Generally, similar queries will be used in different ways. One may have an aggregate, one may not, filters will be different (as above). The similarity doesn't justify dynamic SQL not does it justify a view.

In your specific case above, I'd consider an IF statement to capture the difference, especially if the code was issued by the same search page or form: you have similarity based on usage, not just the same columns and table being used.

Also, you have security to consider.

Dynamic SQL require EXECUTE AS (escalation of rights) or permissions on the base tables. Using a plain stored procedure does not require such permissions.

You may also have different client code: we have stored procedures per client (schemas control permissions). Do you want to GRANT select rights on all your tables to all clients?

I'm not sure that dynamic SQL is the only answer here, though sometimes it can be the best solution if your search parameters get relatively complex. In the simple case you propose, why not a simple query that accepts one or both parameters...

WHERE UserID = COALESCE(@User, UserID)
AND UserName LIKE COALESCE(@Name+'%', UserName)

But you will have to consider dynamic SQL if the search conditions get complex because SQL Server will have better luck trying to optimize plans for the various versions of the query an trying to create one magic plan that satisfies all the different search criteria.

You could make separate stored procedures that identify the user(s) by their individual search methods, only returning the primary key value(s), and then call whichever procedure based on the criteria, dump the results in a #temp table, and then define the rest of your select list in a join with the base table. A lot more work up front but avoids the maintenance issue and allows you to optimize stored procedures for each search criteria.

My example is pretty simplistic, but for a more complex query that involved the usage of indexes, would the above approach open you up to parameter sniffing?
–
8kbJul 21 '11 at 0:58

Which above approach? Not trying to be glib but I'm not sure which one you're talking about. The separate procedure approach would not really do that because the optimization of the central proc is done separately and none of that would really depend on the parameters... now it could occur frequently if the criteria-based procedures are compiled with bad parameters.
–
Aaron Bertrand♦Jul 21 '11 at 1:39