Parameterized SQL with dynamic WHERE column

Question

I am trying to write simple filtering, where the user can input the column to filter and the value. The tricky part is dynamically choosing the column to filter.

I've found a couple solutions on the web and am not sure which to implement. My preference is to lean toward performance instead of maintainability. Any opinions would be greatly appreciated.

Let's assume I have a table "t", which has 5 VARCHAR columns: "c1", "c2", "c3", "c4", and "c5".

Solution 1 - The Easy Way

I could use Dynamic SQL. Something on the lines of:

DECLARE @sql VARCHAR(MAX) = 'SELECT * FROM t WHERE ' + @columnName + ' = ''' + @columnValue + ''';'
EXEC (@sql);

Which would come out to something like:

SELECT *
FROM t
WHERE c1 = 'asdf'
;

I don't want to use this solution for the following two reasons. I'm mainly including this as a simple point of reference before going down the rabbit hole.

It does not guard against SQL injection.
Even if I were to parameterize the columnValue, I would have 5 different execution plans cached for each of the 5 columns since you cannot parameterize @columnName.

Solution 2 - OR's

Could use a series of OR's with only two parameters. So let's say:

@columnName = 'c1'
@columnValue = 'asdf'

Then the SQL would become:

SELECT *
FROM t
WHERE (@columnName = 'c1' AND c1 = @columnValue)
  OR (@columnName = 'c2' AND c2 = @columnValue)
  OR (@columnName = 'c3' AND c3 = @columnValue)
  OR (@columnName = 'c4' AND c4 = @columnValue)
  OR (@columnName = 'c5' AND c5 = @columnValue)
  OR (@columnName IS NULL AND 0 = 0)
;

I generally try to stay away using OR when possible. I remember reading somewhere it suffers from performance issues, but I'm no DBA and can't back that up. Thoughts?

Solution 3 - COALESCE

This solution relies on having a parameter for each column. So parameters would be something on the lines of:

@c1 = 'asdf';
@c2 = NULL;
@c3 = NULL;
@c4 = NULL;
@c5 = NULL;

SQL comes out to:

SELECT *
FROM t
WHERE c1 = COALESCE(@c1, c1)
  AND c2 = COALESCE(@c2, c2)
  AND c3 = COALESCE(@c3, c3)
  AND c4 = COALESCE(@c4, c4)
  AND c5 = COALESCE(@c5, c5)
;

Does anyone have an opinion on what method to implement? I'm leaning towards COALESCE, but I have no hard numbers or experience on the matter. Maybe there is a better way of doing things?

You can validate the column name against the sys.columns view. — HABO
– HABO, Commented Aug 23, 2013 at 22:10
Have you tried the queries and investigated the execution plans? — Möoz
– Möoz, Commented Aug 27, 2013 at 6:38

Aaron Bertrand · Accepted Answer · 2015-08-17 17:26:33Z

The safest way:

DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM dbo.t WHERE ' 
 + QUOTENAME(@columnName) + ' = @ColumnValue;';

EXEC sp_executesql @sql, N'@ColumnValue VARCHAR(255)', @ColumnValue;

To guard further from SQL injection, you could first check:

IF @columnName NOT IN (N'c1',N'c2',N'c3',N'c4',N'c5')
BEGIN
  RAISERROR('Nice try! %s is not valid.', 11, 1, @columnName);
  RETURN;
END

Or as @HABO suggested, against the sys.columns catalog view:

IF NOT EXISTS 
(
   SELECT 1 FROM sys.columns WHERE name = @ColumnName
     AND [object_id] = OBJECT_ID('dbo.t')
)
BEGIN
  RAISERROR('Nice try! %s is not valid.', 11, 1, @columnName);
  RETURN;
END

Especially when combined with Optimize for ad hoc workloads, it is probably ok to have 5 different execution plans - since they are, after all, 5 different queries that could optimize differently based on indexes on different columns, distribution of data within those columns, etc.

Your OR and COALESCE versions - unless you pay a compilation hit every single time - will be stuck using the same plan no matter which column is provided, therefore it may work well for some situations, but not so well for others. And the plan everyone gets will not be based on what's best but which parameter is sent first.

Also, if you are concerned about performance, maybe don't use SELECT * - especially if you don't need all of the columns. Even if you do, you never know when someone adds a blob or geometry or XML or other expensive column to the table, and your code retrieves it even though it doesn't care about it.

Collectives™ on Stack Overflow

Parameterized SQL with dynamic WHERE column

Solution 1 - The Easy Way

Solution 2 - OR's

Solution 3 - COALESCE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Solution 1 - The Easy Way

Solution 2 - OR's

Solution 3 - COALESCE

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related