Click here to Skip to main content
11,796,275 members (77,772 online)
Rate this: bad
Please Sign up or sign in to vote.
See more: SQL Server
here is my procedure
ALTER PROCEDURE [dbo].[RmsUpdateRoAssignReAssign1]
@XMLAssignRo  AS XML
	UPDATE RmsActivity 
						RmsProcessorID = DocumentElement.AssignReAssignRo.value('(UserID)[1]', 'INT' ),
						LastUpdatedByID= DocumentElement.AssignReAssignRo.value('(UserID)[1]', 'INT' ),
						@XMLAssignRo.nodes('/DocumentElement/AssignReAssignRo') DocumentElement(AssignReAssignRo)
						INNER JOIN RmsUserSkillSet RS
							 RS.RmsSkillSetTypeID =  
								 WHEN DocumentElement.AssignReAssignRo.value('(Aging)[1]', 'INT' )< =20  THEN 1					
								 WHEN DocumentElement.AssignReAssignRo.value('(Aging)[1]', 'INT' ) BETWEEN 21 AND 30  THEN 2
								 WHEN DocumentElement.AssignReAssignRo.value('(Aging)[1]', 'INT' ) >=31 THEN 3							
						INNER JOIN tblRoamingUsers RU
						ON RS.RoamingUserID=RU.RoamingUserID
						AND RU.UserID=DocumentElement.AssignReAssignRo.value('(UserID)[1]', 'INT' )
						INNER JOIN tblCampus C
						ON	C.CampusID=DocumentElement.AssignReAssignRo.value('(CampusID)[1]', 'INT' )
						AND RU.SchoolID=C.SchoolID
						RmsActivityID = DocumentElement.AssignReAssignRo.value('(ActivityID)[1]', 'INT' )

i am getting the following error
(Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.)

'RoamingUserID' is unique for a particular user and campus
i m not able to get y i m getting the above error

[edit]Code block sorted out - OriginalGriff[/edit]
Posted 8-Oct-12 20:46pm
Edited 8-Oct-12 20:52pm
digimanus at 12-Oct-12 11:16am
Have you done a select * from.....?
Then you shouls see the problem

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

You might have to check each join where it is giving more than one rows.
U can also use distinct clause where it returns more than one rows.
And rather applying as a case when, u should take as a inner join and then make join.

Keep helping nature..!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Maciej Los 460
1 Abhinav S 368
2 OriginalGriff 340
3 CPallini 269
4 Sergey Alexandrovich Kryukov 252
0 OriginalGriff 2,012
1 Maciej Los 1,615
2 KrunalRohit 1,310
3 CPallini 1,015
4 Richard MacCutchan 828

Advertise | Privacy | Mobile
Web03 | 2.8.151002.1 | Last Updated 18 Oct 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100