Solved

Inclusive IN Statement

Posted on 2011-03-10
10
518 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:ewangoya
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 32

Expert Comment

by:ewangoya
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 39

Accepted Solution

by:
lcohan earned 500 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:ewangoya
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 39

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:ewangoya
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

856 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