Solved

Inclusive IN Statement

Posted on 2011-03-10
10
519 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql case statement to calculate totals 5 37
add stored proc on publlication 4 20
Find special characters using tSQL 6 19
SQL query 7 18
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

726 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