title: How to ignore a parameter in stored procedure if its value is null
body: <p>I have a stored procedure in which I have to join 10 tables and use <code>WHERE</code> condition to filter the records based on the parameters passed in the stored procedure. For example: </p>
  
  <pre><code>create procedure proc1
      @var1 varchar(100) = null,
      @var2 varchar(100) = null,
      @var3 varchar(100) = null,
      @var4 varchar(100) = null,
      ........   
      @var10 varchar(100) = null
  as
  begin
      insert into #a
      select * from
      (
          select * from 
              tab1 as a
              inner join tab2 as b on a.rollnumber = b.rollnumber
              inner join tab3 as c on c.city = b.city
              ........
              inner join tab10 as j on J.id = i.id
          where 
              a.id = isnull(@var1,a.id) and 
              b.id = isnull(@var2,b.id) and 
              c.id = isnull(@var3,c.id) and 
              ...........
              J.id = isnull(@var10,j.id)
      ) as abc
  
      if (select count(*) from #a) &lt; 10 
      begin
          select * from #a
      end
      else 
      begin
          print 'Cannot display the records as count is more than 10'
      end
  end
  </code></pre>
  
  <p>Above stored procedure is working fine, but it is slow because there are 10 conditions in the <code>WHERE</code> clause. What I want is to skip the condition if some parameters are not supplied to the stored procedure. For example, if only 3 parameters are passed to the stored procedure, then <code>WHERE</code> clause should skip the rest of the parameters from the <code>WHERE</code> clause. This will make the procedure more efficient. So, if <code>@var1</code> is not passed, all the values of <code>a.id</code> should be returned.</p>