CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
karan
karan

Reputation: 482

Error shown when executing the stored procedure

Issues are occurring when executing the stored procedure.

Errors like :

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

Here is my query:

Create procedure [dbo].[Mysp]
as
BEGIN
    Declare @Id int
    Declare @MyspRemainingIdFromProduct varchar(max)
    Declare @StoreDeleteQuery varchar(max)
    Declare @i int
    Declare @MyspAddIdFromProduct varchar(max)

    set @MyspRemainingIdFromProduct = 
           (SELECT Mysp_abc_Product.Id 
            FROM Mysp_abc_Product 
            LEFT OUTER JOIN Product ON Mysp_abc_Product.ID = Product.ID
            WHERE Product.ID IS NULL)

    set @StoreDeleteQuery = 'DELETE FROM Mysp_abc_Product WHERE Id IN (' + @MyspRemainingIdFromProduct + ')'

    exec(@StoreDeleteQuery)

    set @MyspAddIdFromProduct =   
           (SELECT Product.Id 
            FROM Product 
            LEFT OUTER JOIN Mysp_abc_Product ON Mysp_abc_Product.ID = Product.ID
            WHERE Mysp_abc_Product.ID IS NULL)

    WHILE (@i <= @MyspAddIdFromProduct)
    BEGIN
        --Insert data into Mysp_abc_Product where condition is p.Deleted = 'False' or p.Published = 'True' or  VisibleIndividually = 'True'
        INSERT INTO Mysp_abc_Product(ProductId, abcStatus, IsDeleted, InTime, StoreId, LanguageId) 
            SELECT 
                p.Id, 1, 0, GETDATE(), s.Id, l.Id 
            FROM
                Language l, Store s, Product p 
            LEFT JOIN 
                Mysp_abc_Product isp on isp.ProductId = p.Id 
            WHERE
                isp.Id IS NULL 
                AND p.Deleted = 'False' 
                OR p.Published = 'True' OR VisibleIndividually = 'True'

        --Insert data into Mysp_abc_Product where condition is p.Deleted = 'True' or p.Published = 'False' or  VisibleIndividually = 'False'
        INSERT INTO Mysp_abc_Product(ProductId, abcStatus, IsDeleted, InTime, StoreId, LanguageId) 
            SELECT
                p.Id, 1, 1, GETDATE(), s.Id, l.Id
            FROM
                Language l, Store s, Product p 
            LEFT JOIN
                Mysp_abc_Product isp ON isp.ProductId = p.Id 
            WHERE
                isp.Id IS NULL 
                AND p.Deleted = 'True' 
                 OR p.Published = 'False' 
                 OR VisibleIndividually = 'False'
    END
END

Upvotes: 1

Views: 62

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You have problems all over the place. The error is pretty clear. It is your code that is not.

For instance, the delete call is way overdone. Why use variables and dynamic SQL at all? Just run:

DELETE FROM Mysp_abc_Product
    WHERE Id IN (SELECT Mysp_abc_Product.Id 
                 FROM Mysp_abc_Product LEFT OUTER JOIN
                      Product
                      ON Mysp_abc_Product.ID = Product.ID
                 WHERE Product.ID IS NULL
                );

Or, even more to the point:

DELETE FROM Mysp_abc_Product abc
    WHERE NOT EXISTS (SELECT 1
                      FROM Product p
                      WHERE abc.ID = p.ID
                     );

This is more concise. It should have better performance.

Note: This only addresses the first problem in your code. There are more problems.

My advice is to build the stored procedure by testing each subquery at once. Don't do the looping in T-SQL. Use set-based operations.

Upvotes: 2

Ritesh
Ritesh

Reputation: 19

set @IncrementalRemainingIdFromProduct = 
       (SELECT Incremental_Solr_Product.Id 
        FROM Incremental_Solr_Product 
        LEFT OUTER JOIN Product ON Incremental_Solr_Product.ID = Product.ID
        WHERE Product.ID IS NULL)

set @IncrementalAddIdFromProduct = 
       (SELECT Product.Id 
        FROM Product 
        LEFT OUTER JOIN Incremental_Solr_Product ON Incremental_Solr_Product.ID = Product.ID
        WHERE Incremental_Solr_Product.ID IS NULL)

Your above query returns multiple records for Incremental_Solr_Product.Id so need to change it to

set @IncrementalRemainingIdFromProduct = 
        (SELECT TOP(1) Incremental_Solr_Product.Id 
         FROM Incremental_Solr_Product 
         LEFT OUTER JOIN Product ON Incremental_Solr_Product.ID = Product.ID
         WHERE Product.ID IS NULL)

So it will work for you

Upvotes: 1

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

The Error message says it all. In SQL server you can't return more than one value in a subquery.

The problem is with:

 set @MyspRemainingIdFromProduct = 
           (SELECT Mysp_abc_Product.Id 
            FROM Mysp_abc_Product 
            LEFT OUTER JOIN Product ON Mysp_abc_Product.ID = Product.ID
            WHERE Product.ID IS NULL)

The subquery returns more than one Id which is not right.

Upvotes: 1

Related Questions