Does adherence to the DRY principle justify dynamic SQL

best practicesdynamic-sqlstored-procedures

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?

Best Answer

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?

Related Solutions

Sql-server – How to use same stored procedure for different tables and avoid using dynamic sql

The other thing you could do is create a view that unions the like tables together, and include a defined column with the table name, then just do a select against that view with the tablename in the where clause.

CREATE VIEW tablesviews AS
SELECT columns, 'TABLE1' as tablename
FROM Table1
UNION ALL
SELECT columns, 'TABLE2' as tablename
FROM Table2
...


SELECT {some columns}
FROM  tableviews
WHERE Tablename=@table and [other where clauses]

Sql-server – SQL Server: Recursive dynamic SQL puzzle

I am curious to know how the column names getting concatenated.

Can't really answer that. Some internal workings of SQL Server creates a result like that, sometimes.

The technique has been around for a considerable amount of time. Microsoft does not support the functionality and advises not to use it.

From SET @local_variable (Transact-SQL)

Do not use a variable in a SELECT statement to concatenate values (that is, to compute aggregate values). Unexpected query results may occur. This is because all expressions in the SELECT list (including assignments) are not guaranteed to be executed exactly once for each output row.

Instead you can use a cursor or the for xml trick to do the same.

declare @a as varchar(4000);
set @a = 'xxx ';

set @a = @a + (
              select quotename(COLUMN_NAME) as '*'
              from INFORMATION_SCHEMA.COLUMNS
              for xml path(''), type
              ).value('text()[1]', 'varchar(4000)');

Result: xxx [city][id][name]

Best Answer

Related Solutions

Sql-server – How to use same stored procedure for different tables and avoid using dynamic sql

Sql-server – SQL Server: Recursive dynamic SQL puzzle

Related Question