Sql Insert Error -"Subquery returned more than 1 value"

Question

In the below code i have pass multiple values with comma Separated to @i_CustomerGroupID and one value to @i_LocationID.In which i face a issue "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.".Pls help me to solve the issue.

ALTER PROCEDURE [dbo].[spInsertCustomerGroupLocationMap]
    -- Add the parameters for the stored procedure here
    @i_LocationID int,
    @i_CustomerGroupID varchar(100)

    --WITH ENCRYPTION
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF NOT EXISTS (SELECT 1 FROM CustomerGroupLocationMap WHERE LocationID = @i_LocationID AND CustomerGroupID = @i_CustomerGroupID)
    BEGIN

        INSERT INTO CustomerGroupLocationMap (LocationID, CustomerGroupID) VALUES (@i_LocationID, (SELECT * FROM dbo.CSVToTable(@i_CustomerGroupID)));

    END
END

Gordon Linoff · Accepted Answer · 2014-07-18 13:59:02Z

2

You want to use insert . . . select:

    INSERT INTO CustomerGroupLocationMap(LocationID, CustomerGroupID) 
        SELECT @i_LocationID, t.*
        FROM dbo.CSVToTable(@i_CustomerGroupID) t;

Your function dbo.CSVToTable() returns more than one value (I assume there is only one column). The correct syntax is the insert . . . select.

As a note, insert . . . values is really not needed. You can use insert . . . select even when you only have constants.

answered Jul 18, 2014 at 13:59

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CathalMF · Accepted Answer · 2014-07-18 13:58:55Z

0

IF NOT EXISTS (SELECT TOP 1 FROM CustomerGroupLocationMap WHERE LocationID = @i_LocationID AND CustomerGroupID = @i_CustomerGroupID)
BEGIN

    INSERT INTO CustomerGroupLocationMap (LocationID, CustomerGroupID) VALUES (@i_LocationID, (SELECT * FROM dbo.CSVToTable(@i_CustomerGroupID)));

END

answered Jul 18, 2014 at 13:58

CathalMF

10.1k7 gold badges76 silver badges115 bronze badges

Comments

TMNT2014 · Accepted Answer · 2014-07-18 14:05:20Z

0

You would need to rewrite both your exists query and the insert query-

IF NOT EXISTS (SELECT 1 FROM CustomerGroupLocationMap WHERE LocationID = @i_LocationID AND CustomerGroupID IN (SELECT *
    FROM dbo.CSVToTable(@i_CustomerGroupID) ))
BEGIN

    INSERT INTO CustomerGroupLocationMap (LocationID, CustomerGroupID) 
    SELECT @i_LocationID, i.* FROM dbo.CSVToTable(@i_CustomerGroupID) i;

END

answered Jul 18, 2014 at 14:05

TMNT2014

2,1401 gold badge12 silver badges13 bronze badges

Collectives™ on Stack Overflow

Sql Insert Error -"Subquery returned more than 1 value"

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related