locked
Use of "IN" with a function RRS feed

  • Question

  • I have an sp where I want to pass a list of items to search for.   Here's the simplified version:

    CREATE PROCEDURE [dbo].[SelectAvailableCourses]
     @TrainingCodes varchar(255)=''
    AS
    BEGIN
     SET NOCOUNT ON;

     SELECT tdfRequirements.RequirementD
     FROM tdfRequirements
     WHERE tdfRequirements.TrainingCode IN (@TrainingCodes)
    END

    Here's an example of how I'm calling it:

    DECLARE @return_value int
    EXEC @return_value = [dbo].[SelectAvailableCourses]
      @TrainingCodes = N'''RGS'',''RPC'''
    SELECT 'Return Value' = @return_value

    If I run:

     SELECT tdfRequirements.RequirementItemID
     FROM tdfRequirements
     WHERE tdfRequirements.TrainingCode IN ('RGS','RPC')

    Then I'll get results, but if I execute the sp using the parameter, then I don't get results (0 rows).  Any ideas?

     

    Friday, June 18, 2010 2:38 PM

Answers

  • You can create a function and then use it in your SP. Here is an example

    --Create Table
    CREATE TABLE TestTable(ID INT IDENTITY(1,1),Value VARCHAR(MAX))
    INSERT INTO TestTable
    SELECT 'Value1' UNION SELECT 'Value2' UNION SELECT 'Value3'
    
    
    --Create function
    CREATE FUNCTION [dbo].[CommaSeparatedList]
    (
     @cslist VARCHAR(8000)
    )
    RETURNS @t TABLE
    (
     Item VARCHAR(64)
    )
    BEGIN 
     DECLARE @spot SMALLINT, @str VARCHAR(8000), @sql VARCHAR(8000) 
     
     WHILE @cslist <> '' 
     BEGIN 
      SET @spot = CHARINDEX(',', @cslist) 
      IF @spot>0 
       BEGIN 
        SET @str = LEFT(@cslist, @spot-1) 
        SET @cslist = RIGHT(@cslist, LEN(@cslist)-@spot) 
       END 
      ELSE 
       BEGIN 
        SET @str = @cslist 
        SET @cslist = '' 
       END 
      INSERT @t SELECT @str
     END 
     RETURN
    END
    
    --Create stored procedure
    CREATE PROCEDURE dbo.RetrieveValues
    @TrainingCodes VARCHAR(MAX) = ''
    AS
    BEGIN
    	SELECT ID,Value
    	FROM TestTable
    	WHERE Value IN (SELECT item 
    			FROM [dbo].[CommaSeparatedList](@TrainingCodes))
    END
    
    
    
    --Test sp
    EXEC dbo.RetrieveValues 'Value1,Value2'

    Abdallah El-Chal, PMP, ITIL, MCTS
    • Marked as answer by Tremour101 Friday, June 18, 2010 4:59 PM
    Friday, June 18, 2010 3:30 PM

All replies

  • You cannot do that, if you want to use this, you will have to use dynamic sql.

     I have modified your query to use dynamic sql, this will give you correct result.

    CREATE PROCEDURE [dbo].[SelectAvailableCourses]
     @TrainingCodes varchar(255)=''
    AS
    BEGIN
     SET NOCOUNT ON;
     DECLARE @SQL nVarchar(4000)
    
     SET @SQL = 'SELECT tdfRequirements.RequirementD '+
    			' FROM tdfRequirements '+
    			' WHERE tdfRequirements.TrainingCode IN ('+@TrainingCodes')'
    Exec sp_ExecuteSQL @SQL
    
    END

    Regards, Deven ------------------------------------------------------------ Please vote if you find any of my post helpful.
    Friday, June 18, 2010 3:07 PM
  • You can create a function and then use it in your SP. Here is an example

    --Create Table
    CREATE TABLE TestTable(ID INT IDENTITY(1,1),Value VARCHAR(MAX))
    INSERT INTO TestTable
    SELECT 'Value1' UNION SELECT 'Value2' UNION SELECT 'Value3'
    
    
    --Create function
    CREATE FUNCTION [dbo].[CommaSeparatedList]
    (
     @cslist VARCHAR(8000)
    )
    RETURNS @t TABLE
    (
     Item VARCHAR(64)
    )
    BEGIN 
     DECLARE @spot SMALLINT, @str VARCHAR(8000), @sql VARCHAR(8000) 
     
     WHILE @cslist <> '' 
     BEGIN 
      SET @spot = CHARINDEX(',', @cslist) 
      IF @spot>0 
       BEGIN 
        SET @str = LEFT(@cslist, @spot-1) 
        SET @cslist = RIGHT(@cslist, LEN(@cslist)-@spot) 
       END 
      ELSE 
       BEGIN 
        SET @str = @cslist 
        SET @cslist = '' 
       END 
      INSERT @t SELECT @str
     END 
     RETURN
    END
    
    --Create stored procedure
    CREATE PROCEDURE dbo.RetrieveValues
    @TrainingCodes VARCHAR(MAX) = ''
    AS
    BEGIN
    	SELECT ID,Value
    	FROM TestTable
    	WHERE Value IN (SELECT item 
    			FROM [dbo].[CommaSeparatedList](@TrainingCodes))
    END
    
    
    
    --Test sp
    EXEC dbo.RetrieveValues 'Value1,Value2'

    Abdallah El-Chal, PMP, ITIL, MCTS
    • Marked as answer by Tremour101 Friday, June 18, 2010 4:59 PM
    Friday, June 18, 2010 3:30 PM
  • SQL Server does not support "lists" as a parameter type. You might consider using a table parameter, where the table contains the values.

    If you really want to understand how to deal with lists of values, please take a look at this post:

    http://www.sommarskog.se/arrays-in-sql.html

     


    HTH, Kalen Delaney www.SQLServerInternals.com
    • Proposed as answer by Naomi N Friday, June 18, 2010 3:35 PM
    Friday, June 18, 2010 3:34 PM
  • Thanks Abdshall, I was using a split function, but left it out of the example for brevity.  What I was missing was making the split function return a table instead of a string, and using the Select statement.
    Friday, June 18, 2010 5:01 PM
  • Thanks dbp2784, your solution was what I was using in the past, but I've often wondered if the use of dynamic sql construction in that manner prevents SQL Server from creating and storing an execution plan in advance, forcing it to re-generate the execution plan each time the query is run because it will never know what the query will look like until the parameters are passed.
    Friday, June 18, 2010 5:03 PM
  • Wow, what great article.  Thanks!
    Friday, June 18, 2010 5:26 PM