CODEWITHSUNDEEP
t-sqlsql-server-2014
S M
S M

Reputation: 159

Error while trying to run SQL queries with one variable having values with comma as a string

I am getting the error in the below sql queries. For osdsId variable, I will get values as a list from the UI. That's why I hardcoded the value for testing. But it is displaying error as 'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.' However, it works if I just assign just one value. Thank you. 

 declare @osdsId VARCHAR(max) = '4292, 4293',
 @pqrId VARCHAR(max) = NULL,
 @queryOrderBy VARCHAR(max) = 'DATE_INSERTED ASC',
 @rowLimit   INT = 0,
 @startRow   INT = 0,
 @endRow     INT = 0

SELECT * FROM (
        SELECT
            ROW_NUMBER() OVER (ORDER BY @queryOrderBy ) AS ROWNUM,
            S.OSDS_ID, 
            S.PQR_ID, 
            S.DATE_INSERTED, 
            S.BUY_CURRENCY, 
            S.SELL_CURRENCY, 
            S.BUY_EXCHANGE_RATE, 
            S.SELL_EXCHANGE_RATE, 
            S.BUY_PERCENT, 
            S.SELL_PERCENT
        FROM
            table1 S
        WHERE
            1=1
            AND S.OSDS_ID IN (COALESCE((SELECT TXT_VALUE FROM 
DBO.FN_PARSETEXT2TABLE_TEXTONLY(@osdsId, ',') ), S.OSDS_ID)) 
            AND S.PQR_ID IN (COALESCE((SELECT TXT_VALUE FROM 
DBO.FN_PARSETEXT2TABLE_TEXTONLY(@pqrId, ',') ), S.PQR_ID)) 
        )x
        WHERE ROWNUM BETWEEN  
        CASE WHEN (@rowLimit > 0) THEN @startRow ELSE ROWNUM END
        AND CASE WHEN (@rowLimit > 0) THEN @endRow ELSE ROWNUM END

Upvotes: 0

Views: 47

Answers (1)

Colin G
Colin G

Reputation: 165

I believe it's because your FN_PARSETEXT2TABLE_TEXTONLY is returning a table, but COALESCE expects its arguments to be a single value. So, when two values are returned from the table, that's where the error comes from. You could add a TOP 1, but that would defeat the purpose.

What I would do: declare a table variable and run the UDF on it outside of your query. It's inefficient to run that within a sub-query anyway since it's consistent throughout execution.

I'm not clear on why you are using the COALESCE though. Is that for if the parsing function fails and returns a NULL, you still want it to return something? Because it will return everything in that case.

So, assuming that your FN_PARSETEXT2TABLE_TEXTONLY returns a table with a single integer column:

 declare @osdsId VARCHAR(max) = '4292, 4293',
 @pqrId VARCHAR(max) = NULL,
 @queryOrderBy VARCHAR(max) = 'DATE_INSERTED ASC',
 @rowLimit   INT = 0,
 @startRow   INT = 0,
 @endRow     INT = 0,
 @osdsTbl TABLE (oid INT),
 @pqrTbl TABLE (pid INT);

SET @osdsTbl = DBO.FN_PARSETEXT2TABLE_TEXTONLY(@osdsId, ',');
SET @pqrTbl = DBO.FN_PARSETEXT2TABLE_TEXTONLY(@pqrId, ',');

SELECT * FROM (
        SELECT
            ROW_NUMBER() OVER (ORDER BY @queryOrderBy ) AS ROWNUM,
            S.OSDS_ID, 
            S.PQR_ID, 
            S.DATE_INSERTED, 
            S.BUY_CURRENCY, 
            S.SELL_CURRENCY, 
            S.BUY_EXCHANGE_RATE, 
            S.SELL_EXCHANGE_RATE, 
            S.BUY_PERCENT, 
            S.SELL_PERCENT
        FROM
            table1 S
        WHERE
            1=1
            AND (@osdsId IS NULL OR (@osdsId IS NOT NULL AND S.OSDS_ID IN (SELECT * FROM @osdsTbl))
            AND (@pqrId IS NULL OR (@pqrId IS NOT NULL AND S.PQR_ID IN (SELECT * FROM @pqrTbl))
        )x
        WHERE ROWNUM BETWEEN  
        CASE WHEN (@rowLimit > 0) THEN @startRow ELSE ROWNUM END
        AND CASE WHEN (@rowLimit > 0) THEN @endRow ELSE ROWNUM END

Upvotes: 1

Related Questions