CODEWITHSUNDEEP
sqlsql-serverstringstored-procedures
erasmo carlos
erasmo carlos

Reputation: 682

how to format string variable to be used in an IN operator

This question seems to have been asked several times, and I apologize for having to open another question with a redundant inquiry.

I am struggling implementing every solution I find online regarding this topic.

I have written a SSRS report that passes a string with multiple values to a sql stored procedure.

The string with values look like: 'Ambulance, Metro, Stickers, Gardens'

The stored procedure uses dynamic sql because of some conditions that need to be met in order to run.

The string of values coming from the report, need to be used in an IN operator, but every solution I have tried to implement I get the error: 

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

ALTER PROCEDURE [dbo].[WIP_OnlineInformation]
(
    @s_Group    NVARCHAR(500) = 'ABC,DEF, GHI'
,   @s_Type     NVARCHAR(500) = 'Garden, Metro, Parks, Ambulance'
)

DECLARE @t_Group_Type TABLE
(
    _Group NVARCHAR(500)
,   _Type NVARCHAR(500)
);

INSERT INTO @t_Group_Type   
SELECT DISTINCT 
    [Group] = (SELECT * FROM dbo.Split(@s_TransactionGroup)) 
,   [Type] = (SELECT * FROM dbo.Split(@s_TransactionType))

And that is it, I can not make it pass the error mentioned above.

Here are the contents of the UDF:

ALTER FUNCTION [dbo].[Split](@String NVARCHAR(2000))
RETURNS @List TABLE(ID VARCHAR(100))
AS
    BEGIN
        WHILE CHARINDEX(',', @String) > 0
        BEGIN
            INSERT INTO @List (ID)
            VALUES(LEFT(@String, CHARINDEX(',', @String) - 1));
            SET @String = RIGHT(@String, LEN(@String) - CHARINDEX(',', @String));
         END;
     INSERT INTO @List (ID)
     VALUES(@String);
     RETURN;
 END;

This is the dynamic query in which I would like to use the values:

SET @s_SQL =
    'SELECT' + CHAR(13) + CHAR(10) +        
    '   pu.Source' + CHAR(13) + CHAR(10) +
    --',    ' + @s_ColumnName  + ' ColX ' + CHAR(13) + CHAR(10) +
    ',  pu.Account' + CHAR(13) + CHAR(10) +
    ',  pu.Name' + CHAR(13) + CHAR(10) +
    ',  pu.Amount'+ CHAR(13) + CHAR(10) +       
    ',  pu.Status' + CHAR(13) + CHAR(10) +
    ',  pu.Phone' + CHAR(13) + CHAR(10) +
    ',  pu.StreetAddress1' + CHAR(13) + CHAR(10) +
    ',  pu.StreetAddress2' + CHAR(13) + CHAR(10) +
    ',  pu.City' + CHAR(13) + CHAR(10) +
    ',  pu.[State]' + CHAR(13) + CHAR(10) +
    ',  pu.ZIP' + CHAR(13) + CHAR(10) +
    ',  pu.Email' + CHAR(13) + CHAR(10) +
    'FROM ' + CHAR(13) + CHAR(10) +
    '   vwCustomer pu WITH (NOLOCK) ' + CHAR(13) + CHAR(10) +
    ' WHERE'  + CHAR(13) + CHAR(10) +
    '   pu.Group IN (' + (SELECT * FROM dbo.SplitString(@s_TransactionGroup)) + ')'  + CHAR(13) + CHAR(10) +
    '   AND pu.ype IN (' + (SELECT * FROM dbo.SplitString(@s_TransactionType)) + ')' + CHAR(13) + CHAR(10)

Any help would be greatly appreciated.

Many thanks.

Upvotes: 1

Views: 220

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

You can fix your error by doing:

INSERT INTO @t_Group_Type   
    SELECT g.*, t.*
    FROM (SELECT * FROM dbo.Split(@s_TransactionGroup)) g CROSS JOIN
         (SELECT * FROM dbo.Split(@s_TransactionType)) t;

It is not clear if you want a Cartesian product, because you don't actually explain the results that you do want.

Upvotes: 2

Related Questions