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?