Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2007-11-28
4
Medium Priority
?
522 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1600 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 52

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 400 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 66

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

618 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