[RESOLVED] Subquery returned more than 1 value?

[blakemckenna]

I'm trying to add a single row to a SQL Server Table and I get the above message. This table has nine columns. The Primary Key is an Identity Column. At the point where I try to add a record, there is only one Column that contains data. I've never received this message before while trying to "Add" a record to the DB. I've attached a screenshot of the Table Design.

Here is the Stored Procedure:

ALTER PROCEDURE [Lookup].[uspSAVE_Manufacturer]
	@ManufacturerID	INT=NULL OUTPUT,
	@Manufacturer		VARCHAR(50)=NULL,
	@Notes			VARCHAR(MAX)=NULL,
	@SortOrder		TINYINT=NULL,
	@IsActive			BIT=NULL,
	@CreatedByID		SMALLINT=NULL,
	@UpdatedByID		SMALLINT=NULL
AS
BEGIN

	IF EXISTS (SELECT [ManufacturerID] FROM [Lookup].[Manufacturer] WHERE [ManufacturerID] = @ManufacturerID)
		BEGIN
			UPDATE	[Lookup].[Manufacturer]
			   SET	[Manufacturer] = @Manufacturer
					,[SortOrder] = @SortOrder
					,[Notes] = @Notes
					,[IsActive] = @IsActive
					,[UpdatedByID] = @UpdatedByID
					,[DateUpdated] = GETDATE()
			 WHERE	[ManufacturerID] = @ManufacturerID
		END
	ELSE
		BEGIN
			INSERT INTO [Lookup].[Manufacturer]
				([Manufacturer]
				,[SortOrder]
				,[Notes]
			  	,[IsActive]
			  	,[CreatedByID]
			  	,[DateCreated]
			  	,[UpdatedByID]
			  	,[DateUpdated])
			 VALUES
				(@Manufacturer
				 ,@SortOrder
				 ,@Notes
				 ,@IsActive
				 ,@CreatedByID
				 ,GETDATE()
				 ,@UpdatedByID
				 ,GETDATE())

			SET @ManufacturerID = @@IDENTITY
		END	
	
END

Thanks,

[digitalShaman]

i dont see anything that would cause this error so i guess there is something more to it. maybe a Trigger?

[szlamany]

fwiw...

The EXISTS() is not needed in this situation - I always do this

Update Violation_T Set VDate=@ActDate, TDate=GetDate()
	Where ActId=@PopupKey and VType=@VX
If @@RowCount=0 Insert into Violation_T values (@PopupKey,@ActDate,'1',@VX,GetDate())

The UPDATE will either work or not and gives me a @@RowCount to check.

If no update - then do the INSERT.

It isn't your problem - and like I said - fwiw...

[blakemckenna]

But I have a ton of SP's that utilize this kind of logic and they ALL work. There's something about this particular script or Table.

[si_the_geek]

Thread moved to the 'Database Development' forum

[FunkyDexter]

We can say with absolute certainty that the code you've shown here is not what is causing the error. That error is thrown when a sub query finds more than one row for a single row in the main query - meaning the result cannot be expressed. The code you've shown does not include a sub query, therefore it cannot be causing that error.

digitalShaman is right, there's something else going on that you're not seeing. There are a few things that might cause it. A trigger or programmed constraint on the table are probably the most likely. Have you checked the table for these?

Something's causing the error but the code you've shown isn't the culprit I'm afraid.



PS, Szlamany's suggestion hasn't really got anything to do with your problem but it is a good tip on how to handle upserts. It'll perform as well as your approach where an existing record is not found and roughly twice as quick where an existing record is found, that's a significant improvement. Depending on the version of SQL Server you're using you might also want to take a look at the MERGE statement which is the formal SQLServer upsert syntax and allows you to express the whole thing as a single statement.

[blakemckenna]

I figured it out. It did have to do with a DB Trigger. Thanks for triggering that thought Funky!

[FunkyDexter]

digitalShaman deserves the credit.

Triggers are a useful tool but they can be a real pain. It's easy to forget they're there.

[blakemckenna]

Thank you digitalShaman!