Using an SQL parameter for the operator in the where clause

Question

I am using SQL input parameters in a stored procedure to select different rows with various WHERE clauses. This is being done using dropdown lists in a view. The issue is that I am allowing the user to select the operator in the where clause using a dropdownlist.

CREATE PROCEDURE SingleClauseReport
    @selectedRows varchar(1000),
    @testLeftInput varchar(100),
    @testOperatorInput varchar(10),
    @testRightInput varchar(100)
AS
BEGIN
    Select @selectedRows
    from Test
    where @testLeftInput + ' ' + @testOperatorInput + ' ' + @testRightInput;
END
GO

I am getting an error stating that a condition is expected. Is it possible to do this? Otherwise I would just have to use one operator, like:

where @testLeftInput = @testRightInput;

Joel Coehoorn · Accepted Answer · 2015-08-10 16:37:28Z

3

What you're trying won't work. You have two options: dynamic sql, or a re-writing the procedure to have one query for each possible operator and then decide which to execute based on the parameter. I strongly recommend the latter.

CREATE PROCEDURE SingleClauseReport
    @selectedRows varchar(1000),
    @testLeftInput varchar(100),
    @testOperatorInput varchar(10),
    @testRightInput varchar(100)
AS
BEGIN
  If @testOperatorInput  = '=' 
  Begin
    Select @selectedRows
    from Test
    where @testLeftInput = @testRightInput;
  End
  Else If @testOperatorInput  = '>' 
  Begin
    Select @selectedRows
    from Test
    where @testLeftInput > @testRightInput;
  End
  --...
END

edited Aug 10, 2015 at 16:37

answered Aug 10, 2015 at 16:32

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

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

2 Comments

Sean Lange Over a year ago

Be careful of performance problems using this approach. Without a couple of additional things this can suffer greatly from cached execution plans. sqlinthewild.co.za/index.php/2009/09/15/…

cfly24 Over a year ago

Okay, I'm trying to figure out the least costly way to use this strategy with an AND in the WHERE clause (So there would be two operators), but that may be enough for another question

Sam CD · Accepted Answer · 2015-08-10 17:51:16Z

0

Don't do this:

AS
BEGIN

declare @sql nvarchar(max)

set @sql = N'Select ' + @selectedRows 
+ ' from Test where ' + @testLeftInput + ' ' 
+ @testOperatorInput + ' ' 
+  @testRightInput;

execute sp_executesql @sql

END

edited Aug 10, 2015 at 17:51

answered Aug 10, 2015 at 16:35

Sam CD

2,1072 gold badges20 silver badges36 bronze badges

1 Comment

Sean Lange Over a year ago

I would highly advise you NOT to use this method. It is wide open to sql injection. This is pretty much a textbook example of how sql injection works.

Collectives™ on Stack Overflow

Using an SQL parameter for the operator in the where clause

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related