CODEWITHSUNDEEP
sqlsql-serverstored-procedures
blue piranha
blue piranha

Reputation: 3874

Building where clause based on null/not null parameters in sql

I am trying to create a stored procedure which will return records based on input. If all the input parameters are null, then return the entire table, else use the parameters and return the records:

 create procedure getRecords @parm1 varchar(10) = null, @parm2 varchar(10) = null, @parm3 varchar(10) = null
 as
 declare @whereClause varchar(500)
 set @whereClause = ' where 1 = 1 '

 if (@parm1 is null and @parm2 is null and @parm3 is null)
    select * from dummyTable
 else
    begin
      if (@parm1 is not null)
         set @whereClause += 'and parm1 = ' + '' + @parm1 + ''
      if (@parm2 is not null)
         set @whereClause += 'and parm2 = ' + '' + @parm2 + ''
      if (@parm3 is not null)
         set @whereClause += 'and parm3 = ' + '' + @parm3 + ''

      select * from dummyTable @whereClause  <-- Error 
    end

Error while creating this procedure is "An expression of non-boolean type specified in a context where a condition"

Please comment if my approach is wrong in building the where clause?

Thanks

Upvotes: 2

Views: 9462

Answers (3)

wildplasser
wildplasser

Reputation: 44220

select * from dummyTable
where (parm1 = @parm1 OR @parm1 IS NULL)
  and (parm2 = @parm2 OR @parm2 IS NULL)
  and (parm3 = @parm3 OR @parm3 IS NULL)
  ;

Upvotes: 9

Vikdor
Vikdor

Reputation: 24124

The entire query should be in a varchar and can be executed using "EXEC" function.

SET @query = "SELECT * FROM dummyTable WHERE 1=1 "

... your IF clauses ...

EXEC(@query)

HTH.

Upvotes: 5

BWS
BWS

Reputation: 3836

try something like this:

create procedure getRecords @parm1 varchar(10) = null, @parm2 varchar(10) = null, @parm3 varchar(10) = null
as
declare @whereClause varchar(500)
declare @sql varchar(500)

set @sql = 'select * from dummyTable '
set @whereClause = ' where 1 = 1 '

if (@parm1 is null and @parm2 is null and @parm3 is null)
    set @sql = @sql + @whereClause
else
   begin
set @sql = @sql + @whereClause
      if (@parm1 is not null)
        set @sql = @sql + ' and parm1 = ' + '' + @parm1 + ''
      if (@parm2 is not null)
        set @sql = @sql + ' and parm2 = ' + '' + @parm2 + ''
      if (@parm3 is not null)
        set @sql = @sql + ' and parm3 = ' + '' + @parm3 + ''
   end

exec (@sql)

Upvotes: 0

Related Questions