here is my procedure
ALTER PROCEDURE [dbo].[RmsUpdateRoAssignReAssign1]
(
@XMLAssignRo AS XML
)
AS
BEGIN
UPDATE RmsActivity
SET
RmsProcessorID = DocumentElement.AssignReAssignRo.value('(UserID)[1]', 'INT' ),
LastUpdatedByID= DocumentElement.AssignReAssignRo.value('(UserID)[1]', 'INT' ),
LastUpdatedDate=GETDATE()
FROM
@XMLAssignRo.nodes('/DocumentElement/AssignReAssignRo') DocumentElement(AssignReAssignRo)
INNER JOIN RmsUserSkillSet RS
ON
RS.RmsSkillSetTypeID =
CASE
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
END
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
WHERE
RmsActivityID = DocumentElement.AssignReAssignRo.value('(ActivityID)[1]', 'INT' )
END
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]