Stored Procedure Subquery Issue

Question

Hi,

In the below stored procedure, I often get this error message... "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Why is this? I check for multiple entries and delete any that exist. Yet still gives that error on occassion.

 Thanks.

ALTER PROCEDURE [dbo].[MainTbl]
	-- Add the parameters for the stored procedure here
	@Name nvarchar(5),
AS
SET TRANSACTION ISOLATION LEVEL READ COMMITTED 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	Begin Transaction

	DECLARE @DetailsColour nvarchar(6);


  -- Insert statements for procedure here
	Begin
			-- Details
			DECLARE @CountDetails int;
			DECLARE @FirstEntryDetails int;

			SET @CountDetails = (SELECT COUNT (Name)
				FROM [DetailsTbl]
				WHERE Name = @Name
				)

			if (@CountDetails > 1)
			Begin
				SET @FirstEntryDetails = (SELECT TOP(1) NameID 
				FROM [DetailsTbl]
				WHERE Name = @Name
				ORDER BY NameID ASC)

				DELETE FROM [DetailsTbl]
				WHERE Name = @Name 
				AND (NameID != @FirstEntryDetails)
			End

			Set @DetailsColour = (SELECT DetailsColour
				FROM [DetailsTbl]
				WHERE Name = @Name)
				if @DetailsColour is null begin set @DetailsColour = 'RED' end


	DECLARE @FoundData int;
	SET @FoundData = 0;
	

	Set @FoundData = (
		SELECT COUNT (Name)
		From [MainTbl]
		WHERE (Name = @Name)
		AND (AccPeriod = @AccPeriod)
		)
	if @FoundData = 0
	 Begin
		insert into [MainTbl] (Name, Colour) 
		values (@Name, @DetailsColour);
	 End
	Else if (@FoundData > 1)
		Begin
			-- check for duplicates
			DECLARE @FirstEntryMain int;

			SET @FirstEntryMain = (SELECT TOP(1) MainID 
			FROM [MainTbl]
			WHERE Name = @Name
			ORDER BY MainID ASC)

			DELETE FROM [MainTbl]
			WHERE Name = @Name 
			AND (MainID != @FirstEntryMain)

			UPDATE [MainTbl]
			SET DetailsColour= @DetailsColour
			WHERE (Name = @Name)
		End
	Else
	 Begin
		-- If the data does exist update it
		update [MainTbl]
		set DetailsColour= @DetailsColour
		Where (Name = @Name)
	 End

	Commit

END

Answer 1

This line is the possible candidate... Name might consists of more than 1 color.....you might add top 1.. but it depends on your business logic...

	Set @DetailsColour = (SELECT DetailsColour
				FROM [DetailsTbl]
				WHERE Name = @Name)

Answer 2

The only statement as such that possibly could yield this error is

Set @DetailsColour = (SELECT DetailsColour
             FROM [DetailsTbl]
             WHERE Name = @Name)

All other queries have COUNT or TOP (1) that excludes the possibility.

However, why should we spend our time guessing? You have the complete error message which includes the line number - and the module where the errors occur. Not the least the module name is interesting, as the error could be in a trigger which is fired by any of the DML statements.

---

Erland Sommarskog, SQL Server MVP, [e-mail]
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Answer 3

May be the following links help.

http://bytes.com/topic/sql-server/answers/81777-subquery-returned-more-than-1-value-not-permitted-when-subquery-follows-when-subquery-used-expression

http://www.dbforums.com/microsoft-sql-server/978185-subquery-returned-more-than-1-value.html

Thanks

Answer 4

MIght be this part of query returning multiple rows so that it has throwing the error

Please try to find out how many records it has throwing

Set @DetailsColour = (SELECT DetailsColour
FROM [DetailsTbl]
WHERE Name = @Name)

solution - try this

 

Set @DetailsColour = (SELECT top 1 DetailsColour
FROM [DetailsTbl]
WHERE Name = @Name)

Answer 5

This line is the possible candidate... Name might consists of more than 1 color.....you might add top 1.. but it depends on your business logic...

 

	Set @DetailsColour = (SELECT DetailsColour

				FROM [DetailsTbl]

				WHERE Name = @Name)

 

Yes it seems to be here. But why does it occure when I delete all multiple rows beforehand...

DELETE FROM [DetailsTbl]
				WHERE Name = @Name 
				AND (NameID != @FirstEntryDetails)

 

Answer 6

Is there any chance that NameID  value will be equals to null...

If that is the case, <> statement will not delete that records....... so, in next line it will cause that error...

 

another reason might be as Sommarskog said, can you also check any triggers exists on the detailstbl and maintbl?? if exists, check that code also......

Answer 7

 

SELECT NAME

 

FROM [DetailsTbl]
group by Name
having COUNT(*) > 1

 

---

Jon Royales

Answer 8

The code posted above... i.e.

SELECT Name
FROM [DetailsTbl]
group by Name
having COUNT(*) > 1

will display the duplicates in the table if there are any

 

ps

sorry I was having trouble with formatting

 

 

---

Jon Royales