Solved

Inclusive IN Statement

Posted on 2011-03-10
10
515 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:xDJR1875
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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now