Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Where MyField IN (@ListOfChoices) ?

Posted on 2007-10-17
Medium Priority
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
  • 2
LVL 17

Expert Comment

by:Chris Mangus
ID: 20097558
Will you be passing multiple values in to @VisaStatus and @TypeOfPosition?
LVL 15

Accepted Solution

dbbishop earned 2000 total points
ID: 20097707
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:
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)

Author Comment

ID: 20100640
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

ID: 20101051
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.

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Suggested Courses

581 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