I don't think you even need dynamic SQL here. You can use variables in the query.
SELECT *
FROM [audit].[LoginAttempt]
WHERE [TimeStamp] <= dateadd(day, -1 * @backDays, getdate())
AND CASE
WHEN @successArg IS NOT NULL
THEN (SELECT 1
WHERE [Success] = @successArg)
ELSE 1
END = 1;
(Not tested as no sample data was provided. Just to demonstrate the idea.)
If you look close at it, you'll see, that the entire CASE ... END
is the left operand of an =
operation. The right side of the operation is 1
.
Unfortunately SQL Server knows no Boolean type we could have directly returned from the CASE ... END
. (Others, e.g. PostgreSQL do.) So we have to trick it using an expression it can use for a Boolean operation. That's why we use the =
operation.
Now we need a way of making this operation evaluate to true when the conditions we actually want to test are met. So the idea is, if we come to the decision, that they are met, we return one designated value for our left side of the =
. On the other side, we just use that value literally. The =
will be true then. If our conditions don't meet, we return any other value for the left side, and the =
won't be true. As a value to indicate the conditions were met, lets choose the 1
. It's close to a representation of a Boolean we might have in mind. (But we could have chosen nearly anything (NOT NULL
, otherwise we had to change the =
operation to IS NULL
).).
So how can we make our left expression to return 1
, when the conditions are met?
Well, we can use a CASE ... END
statement to return a value based on certain conditions. You already know the CASE ... END
. It's somewhat similar to a switch
or if else
constructs in C-like languages (or with different names in other procedural languages).
What we need to test is if the input variable @successArg
is null or not. If it's null, that means the caller doesn't care, whether the logged login attempts were successful or not. Otherwise the value of @successArg
indicates whether he wants only successful logins (@successArg = 1
) or only unsuccessful (@successArg = 0
). This will give us to cases on top: Either ignore the successfulness or take it into account. Hence we have two (main) branches in our CASE ... END
branching about @successArg IS NOT NULL
;
Let's start with the easier case, when @successArg IS NULL
. That's the 'ELSE' branch. Here, we don't care about if the login attempt was successful or not. Being successful or not successful (think of it a Boolean expression) is always true, for any row. So we just return our indicator, that this was a match, 1
.
When @successArg IS NULL
, @successArg
holds the value, the rows must have in the column [Success]
. This is given only if @successArg = [Success]
. As already mentioned, @successArg = [Success]
alone won't be something SQL Server could handle in that context. So we need our true indicating value 1
to be returned when @successArg = [Success]
for the current row, something else otherwise.
One way of doing this would be another, inner CASE ... WHEN
or we might use a correlated subquery. It's "correlated" as it uses values of columns from the current row of the outer query. And it's "sub", well because it's "sub", viewed from the position of the outer query. Furthermore, we use the feature in SQL Server that a SELECT
with no FROM
will produce one record if the WHERE
of that query is true (or if there is none), otherwise an empty set. So let's produce a record with one column holding 1
when @successArg = [Success]
. Such a record, with only one column, will implicitly be casted to a scalar if needed, hence it's suitable for our =
operation outside. When @successArg <> [Success]
, that subquery will produce an empty set. The empty set in that scalar context will be implicitly casted to NULL
. As NULL = 1
isn't true, this will do it for us.
One side note on this: It also can be done without a CASE ... END
but simply a Boolean expression:
SELECT *
FROM [audit].[LoginAttempt]
WHERE [TimeStamp] <= dateadd(day, -1 * @backDays, getdate())
AND (@successArg IS NULL
OR [Success] = @successArg);
That follows our thoughts from above. In case @successArg IS NULL
we want "true" regardless of anything else. Otherwise we only want "true" when [Success] = @successArg
. In any other case "false". (Note: The parenthesis are needed to override the precedence of AND
over OR
because the previous condition.)
In terms of how the query is executed, hence performance, I don't think this makes a significant difference, if any (but frankly I don't know this for sure). The solution with a CASE ... WHEN
though, might be easier to read and therefore maintain. The Boolean only solution, on the other hand, also works in systems, that are not capable of a CASE ... END
.