Inclusive IN Statement

Hi everyone,

We have a stored procedure that currently takes in a @USERID parameter of type text.  The stored procedure has a WHERE clause based on that incoming parameter.  For example:

SELECT First, Last, DOB
FROM Staff
WHERE User = @USERID

The system that passes the parameter @USERID now needs to be expanded to support multiple possiblities for the @USERID.  For example, it previously might have been:

USER1 or USER2 or USER3

Now, we have a situation where we need to show records for multiple users.  For example:

USER1 AND USER2
USER2 AND USER3
USER1 AND USER2 AND USER3

We were going to attack this by having the string parameter come in as a comma-separated string.  For example:

USER1, USER2
USER2, USER3
USER1, USER2, USER3

We tried changing the query above to be as follows:

SELECT First, Last, DOB
FROM Staff
WHERE User IN (@USERID)

However, that doesn't work.  Can't seem to directly use a parameter within a IN statement.  Furthermore, the problem with an IN statement is it hits on the first true value and ignores the rest of the options.  We need a way to take in a comma separated text parameter of varying length, then evaluate and return records that match for each and every option in the string.

Any ideas?
dpmoneyAsked:
Who is Participating?
 
lcohanConnect With a Mentor Database AnalystCommented:
declare @USERID varchar(max);
set @USERID = 'user1,user2,user3,...';

--if you have already your @USERID = 'user1,user2,user3,...'
--why complicate things and not running something like:

declare @sqlstr varchar(max);
set @sqlstr = 'SELECT First, Last, DOB FROM Staff WHERE [User] IN (' + @USERID + ');'
print @sqlstr;
--exec @sqlStr;

just uncomment last exec to run it
0
 
Ephraim WangoyaCommented:

You have to parse the parameter into a table of individual records then use the records for your filter
Take a look at this post

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_26872268.html?cid=1060
0
 
Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:
a method I have used to solve this puzzle is to create a sql spllit function that returns the members of the split as a table.

In this case you would call the function as such

SELECT DATA From dbName.dbo.fn_Split(@USERID,',') WHERE DATA Is Not Null

Something like that... You could then join the results of that to your other table or pull that data into temptable to join to the STAFF table.
JOIN STAFF ON STAFF.User = DATA



CREATE FUNCTION [dbo].[fn_Split]
(    
    @RowData NVARCHAR(MAX),
    @Delimeter NVARCHAR(MAX)
)
RETURNS @RtnValue TABLE 
(
    ID INT IDENTITY(1,1),
    Data NVARCHAR(MAX)
) 
AS
BEGIN 
    DECLARE @Iterator INT
    SET @Iterator = 1

    DECLARE @FoundIndex INT
    SET @FoundIndex = CHARINDEX(@Delimeter,@RowData)

    WHILE (@FoundIndex>0)
    BEGIN
        INSERT INTO @RtnValue (data)
        SELECT 
            Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)))

        SET @RowData = SUBSTRING(@RowData,
                @FoundIndex + DATALENGTH(@Delimeter) / 2,
                LEN(@RowData))

        SET @Iterator = @Iterator + 1
        SET @FoundIndex = CHARINDEX(@Delimeter, @RowData)
    END
    
    INSERT INTO @RtnValue (Data)
    SELECT Data = LTRIM(RTRIM(@RowData))

    RETURN
END

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
dpmoneyAuthor Commented:
Interestingly enough, I have a SPLIT function (dbo.split) that parses the incoming string into a temp table of sorts that I can then query.  

So...USER1, USER2, USER3 becomes

USER1
USER2
USER3

However, same problem, when I do:

SELECT First, Last, DOB
FROM Staff
WHERE User IN (Select * from dbo.split(@USERID))

I have the same issue, the IN in "WHERE User IN" only hits on the first value it finds records for (USER1) and ignores USER2 and USER3.  I need to be inclusive of all items in the passed string of values.  

Other queries with WHERE X IN (A,B,C) returns records that match on A, B, AND C.  This is only returning records on A and ignorming B and C.
0
 
Ephraim WangoyaCommented:

Use the field name returned from the function or exists

SELECT First, Last, DOB
FROM Staff
WHERE User IN (Select USER
                           from dbo.split(@USERID))

or

SELECT First, Last, DOB
FROM Staff
WHERE exists (select 1
                        from dbo.split(@USERID) A
                        where A.USER = Staff.User )
0
 
dpmoneyAuthor Commented:
Good feedback, but not working.  I tried both implementations and they are ignoring all values except the first in the comma separated string.  This only happens when I am calling the derived table in the:

WHERE User IN (Select USER
                           from dbo.split(@USERID))

Here are my results:

USER1, USER2     >>>> I only get values for USER1
USER2, USER1     >>>> I only get values for USER2

I need to get values for both.  This is very strange.  I like the idea of not using Select *.  The function returns the column as "items" so I changed to Select items and it still works as described above.

0
 
Ephraim WangoyaCommented:

interesting, can you select from your function to verify the values returned are correct. no extra spaces or commas

select dbo.split(@USERID)

0
 
lcohanDatabase AnalystCommented:
And BTW - text,ntext are deprecated data types 2005 and higher therefor I suggest better get used to varchar(max)/nvarchar(max) instead:


http://msdn.microsoft.com/en-us/library/ms187993.aspx
0
 
Ephraim WangoyaCommented:
or trim the field

WHERE User IN (Select RTRIM(LTRIM(USER ))
                           from dbo.split(@USERID))
0
 
dpmoneyAuthor Commented:
Thanks!
0
All Courses

From novice to tech pro — start learning today.