none
SQL "IN" statement

    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/
    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/
    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
    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
    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/
    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
    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/
    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