SQL Subquery returned more than 1 value

Question

My query causes the following error:

Msg 512, Level 16, State 1, Procedure Item_insupd, Line 17
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Query:

INSERT INTO [Total_Database].[dbo].[Item]
    (
        ItemID, 
        ItemNo,
        ItemDescription,
        Notes,
        StandardCost,
        SalesGLAccountID,
        ItemTypeID,
        Backorderable
    ) 
 (
    SELECT  [nr],
            [nr],
            [Latijn]+' '+[Subgroep]+' '+CAST([nr] as VARCHAR(255)),
            [NL]+' '+[Vorm]+' '+[Kenmerk]+' '+[Hoogte],[Inkoopprijs],
            (4),
            (case when [Productgroep]='PB' then 1 else 5 end),
            (1) 
    FROM    [ACCESDATA].[dbo].[Planten]
 );

I suspect this to happen because my subquery does not contain a WHERE, unfortunately I do not know how to construct a correct WHERE clause.

Answer 1

I suspect the problem is in this string (line 26 in your code):

IF NOT (EXISTS (SELECT G.GLAccountID FROM GLAccnt G INNER JOIN Inserted I ON G.GLAccountID = I.SalesGLAccountID))
OR ((SELECT I.COGSGLAccountID FROM Inserted I) IS NOT NULL) AND NOT (EXISTS (SELECT G.GLAccountID FROM GLAccnt G INNER JOIN Inserted I ON G.GLAccountID = I.COGSGLAccountID))

It looks like (SELECT I.COGSGLAccountID FROM Inserted I) return more than one row, so you're getting an error.

You're treating inserted as a one row table (for example, you're getting parameters from it like SELECT @ItemNo = I.ItemNo, @ItemDescription = I.ItemDescription FROM Inserted I, but inserted table can have more than one row. So in your case I think you have 3 options - check that there's only 1 row in inserted, rewrite trigger as set-based, or use cursor.

Here's sql fiddle with somewhat similar example.