• SQLPirate - Tuesday, October 17, 2017 10:32 AM

    One option is to pass an array in and then split it and search based on the values resulting from that.
    But I'd probably use a table valued parameter.

    So you'd create your new table type
    CREATE TYPE LocList AS TABLE (LocName varchar(25))

    Then you'd have your procedure (I changed #T to a permanent table still called T)
    CREATE PROCEDURE [dbo].[get_loc]
    @loclist LocList READONLY
    AS

    SELECT LocName
    FROM T
    WHERE LocName IN (SELECT LocName FROM @loclist)

    Testing it with the following would return a LocName of "Atlanta"
    DECLARE @loctemp LocList
    INSERT INTO @loctemp VALUES ('Atlanta')

    EXEC [get_loc] @loctemp

    This would get you "Boston" and "New York"
    DECLARE @loctemp LocList
    INSERT INTO @loctemp VALUES ('Boston'), ('New York')

    EXEC [get_loc] @loctemp

    For an ALL option you would pass the whole list in the parameter or maybe only pass the word "All" in the parameter and change the logic to check for that single value and return everything.

    Just be aware that while this works, it can throw the optimizer for a loop, because the table variable that results within the stored procedure will ALWAYS be assumed to have exactly 1 row by the optimizer.   You would be better served performance wise by passing a delimited string and using Jeff Moden's DelimitedSplit8K inline table-valued function to split that string into a table of values within the stored procedure.   Jeff's code comes at the end of an article that does a phenomenal job of explaining exactly how it works.    The article can be found here and is well worth reading:

    http://www.sqlservercentral.com/articles/72993/

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