mercredi 29 avril 2015

SQL Server WHERE Clause with optional parameter not working as Expected

I have below query. Here Parameter @company is optional from .NET code.

When I pass the value, it is working as expected and bringing matching results for this @company. When i Don't pass the value, it should bring more records for all all other Companies also. Somehow it is not bringing back anything.

I know we can write dynamicSQL and add that condition if it is not null. But is there any other better short cut to do this?

Appreciate your responses.

    declare @company varchar(20) = 'AAA'
select 
    distinct fname as first_name, u.lname as last_name, 
    sc.company as employed_by
        , sc.short_description as employed_by_company_name, u.user_code
    from users u
        inner join sis_companies sc on sc.company = u.company 
            inner join sis_carriers car on sc.company = sc.company
            inner join carrier_shipper_contact_roles cashro on car.carrier = cashro.carrier
    Where cashro.carrier = 'CPL'
      and (isnull(sc.company, '') = '' or sc.company = @company)

Thanks

Aucun commentaire:

Enregistrer un commentaire