Error: SubQuery Returned More than One Value

Question

I've got the following piece of SQL Code that is giving me a titular error.

WHERE

        (SELECT Tokens FROM StringSplitter(@DocumentValue, '|', 1)) IN
        (SELECT Tokens FROM StringSplitter(@sortValue, '|', 1))

Where @DocumentValue and @sortValue are both concatenated strings separated by a delimiter (in this case, a '|').

The StringSplitter function returns a table of each individual string. e.g. Fox|Brown|SQL would return a table with three records: Fox, Brown, and SQL.

What I want to happen is to compare the two sets of strings, inserting them into another table (hence the WHERE) if any of the individual strings from @DocumentValue match any of the individual strings from @sortValue.

This implementation is flawed. If @DocumentValue and @sortValue ever contain more than one matching string, the query fails, with the given error.

That said, how could I fix this error considering I don't care which value matched, as long as I know whether or not at least one did?

Sql Server 2008

Answer 1

Try substituting INTERSECT for IN. Maybe something like:

declare @tokenTable table (token nvarchar(50) primary key)
insert into @tokenTable (select ...) INTERSECT (select ...)

Edit: Not immediately sure what I goofed up with that. The following works, though (note that I used tables for @documentValue and @sortValue because I don't have your splitter function):

declare @documentValue table(token nvarchar(50) primary key)
declare @sortValue table(token nvarchar(50) primary key)
declare @result table(token nvarchar(50) primary key)

insert into @documentValue (token) values ('A')
insert into @documentValue (token) values ('B')
insert into @documentValue (token) values ('C')
insert into @sortValue select * from @documentValue
delete from @sortValue where token = 'C'


insert into @result 
    select 
        A.* 
    from 
        @documentValue A
    inner join 
        @sortValue B 
    on 
        A.token = B.token
select * from @result

Answer 2

JOIN?

 (SELECT count(*)
  FROM StringSplitter(@DocumentValue, '|', 1) AS one JOIN
       StringSplitter(@sortValue, '|', 1) AS two ON one.Tokens = two.Tokens
 ) > 0

Answer 3

You're just missing a WHERE I think (or got it in the wrong place)

INSERT INTO YourTable
SELECT Tokens FROM StringSplitter(@DocumentValue, '|', 1)
WHERE Tokens IN (SELECT Tokens FROM StringSplitter(@sortValue, '|', 1))

Though @expedient's answer of INTERSECT should work fine as well

INSERT INTO YourTable
SELECT Tokens FROM StringSplitter(@DocumentValue, '|', 1)
INTERSECT
SELECT Tokens FROM StringSplitter(@sortValue, '|', 1)

Answer 4

<your action here>
WHERE EXISTS (SELECT 1 FROM dbo.StringSplitter(@DocumentValue, '|', 1)  AS a
                       JOIN dbo.StringSplitter(@sortValue, '|', 1)  AS b
                       ON a.Tokens = b.Tokens)

Answer 5

insert into yourNewTableGoesHere(Token)
select a.Tokens 
from StringSplitter(@DocumentValue, '|', 1) a
where exists (select Tokens 
                from StringSplitter(@sortValue, '|', 1) 
                where Tokens = a.Tokens)

Answer 6

Why not put your tokens in 2 temp tables

(SELECT Tokens INTO #DocTokens FROM StringSplitter(@DocumentValue, '|', 1)) IN 
(SELECT Tokens INTO #SortTokens FROM StringSplitter(@sortValue, '|', 1)) 

SELECT d.Tokens INTO #Tokens FROM #DocTokens d JOIN #SortTokens s ON s.Tokens = d.Tokens

This will give you only the matches. Then you can join in your query to #Tokens to filter your main select.