title: Call Stored Procedure with Null Data Parameter in SQL Server
body: <pre><code>EXECUTE listCustomer null, Null, Null, Null, Null, Null, Null, Null, 
      Null, Null, 'customer_id', 'asc' 
  </code></pre>
  
  <p>I want to select all customers if there is not defined any filteration. Below is the stored procedure:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[listCustomer] 
  -- Add the parameters for the stored procedure here
  @customer_id int = 0
  , @customer_name varchar(111) = null
  , @customer_email varchar(111) = null
  , @customer_phone varchar(22) = null
  , @customer_fax varchar(22) = null
  , @customer_address varchar(255) = null
  , @customer_contact_person varchar(50) = null
  , @gl_account_id int = 0
  , @createdon datetime = null
  , @modifiedon datetime = null
  , @order_by varchar(50) = 'customer_id'
  , @sort_order varchar (5) = 'asc'
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  -- Insert statements for procedure here
  
  select
      c.*
      , ga.gl_account_title
  from
      customer c
  left join   gl_account ga
  on
      c.gl_account_id = ga.gl_account
  where
      c.customer_id = ISNULL(@customer_id,c.customer_id)
      and
      c.customer_name like '%'+isnull(@customer_name,c.customer_name)+'%'
      and
      c.customer_email = ISNULL(@customer_email,c.customer_email) 
      and
      c.customer_phone = ISNULL(@customer_phone,c.customer_phone)
      and
      c.customer_fax = ISNULL(@customer_fax,c.customer_fax)
      and
      c.customer_address like '%'+ISNULL(@customer_address,c.customer_address)+'%'
      and
      c.customer_contact_person = ISNULL(@customer_contact_person,c.customer_contact_person)
      and
      c.gl_account_id = ISNULL(@gl_account_id,c.gl_account_id)
      and
      c.createdon = ISNULL(@createdon,c.createdon)
      --and
      --c.modifiedon = ISNULL(@modifiedon,c.modifiedon)
  order by 
      case when @sort_order = 'asc' then
          case
              when @order_by = 'customer_id' then cast(c.customer_id as varchar)
              when @order_by = 'customer_name' then c.customer_name
              when @order_by = 'customer_contact_person' then c.customer_contact_person
              when @order_by = 'gl_acount_id' then cast(c.gl_account_id as varchar)
              when @order_by = 'createdon' then cast(c.createdon as varchar)
              when @order_by = 'modifiedon' then cast(c.modifiedon as varchar)
          end
      end asc
      , case when @sort_order = 'desc' then
          case
              when @order_by = 'customer_id' then cast(c.customer_id as varchar)
              when @order_by = 'customer_name' then c.customer_name
              when @order_by = 'customer_contact_person' then c.customer_contact_person
              when @order_by = 'gl_acount_id' then cast(c.gl_account_id as varchar)
              when @order_by = 'createdon' then cast(c.createdon as varchar)
              when @order_by = 'modifiedon' then cast(c.modifiedon as varchar)
          end
      end desc
  END
  </code></pre>
  
  <p>when I uncomment modifiedon condition from "where" statement it is giving me no answer means 0 rows. Else when I am commenting it so it is giving me correct answer.</p>
  
  <p>I think it is happening because there's nothing filled in the modified column and it is set to NULL.</p>
  
  <p>Please tell me where I am wrong in this.</p>
  
  <p>Sorry for the english!</p>