locked
SQL "IN" statement RRS feed

  • Question

  • I am trying to pass a string value to a SQL stored proc.

    Here is some pieces of the statement...

    WHERE Forms.Type IN (@type)

    @type = 'In Progress', 'Pending', 'Accepted'

    Can anyone tell me why that parameter does not produce a result set but this does?

    WHERE Forms.Type IN ('In Progress', 'Pending', 'Accepted')

    The later statement will return results but the former will not...??
    Tuesday, October 13, 2009 12:41 AM

Answers

  • Here is my suggestion. Create a function to seperate your values as follows

    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
    
    
    Then, pass this to your stor proc, WHERE Type IN (SELECT item FROM ([dbo].[CommaSeparatedList](@Type))


    Abdallah, PMP, MCTS
    • Marked as answer by parri2bd Tuesday, October 13, 2009 3:16 PM
    Tuesday, October 13, 2009 1:41 PM
  • Consider splitting the delimited string in a table-valued function that you can use as a IN subquery or JOIN.  See http://www.sommarskog.se/arrays-in-sql-2005.html.
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Naomi N Sunday, January 15, 2012 7:51 AM
    Tuesday, October 13, 2009 1:01 AM
  • You don't need dynamic SQL here.  The untested example below shows a parameterized version of your query:

    CREATE PROCEDURE [dbo].[Forms_Select_All]
        @status                nvarchar(150) = null,
        @type                nvarchar(150) = null,
        @name                nvarchar(150) = null,
        @start                datetime = null,
        @end                datetime = null

    AS
    DECLARE @sqlExec varchar(8000)

    SELECT        Companies.Company, UserData.LastName, UserData.MiddleName,
                                UserData.FirstName, FormType.Name, Forms.Status, Forms.StartDate,
                                Forms.EndDate, Forms.History, Forms.FormId, Forms.Id
                    FROM        aspnet_Users INNER JOIN
                                Forms ON aspnet_Users.UserId = Forms.UserId INNER JOIN
                                UserData ON aspnet_Users.UserId = UserData.UserId INNER JOIN
                                Companies ON UserData.DomainId = Companies.DomainId INNER JOIN
                                FormType ON FormType.Type = Forms.FormType
                    WHERE        UserData.LastName LIKE ISNULL(@name, UserData.LastName)
                                AND Forms.Status IN (ISNULL(@status, Forms.Status))
                                AND Forms.FormType IN (SELECT Value FROM fn_Split(@type, ','))
                                AND Forms.StartDate <= DATEADD(year, DATEDIFF(year, '', ISNULL(@end, Forms.StartDate)) + 1, '')
                                AND Forms.EndDate >= DATEADD(year, DATEDIFF(year, '', ISNULL(start, Forms.EndDate)), '')


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Kalman Toth Tuesday, January 17, 2012 4:10 PM
    Tuesday, October 13, 2009 12:51 PM
  • You may want to pass your parameters as comma-delimited string, then split them using any of the available Split functions (check this thread http://forum.lessthandot.com/viewtopic.php?f=17&t=7566 ) and then just INNER JOIN with the function results.
    Premature optimization is the root of all evil in programming.

    Donald Knuth, repeating C. A. R. Hoare

    My blog
    • Marked as answer by Kalman Toth Tuesday, January 17, 2012 4:10 PM
    Tuesday, October 13, 2009 1:03 AM

All replies

  • You need to seperate your values before passing them to your sp. Which version of SQL Server you are using?
    Abdallah, PMP, MCTS
    Tuesday, October 13, 2009 12:45 AM
  • The above IN format would work in SQL Report Services but not in SQL. You need to use Dynamic SQL to achieve this. Eg:

     

    declare @sql varchar(8000),@type varchar(1000)
    set @type = '''In Progress'', ''Pending'', ''Accepted'''
    set @sql = 'SELECT * FROM Forms WHERE Forms.Type IN ('+@type+')'
    EXEC (@sql)

     

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: Troubleshooting SQL
    • Proposed as answer by Phil Brammer Tuesday, October 13, 2009 3:34 AM
    • Unproposed as answer by Naomi N Sunday, January 15, 2012 7:50 AM
    Tuesday, October 13, 2009 12:55 AM
  • Consider splitting the delimited string in a table-valued function that you can use as a IN subquery or JOIN.  See http://www.sommarskog.se/arrays-in-sql-2005.html.
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Naomi N Sunday, January 15, 2012 7:51 AM
    Tuesday, October 13, 2009 1:01 AM
  • You may want to pass your parameters as comma-delimited string, then split them using any of the available Split functions (check this thread http://forum.lessthandot.com/viewtopic.php?f=17&t=7566 ) and then just INNER JOIN with the function results.
    Premature optimization is the root of all evil in programming.

    Donald Knuth, repeating C. A. R. Hoare

    My blog
    • Marked as answer by Kalman Toth Tuesday, January 17, 2012 4:10 PM
    Tuesday, October 13, 2009 1:03 AM
  • If you don't want to use dynamic sql, create a function and the you can use something like this

    SELECT Col1,Col2 FROM Table WHERE Forms.Type IN (SELECT columns from dbo.CommaSeperatedList(@type)).

    There are multiple websites that show how to create a comma seperated list function in case you need it.
    If you still need any help let me know and I can help you out.



    Abdallah, PMP, MCTS
    Tuesday, October 13, 2009 1:07 AM
  • Here is the cull stored proc.

    I can't seem to get it to work with a datetime variable.  It throws an error saying that it cannot convert datetime to character string.

    CREATE PROCEDURE [dbo].[Forms_Select_All]
        @status                nvarchar(150) = null,
        @type                nvarchar(150) = null,
        @name                nvarchar(150) = null,
        @start                datetime = null,
        @end                datetime = null

    AS
    DECLARE @sqlExec varchar(8000)

    SET @sqlExec = 'SELECT        Companies.Company, UserData.LastName, UserData.MiddleName,
                                UserData.FirstName, FormType.Name, Forms.Status, Forms.StartDate,
                                Forms.EndDate, Forms.History, Forms.FormId, Forms.Id
                    FROM        aspnet_Users INNER JOIN
                                Forms ON aspnet_Users.UserId = Forms.UserId INNER JOIN
                                UserData ON aspnet_Users.UserId = UserData.UserId INNER JOIN
                                Companies ON UserData.DomainId = Companies.DomainId INNER JOIN
                                FormType ON FormType.Type = Forms.FormType
                    WHERE        UserData.LastName LIKE ISNULL(' + @name + ', UserData.LastName)
                                AND Forms.Status IN (ISNULL(' + @status + ', Forms.Status))
                                AND Forms.FormType IN (SELECT Value FROM fn_Split(' + @type + ', '',''))
                                AND YEAR(Forms.StartDate) <= YEAR(ISNULL(' + @end + ', Forms.StartDate))
                                AND YEAR(Forms.EndDate) >= YEAR(ISNULL(' + @start + ', Forms.EndDate))'

    EXEC(@sqlExec)
    Tuesday, October 13, 2009 12:29 PM
  • You don't need dynamic SQL here.  The untested example below shows a parameterized version of your query:

    CREATE PROCEDURE [dbo].[Forms_Select_All]
        @status                nvarchar(150) = null,
        @type                nvarchar(150) = null,
        @name                nvarchar(150) = null,
        @start                datetime = null,
        @end                datetime = null

    AS
    DECLARE @sqlExec varchar(8000)

    SELECT        Companies.Company, UserData.LastName, UserData.MiddleName,
                                UserData.FirstName, FormType.Name, Forms.Status, Forms.StartDate,
                                Forms.EndDate, Forms.History, Forms.FormId, Forms.Id
                    FROM        aspnet_Users INNER JOIN
                                Forms ON aspnet_Users.UserId = Forms.UserId INNER JOIN
                                UserData ON aspnet_Users.UserId = UserData.UserId INNER JOIN
                                Companies ON UserData.DomainId = Companies.DomainId INNER JOIN
                                FormType ON FormType.Type = Forms.FormType
                    WHERE        UserData.LastName LIKE ISNULL(@name, UserData.LastName)
                                AND Forms.Status IN (ISNULL(@status, Forms.Status))
                                AND Forms.FormType IN (SELECT Value FROM fn_Split(@type, ','))
                                AND Forms.StartDate <= DATEADD(year, DATEDIFF(year, '', ISNULL(@end, Forms.StartDate)) + 1, '')
                                AND Forms.EndDate >= DATEADD(year, DATEDIFF(year, '', ISNULL(start, Forms.EndDate)), '')


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by Kalman Toth Tuesday, January 17, 2012 4:10 PM
    Tuesday, October 13, 2009 12:51 PM
  • Dan -

    If you take a look in the initial posting, there was an issue with the statement

    WHERE .. Forms.Type IN (ISNULL(@type, Forms.Type)) ..

    This piece of the statement does not allow a nvarchar variable that is delineated by commas to be accepted.

    if @type = 'In Progress', 'Accepted', 'Rejected', 'Approved' this stored proc does not work.  It does not throw any errors, but it doesn't find a result set either.

    However, when I just run a Query with -  WHERE .. Forms.Type IN ('In Progress', 'Accepted', 'Rejected', 'Approved') it produces a result set, so I am trying to figure out where I am going wrong with the parameter that I am sending through to the stored procedure
    Tuesday, October 13, 2009 1:15 PM
  • Here is my suggestion. Create a function to seperate your values as follows

    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
    
    
    Then, pass this to your stor proc, WHERE Type IN (SELECT item FROM ([dbo].[CommaSeparatedList](@Type))


    Abdallah, PMP, MCTS
    • Marked as answer by parri2bd Tuesday, October 13, 2009 3:16 PM
    Tuesday, October 13, 2009 1:41 PM
  • Abdshall -

    Thanks for all of the help!

    Worked perfectly with that function.
    Tuesday, October 13, 2009 3:17 PM