Using variable in sql WHERE clause for Operation

Question

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!

You can also do it with 2 variables.. @UpperLimit and @LowerLimit. (this is assuming you're only looking at > and <). Just throwing it out there. — sam yi
– sam yi, Commented Nov 2, 2015 at 19:08

Dai · Accepted Answer · 2015-11-02 19:07:37Z

4

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

answered Nov 2, 2015 at 19:07

Dai

158k31 gold badges314 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bobby T. Over a year ago

Thanks for your response, I thought about doing it this way, but I also have a timeUnit variable (sec, min, hrs) which makes the guard expression pretty long. But it looks like I'll just have to go this route.

Dai Over a year ago

@BobbyT. How are you storing time values? If you're storing data in a normalized (single column) value then you can use fractional/decimal units as operands.

Alexandre Mognon Silva · Accepted Answer · 2015-11-02 19:13:53Z

0

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)

answered Nov 2, 2015 at 19:13

Alexandre Mognon Silva

11 bronze badge

Comments

Gordon Linoff · Accepted Answer · 2015-11-02 19:17:52Z

0

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.

answered Nov 2, 2015 at 19:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Using variable in sql WHERE clause for Operation

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related