CODEWITHSUNDEEP
sqlsql-server
Bobby T.
Bobby T.

Reputation: 118

Using variable in sql WHERE clause for Operation

I am not a db admin by any means so if semantics look a bit bizarre, I apologize. In SQL Server, is it possible to use a variable for a comparison operator like so?

declare @compare = '>';
declare @limit = '5';

select agentId, sessionId, duration
from table_x
where duration @compare @limit

I do not want to write a ton of lines depending on what the variable could be, which is why I ask. Not to mention if I throw in another variable, that further makes the amount of lines even larger. Also, I like the fact that it's extensible, so in the event there was an additional operator, it would just process it anyway.

Thanks for the help!

Upvotes: 0

Views: 8677

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

One way of getting varied comparisons for numbers is to use sign():

where sign(duration - limit) = (case when @compare = '=' then 0
                                     when @compare = '>' then 1
                                     when @compare = '<' then -1
                                end)

However, these types of operations in the WHERE clause are not recommended because they preclude the use of indexes. Also, this structure doesn't work for strings or dates, but it does work for numbers.

Upvotes: 0

Alexandre Mognon Silva
Alexandre Mognon Silva

Reputation: 1

You can try doing this using dynamic sql, something like 

declare @compare varchar(2) = '>';
declare @limit int = '5';

declare @dynamicQuery = nvarchar(max) = N'select agentId, sessionId, duration
from table_x
where duration' + @compare + @limit

EXEC(@dynamicQuery)

Upvotes: 0

Dai
Dai

Reputation: 155688

You can, actually, by invoking different operators via a guard expression:

DECLARE @comparison varchar(2) = '>'
DECLARE @limit int 5 = 123

SELECT
    Foo
FROM
    Table
WHERE
    ( @comparson = '>' AND Duration > @limit )
    OR
    ( @comparson = '<' AND Duration < @limit )
    OR
    ( @comparson = '=' AND Duration = @limit )
    OR
    ( @comparson = '<>' AND Duration <> @limit )

To generalize this, you can convert this into a UDF and use it in multiple places:

CREATE FUNCTION VariableComparison(@comparison AS varchar(2), @value as numeric, @operand as numeric) RETURNS bit AS
BEGIN

    RETURN ( @comparison = '>' AND @value > @operand )
    OR
    ( @comparison = '<' AND @value < @operand )
    OR
    ( @comparison = '=' AND @value = @operand )
    OR
    ( @comparison = '<>' AND @value <> @operand )

END

Used like so:

SELECT
    ...
WHERE
    dbo.VariableComparison( @comparison, Duration, @limit ) = 1

Upvotes: 4

Related Questions