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

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

JuliafrazerAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>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
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
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
 
JuliafrazerAuthor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.