Attempting to use a subquery as an expression, need alternatives / help

SQL Server 2005.

I have a stored procedure which accepts a few parameters that may or may开发者_如何学编程 not actually have data passed to them. So they may come through as an empty string, or they may come through with data.

The data would be a delimited list of IDs... manufacturer ID, catalog ID, etc. The proc searches through records, and if any of those parameters have data, it's used in the where clause.

FYI: Split is a function that splits a string (accepts a delimiter) and returns a table.

WHERE

...

AND m.Mfg_ID in (CASE WHEN @manufacturerIds <> '' THEN (SELECT * FROM Split(@manufacturerIds, '|')) ELSE (select m.Mfg_Id) END)

I get an error of: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

What I don't understand is that if I remove the CASE statement, so that I just have this...

WHERE

...

AND m.Mfg_ID in (SELECT * FROM Split(@manufacturerIds, '|'))

... it runs OK; however, this keeps me from being able to pass an empty string.

Any help would be much appreciated!

The difference is that (select m.Mfg_Id) returns a single row, but (select * from split(...)) returns multiple rows. A case returns a single value, so it doesn't know what to do with the multiple rows from Split.

Instead of a case you could union them together:

and m.Mfg_ID in 
(
select  *
from    Split(@manufacturerIds, '|')
where   @manufacturerIds <> ''
union all
select  m.Mfg_Id
where   @manufacturerIds = ''
)

The bottom half of the union only runs when @manufacturerIds = ''.

SELECT m.Mfg_ID -- , other columns
    FROM dbo.base_table_name AS m
    LEFT OUTER JOIN dbo.Split(@ManufacturerIds) AS f
    ON (m.Mfg_id = f.column_name)
    WHERE @manufacturerIds = '' 
    OR f.column_name IS NOT NULL;

I think it may be something to do with the 'SELECT * ' part of the CASE subquery. Try selecting only one column instead.

I have tried to rewrite your "where" clause to something that seems more logical to me. Instead of splitting the text into smaller bits, i compare the text with "LIKE". I assume that would give better performance.

DECLARE @t table (Mfg_id VARCHAR(10))
DECLARE @manufacturerIds VARCHAR(50)
INSERT @t 
SELECT 'aaa' UNION ALL SELECT 'a' UNION ALL SELECT 'ab' UNION ALL SELECT 'abc'

SET @manufacturerIds = '|' +'aaa|abc|bbb' + '|'

SELECT Mfg_ID matching FROM @t m
WHERE ( @manufacturerIds like '%|' + CAST(m.Mfg_ID AS VARCHAR(20)) + '|%' OR @manufacturerIds = '')

Result:

matching
----------
aaa
abc

If you just want your corrent script corrected here is how you could do it:

AND (@manufacturerIds = '' or m.Mfg_ID in (SELECT * FROM Split(@manufacturerIds, '|')))