Technical: Microsoft – SQL Server – Error – “Auto Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.” – Msg 512, Level 16, State 1

Technical: Microsoft – SQL Server – Error – “Auto Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.” – Msg 512, Level 16, State 1

Introduction

This error is not really a big deal, but can use some of the methods mentioned below to streamline your code.

SQL

 

SQL  – Table Creation



if OBJECT_ID('dbo.sales') is null
begin

create table dbo.sales
(
      [partitionID] int
    , [salesPersonID] int
)

end
go

SQL  – Statement Insert



set nocount on;

truncate table dbo.sales

insert into dbo.sales
([partitionID], [salesPersonID])
values (10, 120)
go	

insert into dbo.sales([partitionID], [salesPersonID])
values (20, 140);

insert into dbo.sales([partitionID], [salesPersonID])
values (30, 160);
go	

insert into dbo.sales([partitionID], [salesPersonID])
  values
	  (40, 240)
	, (50, 260)
	, (60, 280);	
go	

Error

When we run the multi-statement insert, we run into the error pasted below:

Error Message


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

Error Source

We traced the error back to the Trigger on the dbo.sales table.



if OBJECT_ID('[dbo].[TR_Sales_SetPartitionID]') is not null
begin
	drop trigger [dbo].[TR_Sales_SetPartitionID]
end
go

CREATE TRIGGER [dbo].[TR_Sales_SetPartitionID]
	ON  [dbo].[sales] 
	AFTER INSERT
AS 
BEGIN

    SET NOCOUNT ON;

    DECLARE @salesPersonId INT
    DECLARE @partitionID INT

    begin
	SET @salesPersonId = (SELECT SalesPersonID FROM inserted)

        set @partitionID = rand(cast(checksum(newid()) as bigint))
                 * @salesPersonId

	IF ( (SELECT partitionID FROM inserted) IS NULL )
	BEGIN

		UPDATE [dbo].[sales] 
		SET    partitionID = @partitionID
		WHERE   salesPersonID = @salesPersonID

	END

   end
END

Error Diagnosis

  • Traced the error back to the SQL that reads “set @salesPersonID = ( select personID from inserted”
  • The select line has problems when we are trying to insert more than one record within the same batch; we thus end up with Number of records in the Virtual table (inserted).  The system is not able to place N entries in the salePersonID column into a single variable (@salesPersonID)

Corrections

Corrected Trigger

We changed the problematic SQL to ensure that a single record is returned:

Revision Snippet

	   SET @salesPersonId = (
					SELECT SalesPersonID 
					FROM   @tblList
					where  [id] = @id
			       )

Full Revision


if OBJECT_ID('[dbo].[TR_Sales_SetPartitionID]') is not null
begin
	drop trigger [dbo].[TR_Sales_SetPartitionID]
end
go

CREATE TRIGGER [dbo].[TR_Sales_SetPartitionID]
	ON  [dbo].[sales] 
	AFTER INSERT
AS 
BEGIN

    SET NOCOUNT ON;

    DECLARE @salesPersonId INT
    DECLARE @partitionID INT

    declare
	  @id bigint
	, @IdMax bigInt

   DECLARE @tblList TABLE
   (
	 [id] bigint not null identity(1,1)
	,[SalesPersonID] int not null
	,[partitionID] int null
   )			

   begin

	insert into @tblList
	(
		[SalesPersonID]
	)
	select
		SalesPersonID
	from  inserted

	set @id =1
	set @IdMax = ( select MAX(id) from @tblList)

	while (@id <= @IdMax)
	begin

	    /*
		SET @salesPersonId = (SELECT SalesPersonID FROM inserted)

		set @partitionID = Rand(@salesPersonID) * 10010
	    */

	   SET @salesPersonId = (
					SELECT SalesPersonID 
					FROM   @tblList
					where  [id] = @id
			       )

	  set @partitionID = rand(cast(checksum(newid()) as bigint)) * @salesPersonId

	  update @tblList
	  set    [partitionID] = @partitionID 
	  where  [id] = @id

 	  set @id = @id + 1
       end

      --IF ( (SELECT partitionID FROM inserted) IS NULL )
      BEGIN

	   UPDATE tblSales
	   SET    partitionID = @partitionID

	   FROM   [dbo].[sales] tblSales

		inner join @tblList tblList
			on tblSales.salesPersonID = tblList.SalesPersonID

	  WHERE  tblSales.partitionID is null

     END

   end

END

go

Corrected Trigger – Set

We changed the problematic SQL to utilize Sets.  This is possible because the work we were doing within the Trigger can be maintained within a simple SQL.

Full Revision



if OBJECT_ID('[dbo].[TR_Sales_SetPartitionID]') is not null
begin
	drop trigger [dbo].[TR_Sales_SetPartitionID]
end
go

CREATE TRIGGER [dbo].[TR_Sales_SetPartitionID]
	ON  [dbo].[sales] 
	AFTER INSERT
AS 
BEGIN

    SET NOCOUNT ON;

    begin

	UPDATE tblSales

	SET    partitionID = rand(cast(checksum(newid()) as bigint)) 
                         * tblInserted.salesPersonId

	FROM   [dbo].[sales] tblSales

		inner join inserted tblInserted

			  on tblSales.salesPersonID = tblInserted.salesPersonID

	WHERE  tblSales.partitionID is null

   end

END

9 thoughts on “Technical: Microsoft – SQL Server – Error – “Auto Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.” – Msg 512, Level 16, State 1

  1. Hi your explain is amazing about this Error, I am a new developer and actually I’m working with a web system, that have show me that Error, please can you help me to know what I am doing wrong?.
    This are the triggers that I am using:
    //TRIGGER TO SAVE THE INFO
    ALTER TRIGGER [dbo].[tg_Audit_Insertbom]
    ON [dbo].[Bomreq]
    AFTER INSERT
    AS
    DECLARE @now datetime
    DECLARE @accion nvarchar(10)

    BEGIN TRY
    SET @now = getdate()
    SET @accion = ‘INSERTED’

    INSERT INTO [dbo].[Move_historypart]
    (Part, ActualBomqpa, ReqBomqpa, Status, Comments, Fecha, Usuario, AdminComments, email, UsuarioAdmin)
    SELECT INSERTED.Part, INSERTED.ActualBomqpa, INSERTED.ReqBomqpa, INSERTED.Status, INSERTED.Comments, @now, INSERTED.Usuario, INSERTED.AdminComments, INSERTED.email, INSERTED.UsuarioAdmin
    FROM INSERTED
    END TRY

    BEGIN CATCH
    ROLLBACK TRANSACTION

    RAISERROR (‘HUBO UN ERROR SOBRE LA INSERCION DE LA TABLA AUDITORA DE PRODUCTOS’,20,1)
    END CATCH

    //TRIGGER TO SEND EMAIL
    USE [EngRecord]
    GO
    /****** Object: Trigger [dbo].[tg_Audit_InsertConver] Script Date: 01/27/2014 08:26:19 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER TRIGGER [dbo].[tg_Audit_InsertConver]
    ON [dbo].[ConverFactor]
    AFTER INSERT, UPDATE
    AS
    DECLARE @now datetime
    DECLARE @accion nvarchar(10)

    BEGIN TRY
    SET @now = getdate()
    SET @accion = ‘INSERTED’

    INSERT INTO [dbo].[Move_historypart]
    (Part, ActualConverfactor, ReqConverfactor, Status, Comments, Fecha, Usuario, AdminComments)
    SELECT INSERTED.Part, INSERTED.ActualConver, INSERTED.Reqconver, INSERTED.Status, INSERTED.Comments, @now, INSERTED.Usuario, INSERTED.AdminComments
    FROM INSERTED
    END TRY

    BEGIN CATCH
    ROLLBACK TRANSACTION

    RAISERROR (‘HUBO UN ERROR SOBRE LA INSERCION DE LA TABLA AUDITORA DE PRODUCTOS’,20,1)
    END CATCH

    • Joe:

      Thanks for commenting. I will need a bit of help to better understand question. Here are my follow-up questions:

      a) Can you please let me know share the actual table structures for [dbo].[Bomreq], [dbo].[ConverFactor], and [dbo].[Move_historypart]

      b) Also, sample DML (Insert or Update) statement

      c) Actual Error message

      It does not seem to me that you should be getting an error, but one never knows.

  2. Hi Daniela

    Thanks for your Quickly reply to my answer, here are my answers:
    a) Structure of [dbo].[BomReq]

    CREATE TABLE [dbo].[Bomreq](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Part] [varchar](50) NOT NULL,
    [ActualBomqpa] [varchar](50) NULL,
    [ReqBomqpa] [varchar](50) NULL,
    [Fecha] [datetime] NULL,
    [Status] [varchar](50) NOT NULL,
    [Comments] [varchar](250) NULL,
    [Usuario] [varchar](50) NOT NULL,
    [Accept] [bit] NULL,
    [Reject] [bit] NULL,
    [AdminComments] [varchar](300) NULL,
    [Email] [varchar](50) NULL,
    [UsuarioAdmin] [varchar](50) NULL,
    CONSTRAINT [PK_Bomreq] PRIMARY KEY CLUSTERED
    (
    [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO

    a)ConverFactor Structure

    CREATE TABLE [dbo].[Move_historypart](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Part] [varchar](50) NULL,
    [ActualScrapfactor] [varchar](50) NULL,
    [ReqScrapfactor] [varchar](50) NULL,
    [ActualConverfactor] [varchar](50) NULL,
    [ReqConverfactor] [varchar](50) NULL,
    [ActualBomqpa] [varchar](50) NULL,
    [ReqBomqpa] [varchar](50) NULL,
    [ActualRouting] [varchar](50) NULL,
    [ReqRouting] [varchar](50) NULL,
    [ActualPlannercode] [varchar](50) NULL,
    [ReqPlannercode] [varchar](50) NULL,
    [ActualEngrecord] [varchar](50) NULL,
    [ReqEngrecord] [varchar](50) NULL,
    [ActualMold] [varchar](50) NULL,
    [ReqMold] [varchar](50) NULL,
    [ActualDesign] [varchar](50) NULL,
    [ReqDesign] [varchar](50) NULL,
    [ActualInslevel] [varchar](50) NULL,
    [ReqInslevel] [varchar](50) NULL,
    [Status] [varchar](50) NULL,
    [Comments] [varchar](50) NULL,
    [Fecha] [datetime] NULL,
    [Usuario] [varchar](50) NULL,
    [Accept] [bit] NULL,
    [Reject] [bit] NULL,
    [AdminComments] [varchar](50) NULL,
    [email] [varchar](50) NULL,
    [UsuarioAdmin] [varchar](50) NULL,
    CONSTRAINT [PK_Move_history] PRIMARY KEY CLUSTERED
    (
    [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO

    ////////////////////////////////////////////////////////////
    The way I am doing the insert on MoveHistory_part is across the next Trigger:
    ALTER TRIGGER [dbo].[tg_Audit_Insertbom]
    ON [dbo].[Bomreq]
    AFTER INSERT
    AS
    DECLARE @now datetime
    DECLARE @accion nvarchar(10)

    BEGIN TRY
    SET @now = getdate()
    SET @accion = ‘INSERTED’

    INSERT INTO [dbo].[Move_historypart]
    (Part, ActualBomqpa, ReqBomqpa, Status, Comments, Fecha, Usuario, AdminComments, email, UsuarioAdmin)
    SELECT INSERTED.Part, INSERTED.ActualBomqpa, INSERTED.ReqBomqpa, INSERTED.Status, INSERTED.Comments, @now, INSERTED.Usuario, INSERTED.AdminComments, INSERTED.email, INSERTED.UsuarioAdmin
    FROM INSERTED
    END TRY

    BEGIN CATCH
    ROLLBACK TRANSACTION

    RAISERROR (‘HUBO UN ERROR SOBRE LA INSERCION DE LA TABLA AUDITORA DE PRODUCTOS’,20,1)
    END CATCH

    ///////////////////////////////////////////////////////////////////////////////////////////////
    The trigger that I created to send the email is the next:
    ALTER Trigger [dbo].[Trigger_Aviso_alRequisitor_Bomqpa]
    On [dbo].[Bomreq]
    For UPDATE
    As

    Declare @E_Subject varchar(200)
    Declare @usuario varchar(50)
    Declare @email varchar(100)
    Declare @ID varchar(50)
    Declare @Status varchar(50)
    Declare @direccion varchar(100)

    Select @ID = (Select Part From Inserted )
    Select @usuario = (Select Usuario from Inserted)
    Select @email = (Select email from Inserted where Usuario = @Usuario )
    Select @Status = (Select status from Inserted where Part = @ID)
    Select @E_Subject = @usuario + ‘ Your Request have been ‘ + @Status + ‘ from BOM/QPA Section. ‘

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ‘Engrecord_Mail’,
    @recipients= @email,
    @subject=@E_Subject,
    @body = ‘ Regards ‘

    ////////////////////////////////////////////////////////////////////////////
    The error is the next:
    Server Error in ‘/’ Application.
    ——————————————————————————–

    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.

    Exception Details: System.Data.SqlClient.SqlException: 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.

    Source Error:

    Line 101:
    Line 102:
    Line 103: comm.ExecuteNonQuery();
    Line 104:
    Line 105: conn.Close();

    Source File: C:\Users\jo73975\Documents\Visual Studio 2010\Projects\tester\tester\BomqpaGridview.aspx.cs Line: 103

    Stack Trace:

    [SqlException (0x80131904): 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.]
    System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +1754082
    System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5295874
    System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +242
    System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1682
    System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +269
    System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite) +1325
    System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +175
    System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) +205
    System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +160
    tester.WebForm21.btnReject_Click(Object sender, EventArgs e) in C:\Users\jo73975\Documents\Visual Studio 2010\Projects\tester\tester\BomqpaGridview.aspx.cs:103
    System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9552874
    System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +103
    System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
    System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
    System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35
    System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1724

    ——————————————————————————–

    I hope this can answer your questions, thanks so much for your time.

    Regards

    • if object_id(‘[dbo].[tg_Audit_Insertbom]’) is not null
      begin
      DROP TRIGGER [dbo].[tg_Audit_Insertbom]
      end
      go

      if object_id(‘[dbo].[Trigger_Aviso_alRequisitor_Bomqpa]’) is not null
      begin
      DROP TRIGGER [dbo].[Trigger_Aviso_alRequisitor_Bomqpa]
      end
      go

      CREATE TRIGGER [dbo].[tg_Audit_Insertbom]
      ON [dbo].[Bomreq]
      AFTER INSERT
      AS
      DECLARE @now datetime
      DECLARE @accion nvarchar(10)

      BEGIN TRY
      SET @now = getdate()
      SET @accion = ‘INSERTED’

      INSERT INTO [dbo].[Move_historypart]
      (Part, ActualBomqpa, ReqBomqpa, Status, Comments, Fecha, Usuario, AdminComments, email, UsuarioAdmin)
      SELECT INSERTED.Part, INSERTED.ActualBomqpa, INSERTED.ReqBomqpa, INSERTED.Status, INSERTED.Comments, @now, INSERTED.Usuario, INSERTED.AdminComments, INSERTED.email, INSERTED.UsuarioAdmin
      FROM INSERTED
      END TRY

      BEGIN CATCH
      ROLLBACK TRANSACTION

      RAISERROR (‘HUBO UN ERROR SOBRE LA INSERCION DE LA TABLA AUDITORA DE PRODUCTOS’,20,1)
      END CATCH

      go

      CREATE Trigger [dbo].[Trigger_Aviso_alRequisitor_Bomqpa]
      On [dbo].[Bomreq]
      For UPDATE
      As

      Declare @E_Subject varchar(200)
      Declare @usuario varchar(50)
      Declare @email varchar(100)
      Declare @ID varchar(50)
      Declare @Part varchar(50)
      Declare @Status varchar(50)
      Declare @direccion varchar(100)

      DECLARE cursorData CURSOR FOR

      select Part, Usuario, email, [status]
      from Inserted

      OPEN cursorData

      FETCH NEXT
      FROM cursorData
      INTO @Part, @usuario, @email, @Status

      WHILE @@FETCH_STATUS = 0
      BEGIN

      Select @E_Subject = @usuario + ‘ Your Request have been ‘
      + @Status + ‘ from BOM/QPA Section. ‘

      /*

      print
      ‘Email ‘ + @email + char(13) + char(10)
      + ‘Subject ‘ + @E_Subject + char(13) + char(10)

      */

      EXEC msdb.dbo.sp_send_dbmail
      @profile_name = ‘Engrecord_Mail’,
      @recipients= @email,
      @subject=@E_Subject,
      @body = ‘Regards’

      FETCH NEXT
      FROM cursorData
      INTO @Part, @usuario, @email, @Status

      END

  3. Thanks for the reply, it looks good, I have a doubt, do I must create a new Trigger or will be a Store Procedure?, I am sorry for this question, but I am really new on this Developers World.

    Thanks so much for all your help.

    Regards

  4. Thanks so much, I really appreciate your help, now is working so good.

    Please never let to share your valuable knowledge.

    Best Regards!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s