Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Inclusive IN Statement

Posted on 2011-03-10
10
Medium Priority
?
524 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:dpmoney
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35099527

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
 
LVL 17

Expert Comment

by:Daniel Reynolds
ID: 35099594
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
 

Author Comment

by:dpmoney
ID: 35099638
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35099679

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
 

Author Comment

by:dpmoney
ID: 35099771
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
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 35099864
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
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35099886

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

select dbo.split(@USERID)

0
 
LVL 40

Expert Comment

by:lcohan
ID: 35099898
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
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35099905
or trim the field

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

Author Closing Comment

by:dpmoney
ID: 35099928
Thanks!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

916 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