Unable to capture the output of the query in a variable

Question

I am basically trying to store the output of the query in a variable but getting the error message

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

--DECLARE @companystring varchar(max)= '867343,867358,867443,867458,867543'

    DECLARE @companystring varchar(max)
    set @companystring = (SELECT DISTINCT Top 1000(mc.companyId) FROM ciqMarketCap mc 
    JOIN ciqCompany c   ON c.companyid = mc.companyid
    WHERE c.companyStatusTypeId NOT IN (5,6) AND  c.companyTypeId IN (1,4))


    select 
            ci.id
        into #companyId
        from dbo.splitstring(@companystring) ci

Could somebody tell me what the problem is ?

Your query doesn't even have a pretense of trying to return a single row. I have no idea what you really want to do, but a temporary table comes to mind. — Gordon Linoff, Jun 15 '17 at 15:50
Well, your subquery returns 1000 rows and you can only return 1 in a subquery (hence the error that returning more than 1 value in a subquery is not permitted). Are you actually trying to stuff 1000 records into 1 string? — Jacob H, Jun 15 '17 at 15:50
basically what I am trying to do is alternative to the commented declare statement in my updated post. I need 1000 company ids stored in that variable — Tom, Jun 15 '17 at 15:57
https://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — shibormot, Jun 15 '17 at 16:09
Well, the problem is that you can't insert a multi-row result set into a scalar variable. [Table variables exist](https://stackoverflow.com/questions/3838240/select-into-a-table-variable-in-t-sql) to support saving a result set into a variable, but a) they're almost identical to temporary tables, and b) you immediately dump the variable into a temp table. — Bacon Bits, Jun 15 '17 at 16:16
My question would be why you're bothering with the variable at all. Why not replace the whole thing with this: `SELECT DISTINCT Top 1000 (mc.companyId) as id INTO #companyId FROM ciqMarketCap mc JOIN ciqCompany c ON c.companyid = mc.companyid WHERE c.companyStatusTypeId NOT IN (5,6) AND c.companyTypeId IN (1,4)`. Is `ciqMarketCap.companyId` already a comma delimited list or something? Also, as always, TOP without an ORDER BY is not meaningful because tables are *unordered* sets. — Bacon Bits, Jun 15 '17 at 16:16

score 1 · Answer 1 · answered Jun 15 '17 at 16:28

If i understand you right and you need to convert column data to string, you can use next query -

DECLARE @companystring nvarchar(max)

SELECT TOP 1000 @companystring = coalesce(@companystring+';','') + CAST( mc.companyId AS nvarchar)
FROM ciqMarketCap mc 
JOIN ciqCompany c   ON c.companyid = mc.companyid
WHERE c.companyStatusTypeId NOT IN (5,6) AND  c.companyTypeId IN (1,4)

SELECT @companystring

score 1 · Answer 2 · answered Jun 20 '17 at 16:13

First, it's not ideal to store large dataset into nvarchar/varchar variables. The appropriate step would be to create a temporary table variable. See edited query below:

DECLARE @companystring TABLE(companyId nvarchar(max));

INSERT INTO @companystring(companyId)
SELECT 
 DISTINCT Top 1000(mc.companyId) 
FROM ciqMarketCap mc 
JOIN ciqCompany c ON c.companyid = mc.companyid
WHERE c.companyStatusTypeId NOT IN (5,6) 
AND c.companyTypeId IN (1,4);

select ci.companyId into #companyId from @companystring ci;

Unable to capture the output of the query in a variable

2 Answers2