I have a stored procedure that does some clean up on a database each morning. This is a Connectwise database (Company CRM) which we have access for reporting. It currently lives on a VM Server 2012 in Microsoft Azure. In a nutshell this stored procedure combines data from multiple records into one record and deletes all the combined records.

Background into the issue experienced. We are deleting a record from one table (parent), there are several other tables (child) with foreign keys to this table and they have not been setup with the cascade delete option. Our Stored Procedure goes through each one of the other child tables and deletes the records relating to the parent table. Turns out this script started failing in February and we are just now noticing (smh).

Error Message Received -

Msg 512, Level 16, State 1, Procedure Alter_SR_Service_User_Defined_Field_Value, Line 53 [Batch Start Line 93]
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    delete
    from    
        SR_Service_User_Defined_Field_Value
    where
        SR_Service_RecID in (   select 
                                    (s.SR_Service_RecID)
                                from
                                    SR_Service s
                                join SR_Board sb on sb.SR_Board_RecID=s.SR_Board_RecID
                                where sb.Board_Name like 'Collections' 
                                    and s.SR_Status_RecID != 511
                                    and cast(trim(left(right(s.Summary, len(s.Summary) - charindex('#',s.Summary)),5))as int)=@invoiceNumber
                            )
        and SR_Service_RecID != (   select 
                                        min (s.SR_Service_RecID)
                                    from
                                        SR_Service s
                                    join SR_Board sb on sb.SR_Board_RecID=s.SR_Board_RecID
                                    where sb.Board_Name like 'Collections' 
                                        and s.SR_Status_RecID != 511
                                        and cast(trim(left(right(s.Summary, len(s.Summary) - charindex('#',s.Summary)),5))as int)=@invoiceNumber
                                )
                                )

SR_Service_User_Defined_Field_Value Table (1st column is an primary key, second and third are foreign keys):

Troubleshooting to date:

1. I have simplified the delete statement to just delete based off of SR_Service_RecIDs without the additional select subqueries with no luck.
2. I have tried to delete multiple records by SR_Service_User_Defined_Field_Value_RecID using the in statement with no luck. Where SR_Service_User_Defined_Field_Value_RecID in (5789,5787).
3. I could delete using the in statement with just one recid in the list. Where SR_Service_User_Defined_Field_Value_RecID in (5789).
4. I updated the FK SR_service_RecID to use cascade delete. I then tried to delete the one parent record and received the same error.

Trigger on this table:

  -- Code for delete 
  if exists(select * from deleted) and not exists(Select * from inserted)
begin 
    INSERT INTO [TruCWHistorian].[dbo].[CWHistorian]( [Table], [Object_RecID], [FieldName], [Old_Value], [New_Value], [Date_Updated], [Updated_By])
    SELECT 
        'SR_Service_User_Defined_Field_Value',
        d.SR_Service_RecID,
        u.Caption,
        (select User_Defined_Field_Value FROM deleted),
        '', -- no new value for deleted record case 
        getDate(),
        '' -- no record of who made change in this case 
    FROM    
        deleted d
    join User_Defined_Field u on u.User_Defined_Field_RecID=d.User_Defined_Field_RecID
end  

CodePudding user response：

In your trigger, this subquery is causing the problem (and there's no reason for it anyway, since with no correlation it returns all the rows from deleted):

(select User_Defined_Field_Value FROM deleted)

Why isn't it just:

INSERT INTO [TruCWHistorian].[dbo].[CWHistorian](...)
SELECT 
    'SR_Service_User_Defined_Field_Value',
    d.SR_Service_RecID,
    u.Caption,
    d.User_Defined_Field_Value,
    '', -- no new value for deleted record case 
    getDate(),
    '' -- no record of who made change in this case 
FROM    
    deleted d
...