SQL Server Performance

Query

Discussion in 'ALL SQL SERVER QUESTIONS' started by immad, Nov 5, 2013.

  1. immad Member

    hello

    select vm.VoucherTypeCode,vm.VoucherNo,vm.VoucherDate,vm.Narration, vt.VoucherType
    from VoucherMaster vm
    left join VoucherDetail vd on vm.VoucherTypeCode = vd.VoucherTypeCode
    left join VoucherType vt on vt.VoucherTypeCode = vt.VoucherTypeCode
    where
    vm.VoucherDate BETWEEN @FromDate and @ToDate
    AND (ISNULL(@VoucherNo,'')='' OR vm.VoucherNo = @VoucherNo)

    i am getting problem in this row AND (ISNULL(@VoucherNo,'')='' OR vm.VoucherNo = @VoucherNo)

    when i search from voucherno its give me null result

    please help me out
  2. immad Member

    i change it but still same result
  3. Iain Cowden New Member

    Not sure why you are using ISNULL as you are not replacing the NULLs with anything.
    Try
    AND (@Voucherno is null OR vm.Voucherno=@VoucherNo)
  4. davidfarr Member

    I can see why immad uses ISNULL(@VoucherNo,'')=''
    @VoucherNo is a varchar type and could contain an empty string value.
    ISNULL(@VoucherNo,'')='' makes provision for both nulls and empty string values.
    (@Voucherno is null OR vm.Voucherno=@VoucherNo) does not.

    In immad's query; this table joining on itself is somewhat pointless;
    left join VoucherType vt on vt.VoucherTypeCode = vt.VoucherTypeCode
    ...although I cannot be certain that it's the cause of his problem.
    I would need to see some actual data from the tables to know for sure. 
  5. Iain Cowden New Member

    I'd be willing to bet that that line should end vt.vouchercode=vd.vouchercode, in order to get additional details from the "type" table.

    I wonder if he is then adding a WHERE and looking for a specific Type (which never gets returned as there are no Types due to the Typo...).

    Lets wait for the OP...

Share This Page