SQL Server dynamic where clause with variables on both sides

Question

Maybe this can't be done (other than with executing a SQL string) and I have tried many different ways but basically I want to build a dynamic where clause with both sides of the conditions as variables.

I need something like (but that works):

WHERE 0 = 0
  AND (
        (CASE @dateCondition
             WHEN 'DateCreated' THEN DateCreated
             WHEN 'DueDate' THEN DateCreated
             WHEN 'CompletionDate' THEN DateCreated
         END) = @dateFrom)

OR essentially

WHERE @dateCondition = @dateFrom

I would set the value for your where clause in a third variable, in an IF-ELSE statement depending on the value of @dateCondition, then I'd build the dynamic sql. In other words, do it in 2 steps. — Melanie, Aug 01 '16 at 19:49

score 0 · Answer 1 · answered Aug 01 '16 at 19:49

I've been out of the loop for a few years on front end queries, but typically (well, historically) I'd do something like this:

WHERE 1 = 1
    AND (
           (@dateCondition = 'DateCreated'    AND DateCreated = @dateFrom)
        OR (@dateCondition = 'DueDate'        AND DueDate = @dateFrom)
        OR (@dateCondition = 'CompletionDate' AND CompletionDate = @dateFrom)
    )

Although depending on my application, I may build the query dynamically in the code.

As always, be careful of NULL parameters.

score 0 · Answer 2 · edited Aug 01 '16 at 21:16

Of course as soon as I post the question I figure it out.

Here is what I am using. Let me know if you have a better more efficient solution.

Actually two solutions:

1.

    AND 
       CASE 
          WHEN @dateCondition = 'DateCreated' AND DateCreated >=  @dateFrom
             THEN 1
          WHEN @dateCondition = 'DueDate' AND DateCreated >=  @dateFrom
             THEN 1
          WHEN @dateCondition = 'CompletionDate' AND DateCreated >=  @dateFrom
             THEN 1
          ELSE 0  
       END = 1

2.

    AND DateCreated >= CASE 
                          WHEN @dateCondition = 'DateCreated'  
                             THEN @dateFrom 
                          ELSE DateCreated 
                       END

etc...

score 0 · Answer 3 · answered Aug 01 '16 at 20:01

Assuming the variables are set ahead of time, a simple way would be to write a dynamic query.

DECLARE @date DATE = SELECT 
                            CASE @dateCondition
                                WHEN  'DateCreated' THEN DateCreated
                                WHEN  'DueDate' THEN DateCreated
                                WHEN  'CompletionDate' THEN DateCreated
                            END
                     FROM [ . . . ]

DECLARE @sql NVARCHAR(MAX) = 
     'SELECT *
      FROM [. . .]
      WHERE
          @date = @dateFrom
     '

EXEC sp_executesql @sql, N'@date DATE, @dateFrom DATE', @date, @dateFrom

This has security holes, though, so if the variables aren't set explicitly then it's not the way to go.

score 0 · Answer 4 · answered Aug 01 '16 at 20:11

You don't need dynamic SQL

CASE WHEN @dateCondition ='DateCreated' AND DateCreated = @dateFrom THEN 1 ELSE 0 END = 1
OR
CASE WHEN @dateCondition ='DueDate' AND DueDate= @dateFrom THEN 1 ELSE 0 END = 1
OR 
CASE WHEN @dateCondition ='CompletionDate' AND CompletionDate= @dateFrom THEN 1 ELSE 0 END = 1

or like this

CASE WHEN @dateCondition ='DateCreated' THEN DateCreated ELSE @dateFrom END = @dateFrom 
AND 
CASE WHEN @dateCondition ='DueDate' THEN DueDate ELSE @dateFrom END = @dateFrom
AND
CASE WHEN @dateCondition ='CompletionDate' THEN CompletionDate ELSE @dateFrom END = @dateFrom

Depending on if you think in OR or you think in AND

Personally I think in AND.

SQL Server dynamic where clause with variables on both sides

4 Answers4