Stored procedure with input parameters

• sql_2005_fan

  SSCommitted

  Points: 1871

  More actions

  September 21, 2017 at 6:17 pm

  #349214

  Hi ,

  I have the following stored procedure with  three input parameters. 
  But not all the times I pass value for the three input parameters. If I pass a null value to a parameters it should ignore that paramter.

  If I wanted to execute the stored procedure with only the paramter phonetype as phonetype='res'  the select statement in the stored procedure should ignore firstname and lastname parameters.

  CREATE PROCEDURE GetPersonPhoneDetails

  @firstname varchar(50),@lastname varchar(50),@PhoneType varchar(50)
  AS

  SET NOCOUNT ON

  SELECT p.[BusinessEntityID]
     ,p.[Title]
    ,p.[FirstName]
    ,p.[MiddleName]
    ,p.[LastName]
    ,p.[Suffix]
          ,pp.PhoneNumber
          ,ppt.PhoneType
  FROM [AdventureWorks2012].[Person].[Person] p INNER JOIN [AdventureWorks2012].[Person].[PersonPhone] pp
  ON p.BusinessEntityID=pp.BusinessEntityID INNER JOIN [AdventureWorks2012].[Person].[PhoneNumberType] ppt
  ON pp.PhoneNumberTypeID=ppt.PhoneNumberTypeID
  WHERE p.firstname like IsNull(@FirstName,lc.FirstName)+'%'
  AND p.lastname like IsNull(@LastName,lc.LastName)+'%'
  AND ppt.name like IsNull(@Name,lc.Name)+'%'
  GO
      
  I have the above code and when @FirstName parameter is null and the real value is null I am not getting the required result set.
  Is there a way I can avoid  the following clause in the where statement if the input value parameter @firstname is null?
  p.firstname like IsNull(@FirstName,lc.FirstName)+'%'

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  September 21, 2017 at 8:31 pm

  #1960432

  Did you try COALESCE instead?

  DECLARE @LastName VARCHAR(20) = NULL;
SET NOCOUNT ON;
SELECT p.[BusinessEntityID]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
,p.[LastName]
,p.[Suffix]
,pp.PhoneNumber
FROM [Person].[Person] p INNER JOIN [Person].[PersonPhone] pp
ON p.BusinessEntityID=pp.BusinessEntityID INNER JOIN [Person].[PhoneNumberType] ppt
ON pp.PhoneNumberTypeID=ppt.PhoneNumberTypeID
WHERE p.LastName LIKE COALESCE(@LastName,p.LastName) + '%';

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  September 22, 2017 at 1:34 am

  #1960443

  This doesn't make sense.  Where does the lc alias in your WHERE clause come from?

  John

• Thom A

  SSC Guru

  Points: 99130

  More actions

  September 22, 2017 at 1:51 am

  #1960445

  Have a look at Gail's article here, specifically looking at the part about "The Catch-all Query": https://www.red-gate.com/simple-talk/sql/t-sql-programming/how-to-confuse-the-sql-server-query-optimizer/

  She gives a few solutions there.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  September 22, 2017 at 6:57 am

  #1960481

  I have to agree with John Mitchell - your query has a few holes in it.   You have no table alias "lc", so that's just not right.  I also see you examining an @Name parameter that also doesn't exist, and I've assumed you meant @PhoneType.   You also then reference ppt.name, and I'm not sure if that's what you want, as I don't happen to have a copy of AdventureWorks handy to check it out.   My query below removes all that ISNULL functionality and instead checks for all the possible combinations of which values are NULL

  You'll have to test it to be sure that it's what you want.   It will avoid the lack of sargability that your posted query has, which was making index use impossible.
  CREATE PROCEDURE GetPersonPhoneDetails (
    @firstname varchar(50),
    @lastname varchar(50),
    @PhoneType varchar(50)
)
AS
BEGIN
SET NOCOUNT ON;
IF @firstname IS NULL AND @lastname IS NULL AND @PhoneType IS NULL
    BEGIN
    SELECT p.BusinessEntityID
        , p.Title
        , p.FirstName
        , p.MiddleName
        , p.LastName
        , p.Suffix
        , pp.PhoneNumber
        , ppt.PhoneType
    FROM AdventureWorks2012.Person.Person AS p
        INNER JOIN AdventureWorks2012.Person.PersonPhone AS pp
            ON p.BusinessEntityID = pp.BusinessEntityID
        INNER JOIN AdventureWorks2012.Person.PhoneNumberType AS ppt
            ON pp.PhoneNumberTypeID = ppt.PhoneNumberTypeID;
    END;
ELSE
    IF @firstname IS NULL AND @lastname IS NULL
        BEGIN
        SELECT p.BusinessEntityID
            , p.Title
            , p.FirstName
            , p.MiddleName
            , p.LastName
            , p.Suffix
            , pp.PhoneNumber
            , ppt.PhoneType
        FROM AdventureWorks2012.Person.Person AS p
            INNER JOIN AdventureWorks2012.Person.PersonPhone AS pp
                ON p.BusinessEntityID = pp.BusinessEntityID
            INNER JOIN AdventureWorks2012.Person.PhoneNumberType AS ppt
                ON pp.PhoneNumberTypeID = ppt.PhoneNumberTypeID
        WHERE ppt.PhoneTyoe LIKE @PhoneType + '%';
        END;
     ELSE
        IF @firstname IS NULL AND @PhoneType IS NULL
            BEGIN
            SELECT p.BusinessEntityID
                , p.Title
                , p.FirstName
                , p.MiddleName
                , p.LastName
                , p.Suffix
                , pp.PhoneNumber
                , ppt.PhoneType
            FROM AdventureWorks2012.Person.Person AS p
                INNER JOIN AdventureWorks2012.Person.PersonPhone AS pp
                    ON p.BusinessEntityID = pp.BusinessEntityID
                INNER JOIN AdventureWorks2012.Person.PhoneNumberType AS ppt
                    ON pp.PhoneNumberTypeID = ppt.PhoneNumberTypeID
            WHERE p.lastname LIKE @lastname + '%';
            END;
         ELSE
            IF @lastname IS NULL AND @PhoneType IS NULL
                BEGIN
                SELECT p.BusinessEntityID
                    , p.Title
                    , p.FirstName
                    , p.MiddleName
                    , p.LastName
                    , p.Suffix
                    , pp.PhoneNumber
                    , ppt.PhoneType
                FROM AdventureWorks2012.Person.Person AS p
                    INNER JOIN AdventureWorks2012.Person.PersonPhone AS pp
                        ON p.BusinessEntityID = pp.BusinessEntityID
                    INNER JOIN AdventureWorks2012.Person.PhoneNumberType AS ppt
                        ON pp.PhoneNumberTypeID = ppt.PhoneNumberTypeID
                WHERE p.FirstName LIKE @firstname + '%';
                END
             ELSE
                IF @firstname IS NULL
                    BEGIN
                    SELECT p.BusinessEntityID
                        , p.Title
                        , p.FirstName
                        , p.MiddleName
                        , p.LastName
                        , p.Suffix
                        , pp.PhoneNumber
                        , ppt.PhoneType
                    FROM AdventureWorks2012.Person.Person AS p
                        INNER JOIN AdventureWorks2012.Person.PersonPhone AS pp
                            ON p.BusinessEntityID = pp.BusinessEntityID
                        INNER JOIN AdventureWorks2012.Person.PhoneNumberType AS ppt
                            ON pp.PhoneNumberTypeID = ppt.PhoneNumberTypeID
                    WHERE p.lastname LIKE @lastname + '%'
                        AND ppt.PhoneType LIKE @PhoneType + '%';
                    END;
                 ELSE
                    IF @lastname IS NULL
                        BEGIN
                        SELECT p.BusinessEntityID
                            , p.Title
                            , p.FirstName
                            , p.MiddleName
                            , p.LastName
                            , p.Suffix
                            , pp.PhoneNumber
                            , ppt.PhoneType
                        FROM AdventureWorks2012.Person.Person AS p
                            INNER JOIN AdventureWorks2012.Person.PersonPhone AS pp
                                ON p.BusinessEntityID = pp.BusinessEntityID
                            INNER JOIN AdventureWorks2012.Person.PhoneNumberType AS ppt
                                ON pp.PhoneNumberTypeID = ppt.PhoneNumberTypeID
                        WHERE p.FirstName LIKE @firstname + '%'
                            AND ppt.PhoneType LIKE @PhoneType + '%';
                        END;
                     ELSE
                        IF @PhoneType IS NULL
                            BEGIN
                            SELECT p.BusinessEntityID
                                , p.Title
                                , p.FirstName
                                , p.MiddleName
                                , p.LastName
                                , p.Suffix
                                , pp.PhoneNumber
                                , ppt.PhoneType
                            FROM AdventureWorks2012.Person.Person AS p
                                INNER JOIN AdventureWorks2012.Person.PersonPhone AS pp
                                    ON p.BusinessEntityID = pp.BusinessEntityID
                                INNER JOIN AdventureWorks2012.Person.PhoneNumberType AS ppt
                                    ON pp.PhoneNumberTypeID = ppt.PhoneNumberTypeID
                            WHERE p.FirstName LIKE @firstname + '%'
                                AND p.LastName LIKE @lastname + '%';
                            END;
                         ELSE
                            BEGIN
                            SELECT p.BusinessEntityID
                                , p.Title
                                , p.FirstName
                                , p.MiddleName
                                , p.LastName
                                , p.Suffix
                                , pp.PhoneNumber
                                , ppt.PhoneType
                            FROM AdventureWorks2012.Person.Person AS p
                                INNER JOIN AdventureWorks2012.Person.PersonPhone AS pp
                                    ON p.BusinessEntityID = pp.BusinessEntityID
                                INNER JOIN AdventureWorks2012.Person.PhoneNumberType AS ppt
                                    ON pp.PhoneNumberTypeID = ppt.PhoneNumberTypeID
                            WHERE p.FirstName LIKE @firstname + '%'
                                AND p.LastName LIKE @lastname + '%'
                                AND ppt.PhoneType LIKE @PhoneType + '%';
                            END;
END;
GO

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)