Where MyField IN (@ListOfChoices) ?

Posted on 2007-10-17
Last Modified: 2007-10-18
I need a SP that I can pass in vars for use in IN statements as follows:

ALTER PROCEDURE [sp_SearchSpots]
      -- Add the parameters for the stored procedure here
      @VisaStatus VARCHAR(50),
      @TypeOfPosition VARCHAR(50)
SELECT * FROM Spots WHERE VisaStatus IN(@VisaStatus) AND TypeOfPosition IN (@TypeOfPosition)

How can I get this to work?
Question by:alivemedia
    LVL 17

    Expert Comment

    Will you be passing multiple values in to @VisaStatus and @TypeOfPosition?
    LVL 15

    Accepted Solution

    Assuming there are multiple values in your values, and assuming comma-delimited data (e.g. @VisaStatus = 'status1, status2, status3'), and the same for @TypeOfPosition, you can use the function below. If that is not the case, just change "IN (@VisaStatus)" to "= @VisaStatus"

    To make your code work with the function, change the SELECT statement to:
    SELECT *
    FROM Spots
    WHERE VisaStatus IN dbo.SplitList(@VisaStatus, ',')
    AND TypeOfPosition IN dbo.SplitList(@TypeOfPosition, ',')

    CREATE  FUNCTION SplitList (@List VARCHAR(4000), @Delimiter CHAR(1) = ',')
           RETURNS @tblList TABLE (ListItem VARCHAR(100), UNIQUE CLUSTERED (ListItem))
          DECLARE @pos INT
          DECLARE @ListItem VARCHAR(4000)
          SET @List = LTRIM(RTRIM(@List))
          IF RIGHT(@List, 1) = @Delimiter
              SET @List = LEFT(@List, Len(@List) - 1)
          SET @pos = CHARINDEX(@Delimiter, @List)

          WHILE @pos > 0
                SET @ListItem = LEFT(@List, CHARINDEX(@Delimiter, @List) - 1)
                INSERT INTO @tblList (ListItem) VALUES(RTRIM(LTRIM(@ListItem)))
                SET @List = SUBSTRING(@List, @pos + 1, LEN(@List))
                SET @pos = CHARINDEX(@Delimiter, @List)
    LVL 2

    Author Comment

    I spoke to soon, when I try to run this I get the following error:

    Msg 4121, Level 16, State 1, Procedure sp_SearchSpots, Line 12
    Cannot find either column "dbo" or the user-defined function or aggregate "dbo.SplitList", or the name is ambiguous.

    This function gets created under Functions > Table-valued Functions in SQL Sever 2005 Management Studio if that helps.
    LVL 15

    Expert Comment

    Uncertain what the problem might be. I'm on 2000, and that is where you posted the question. I know it works in 2000, because I am using it in several places.

    In SQL Server 2000, it is required to prefix the function with the owner. You might try it without and see if that works.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now