1
EXECUTE listCustomer null, Null, Null, Null, Null, Null, Null, Null, 
    Null, Null, 'customer_id', 'asc' 

I want to select all customers if there is not defined any filteration. Below is the stored procedure:

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

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.

I think it is happening because there's nothing filled in the modified column and it is set to NULL.

Please tell me where I am wrong in this.

Sorry for the english!

3
  • you need to use dynamic sql for this to work. Commented Oct 4, 2013 at 5:36
  • It was working fine before createdon and modifiedon but when I added these columns and set the starting value to null. after that it is happening Commented Oct 4, 2013 at 5:37
  • Is there any solution to send datetime null parameter so that it would work fine i think. Commented Oct 4, 2013 at 5:38

2 Answers 2

2

Try to change the condition that's giving your problems to:

(c.modifiedon = @modifiedon OR @modifiedon IS NULL)
Sign up to request clarification or add additional context in comments.

1 Comment

Yes It is working fine now... Let me check other criteria as well.
0

You might use NULLIF

 NullIF(c.modifiedon,@modifiedon) IS NULL

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.