CODEWITHSUNDEEP
mysqlsql
Sherlock
Sherlock

Reputation: 7597

Is it possible to parametrize an operator in SQL?

Is it possible, in any SQL dialect, to parametrize an operator?

I want to be able to do the following:

if(x == y)
{
     operator = '<=';
}
else
{
     operator = '=';
}

And then use that in a prepared statement:

SELECT a FROM b WHERE number :operator 10

I can't get this done in MySQL (what I'm using), but I'm wondering whether this is possible in any dialect.

Upvotes: 1

Views: 91

Answers (3)

GeorgesD
GeorgesD

Reputation: 1082

(at least : SQL server) You can do it as a string and then

  • Use a "select case" in your command

    CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END Searched CASE expression: CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END

    WHERE CASE WHEN @operator='<=' THEN number<=10 ELSE number=10 END

  • Building Dynamic SQL

Upvotes: 1

Andy Lester
Andy Lester

Reputation: 93735

No, you can't make an operator a parameter directly, and the other examples show you ways around it, by passing in a string that represents your choice of a code branch.

The important thing to remember about SQL statement preparation is that nothing that is executable may be parametrized. The only thing that can have placeholders is an actual value. Anything that would affect how the statement would be compiled cannot be a parameter.

Upvotes: 2

Guffa
Guffa

Reputation: 700582

You can make the parameter mean the inclusion of the range below the number:

if(x == y) {
  less = 1
} else {
  less = 0;
}

Use the parameter in an expression:

SELECT a FROM b WHERE (number < 10 and :less = 1) or (number = 10)

Upvotes: 1

Related Questions