Does adherence to the DRY principle justify dynamic SQL?

Question

Suppose I have two queries that will be frequently run against my database:

SELECT 
  UserID,
  UserName,
  UserGender
FROM Users
WHERE UserID = @User

SELECT 
  UserID,
  UserName,
  UserGender
FROM Users
WHERE UserName LIKE @Name + '%

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?

Answer 1

Strict DRY doesn't really apply to databases

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?

Answer 2

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.