Solved

sql sub procedure returns correct results but also an error message due to a sub query violation

Posted on 2007-11-28
4
512 Views
Last Modified: 2010-04-21
Hi, running sql server 2005 stored procedure using a while loop, with if else statement, i try to populate a temporary returnable table with values for a booking, slotid, time and username/availability.

However, despite the correct results returning, i get the following error message

Msg 512, Level 16, State 1, Procedure test2, Line 63
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, Procedure test2, Line 63
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

What could be wrong?

Thanks

Julia
WHILE

	(Select Max (sa.SlotID)

	From SlotAvailability as sa join AvailabilityType as at on sa.availabilityid = at.availabilityID

	WHERE sa.MachineID = @MachineID and sa.availabilityid = 1) >= @counter

		

		Begin
 

--checks to see if the counter matches a slotId that exists for the selected machine, or null in this case

--else moves on to the next slotID before any processing gets done.

		If 

			(

			select slotid

			from slotavailability

			where exists

			(select slotID

			from slotavailability

			Where @Counter   in (Select SlotID from slotavailability where machineID = @machineID and availabilityid = 1))) is null
 
 

			Begin

			SET @Counter = @Counter + 1
 

			End
 

		Else
 

			Begin

			Set @SlotTimeCounter	= (select slottime	from slot where slotId = @Counter)
 

			Insert Into @DateResults

			Values (@Counter,@SlotTimeCounter, 'Available')

			

			

	
 
 

			SET @Counter = @Counter + 1
 

		

			End

		END
 

	END

Open in new window

0
Comment
Question by:Juliafrazer
  • 2
4 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 400 total points
ID: 20366919
>Subquery returned more than 1 value.
Here are your sub-queries.  Run both of them to see if one returns more than one record, then fix.

select slotID from slotavailability Where @Counter in (Select SlotID from slotavailability where machineID = @machineID and availabilityid = 1))) is

Select SlotID from slotavailability where machineID = @machineID and availabilityid = 1
0
 
LVL 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 100 total points
ID: 20367001
Can you simplify your query to something like:
WHILE

	(Select Max (sa.SlotID)

	From SlotAvailability as sa join AvailabilityType as at on sa.availabilityid = at.availabilityID

	WHERE sa.MachineID = @MachineID and sa.availabilityid = 1) >= @counter

		

		Begin

 

--checks to see if the counter matches a slotId that exists for the selected machine, or null in this case

--else moves on to the next slotID before any processing gets done.

		If 

			(

			select slotID

			from slotavailability

			Where SlotID = @Counter and machineID = @machineID and availabilityid = 1) is null

 

 

			Begin

			SET @Counter = @Counter + 1

 

			End

 

		Else

 

			Begin

			Set @SlotTimeCounter	= (select slottime	from slot where slotId = @Counter)

 

			Insert Into @DateResults

			Values (@Counter,@SlotTimeCounter, 'Available')

			

			

	

 

 

			SET @Counter = @Counter + 1

 

		

			End

		END

 

	END

Open in new window

0
 

Author Closing Comment

by:Juliafrazer
ID: 31411460
thanks guys, This helped me.  The select statement in question was returning multiple slotID's.  It had to trim the query as you selected and add an an additional and slotID = @counter statement
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 20367165
Thanks for the grade.  Good luck with your project.  -Jim

btw What casino do you work for?  I spent some time at Mystic Lake Casino in Prior Lake, Minnesota, and nearly accepted an offer at Greektown Casino in Detroit.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Peformance + mulitple query plans 9 48
sql server query from excel 3 57
SQL Server - Slabs 9 38
ORA-00923: FROM keyword not found where expected 3 33
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now