The stored proc is failing at below location,Thanks, for all your help.
--Insert MSOrg Information
DECLARE @PersonnelNumber int,
@MSOrg varchar(255)
DECLARE csr CURSOR FAST_FORWARD FOR
SELECT PersonnelNumber FROM Person
OPEN csr
FETCH NEXT FROM csr
INTO @PersonnelNumber
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC GetMSOrg @PersonnelNumber, @MSOrg out
INSERT INTO PersonSubject (
PersonnelNumber
,SubjectID
,SubjectValue
,Created
,Updated
)
SELECT @PersonnelNumber
,SubjectID
,@MSOrg
,getDate()
,getDate()
FROM Subject
WHERE DisplayName = 'MS Org'
FETCH NEXT FROM csr
INTO @PersonnelNumber
END
CLOSE csr
DEALLOCATE csr
Below is the stored prc defination GetMSOrg and fails at third condition
CREATE PROCEDURE [dbo].[GetMSOrg]
(
@PersonnelNumber int
,@OrgTerm varchar(200) out
)
AS
DECLARE @MDRTermID int
,@ReportsToPersonnelNbr int
--Check to see if we have reached the top of the chart
SELECT @ReportsToPersonnelNbr = ReportsToPersonnelNbr
FROM ReportsTo
WHERE PersonnelNumber = @PersonnelNumber
IF (@ReportsToPersonnelNbr IS NULL) --Reached the Top of the Org Ladder
BEGIN
SET @OrgTerm = 'Non-standard rollup'
END
ELSE IF (@PersonnelNumber IN (SELECT PersonnelNumber FROM OrgTermMap))
BEGIN
SELECT @OrgTerm = s.Term
FROM OrgTermMap tm
JOIN Taxonomy..StaticHierarchy s ON tm.OrgTermID = s.TermID
WHERE tm.PersonnelNumber = @PersonnelNumber
END
ELSE
BEGIN
SELECT @MDRTermID = tm.OrgTermID
FROM ReportsTo r
JOIN OrgTermMap tm ON r.ReportsToPersonnelNbr = tm.PersonnelNumber
WHERE r.PersonnelNumber = @PersonnelNumber
IF (@MDRTermID IS NULL)
BEGIN
EXEC GetMSOrg @ReportsToPersonnelNbr, @OrgTerm out
END
ELSE
BEGIN
SELECT @OrgTerm = Term
FROM Taxonomy..StaticHierarchy
WHERE VocabID = 118
AND TermID = @MDRTermID
END
END
GO
