none
Can't return null values with % as default for VARCHAR type when no parameter is passed

    Question

  • This is driving me nuts. I have a sp that has two parameters, "SITE" and "PO". I have set the defaults bot to '%' . Only one or the other parameters will be passed but not both. If I pass a SITE parameter I do not get any records where the PO field is NULL but that is what I want. WHat am I missing here? Here is my sp. Thanks for any help. 
    USE [AAIOMS_Development]
    GO
    /****** Object:  StoredProcedure [dbo].[spSelectLOG_ReqByPo]    Script Date: 10/29/2009 07:43:15 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[spSelectLOG_ReqByPo]
    (@Site varchar(3) = '%',
    @PO VARCHAR(8) = '%')
    AS SELECT      Date_Serial, RequestDate, PO, Site, CANC, REC
    FROM          IMMS.dbo.Requisitions
    WHERE      (Site LIKE @Site) AND (CANC IS NULL) AND (REC IS NULL) AND(PO LIKE @PO)
    Thursday, October 29, 2009 3:53 PM

Answers


  • ALTER
    PROCEDURE [dbo].[spSelectLOG_ReqByPo]

    (

      @Site varchar(3) = NULL ,

      @PO VARCHAR(8) = NULL

    )

    AS

    SELECT      Date_Serial, RequestDate, PO, [Site], CANC, REC

    FROM       IMMS.dbo.Requisitions

    WHERE      (Site LIKE '%' + @Site + '%' OR @Site IS NULL ) AND (CANC IS NULL) AND (REC IS NULL) AND(PO LIKE '%' + @PO '%' OR @PO IS NULL)

    The above will do a table scan, OK if you do not have too many records else you have to use dynamic SQL which has its own pitfalls.


    • Marked as answer by AkAlan Friday, October 30, 2009 5:08 PM
    Thursday, October 29, 2009 4:03 PM
  • try

    ALTER PROCEDURE [dbo].[spSelectLOG_ReqByPo]
    (
      @Site varchar(3) = NULL ,
      @PO VARCHAR(8) = NULL
    )
    AS
    BEGIN

    IF @site is not null and @po is not null
    Begin

        PRINT 'Both Values is filled, Please choose one'
        Return()

    END
    IF @site is not null
    Begin

        SELECT     Date_Serial, RequestDate, PO, [Site], CANC, REC
        FROM       IMMS.dbo.Requisitions
        WHERE      Site LIKE '%' + @Site + '%'  AND (CANC IS NULL) AND (REC IS NULL)

    END
    IF @PO Is NOT NULL
    BEGIN
        SELECT      Date_Serial, RequestDate, PO, [Site], CANC, REC
        FROM       IMMS.dbo.Requisitions
        WHERE       (CANC IS NULL) AND (REC IS NULL) AND(PO LIKE '%' + @PO '%' )
    END

    END

    Best Regards,
    Melissa Suciadi


    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.

    • Marked as answer by AkAlan Friday, October 30, 2009 5:16 PM
    Thursday, October 29, 2009 5:00 PM

All replies


  • ALTER
    PROCEDURE [dbo].[spSelectLOG_ReqByPo]

    (

      @Site varchar(3) = NULL ,

      @PO VARCHAR(8) = NULL

    )

    AS

    SELECT      Date_Serial, RequestDate, PO, [Site], CANC, REC

    FROM       IMMS.dbo.Requisitions

    WHERE      (Site LIKE '%' + @Site + '%' OR @Site IS NULL ) AND (CANC IS NULL) AND (REC IS NULL) AND(PO LIKE '%' + @PO '%' OR @PO IS NULL)

    The above will do a table scan, OK if you do not have too many records else you have to use dynamic SQL which has its own pitfalls.


    • Marked as answer by AkAlan Friday, October 30, 2009 5:08 PM
    Thursday, October 29, 2009 4:03 PM
  • try

    ALTER PROCEDURE [dbo].[spSelectLOG_ReqByPo]
    (
      @Site varchar(3) = NULL ,
      @PO VARCHAR(8) = NULL
    )
    AS
    BEGIN

    IF @site is not null and @po is not null
    Begin

        PRINT 'Both Values is filled, Please choose one'
        Return()

    END
    IF @site is not null
    Begin

        SELECT     Date_Serial, RequestDate, PO, [Site], CANC, REC
        FROM       IMMS.dbo.Requisitions
        WHERE      Site LIKE '%' + @Site + '%'  AND (CANC IS NULL) AND (REC IS NULL)

    END
    IF @PO Is NOT NULL
    BEGIN
        SELECT      Date_Serial, RequestDate, PO, [Site], CANC, REC
        FROM       IMMS.dbo.Requisitions
        WHERE       (CANC IS NULL) AND (REC IS NULL) AND(PO LIKE '%' + @PO '%' )
    END

    END

    Best Regards,
    Melissa Suciadi


    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.

    • Marked as answer by AkAlan Friday, October 30, 2009 5:16 PM
    Thursday, October 29, 2009 5:00 PM
  • Both accepted solutions worked with some slight tweeking on the syntax. I believe there are missing '+'s in both solutions here:

    (PO LIKE '%' + @PO '%') should be (PO LIKE '%' + @PO +'%')

    Otherwise they worked.Thanks for both posts, I really appreciate it.
    Friday, October 30, 2009 5:20 PM