CODEWITHSUNDEEP
sqlsql-serversql-server-2008
cfly24
cfly24

Reputation: 1952

Using an SQL parameter for the operator in the where clause

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;

Upvotes: 1

Views: 65

Answers (2)

Sam CD
Sam CD

Reputation: 2097

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

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 415690

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

Upvotes: 3

Related Questions