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?
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?Tweet