0

I'm building a UI where user will have an option of entering different comparison operations such as >,<,>=,<=.

I need to reflect it in my query, so the WHEREclause in my query will look like this:

declare @number int
declare @operand varchar(2) = '>' --can be >, <, >=, <=

.....
WHERE amount` + operand + `@number`
.....

So, resulting piece would be WHERE amount > @number

gene
  • 2,098
  • 7
  • 40
  • 98
  • Possible duplicate of [how to set table name in dynamic sql query?](http://stackoverflow.com/questions/20678725/how-to-set-table-name-in-dynamic-sql-query) – Juan Carlos Oropeza Jun 17 '16 at 15:41
  • 2
    You have to use dynamic. And be aware of SQL Injection attacks – Code Different Jun 17 '16 at 15:41
  • 1
    Recommended to have a case statement if you only have the comparison operators commented in your code... primarily because of SQL injection. – RizJa Jun 17 '16 at 15:43
  • You could also take a look at a [similar question here](http://stackoverflow.com/questions/37840979/how-to-write-an-attribute-name-to-the-select-query-dynamically/37841275#37841275) – BJones Jun 17 '16 at 15:43

2 Answers2

0

Simply use this

declare @number int = 4
declare @operand varchar(2) = '>' --can be >, <, >=, <=

exec('select * from TableName
WHERE amount  ' + @operand + ' ' + @number)
Nazir Ullah
  • 610
  • 5
  • 13
0

This opens you up to SQL code injection. Also to concatenate an INT you have to set as varchar, but it will evaluate as INT in the actual statement. 

declare @number varchar(max) = '1'
declare @operand varchar(2) = '>' --can be >, <, >=, <=
declare @Where varchar(max)


SET @Where = 'WHERE amount ' +  @operand + ' ' + @number

SELECT @Where
Arleigh Reyna
  • 319
  • 1
  • 6